Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 4 of 4

Thread: Sort and Match Uneven Columns of Data

  1. #1
    Join Date
    31st December 2004
    Posts
    10

    Sort and Match Uneven Columns of Data

    How can i go about sorting and matching two columns or more of data if the columns are uneven? (See Simplified Pic below) Basically I need the rows in the first two columns to match with the according data in the last two columns. A simple sort will not work because there are not the same numbers in the RA# and AF# fields.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    24th June 2003
    Location
    Canada
    Posts
    301

    Re: Sort and Match Uneven Columns of data

    Assuming your match criteria reside under RA# and AF# columns, you can populate either of the two sets of data using a combination of INDEX and MATCH functions.

    Are you familiar with these?

    m

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    31st December 2004
    Posts
    10

    Re: Sort and Match Uneven Columns of data

    not really, could you provide an example? I just need to make sure that the ra# and quantity columns match up with the af# and quantity column.
    so it would go from

    to
    Last edited by Jack in the UK; November 4th, 2005 at 09:36. Reason: Tidy post / Edited subject title - jiuk

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    24th June 2003
    Location
    Canada
    Posts
    301

    Re: Sort and Match Uneven Columns of data (Still Can't Get It)

    To get your results to appear exactly like you have them in http://www.benrabicoff.com/match2.jpg, you need to have the original RA# List somewhere other than in Columns B & C, as this is where you'd like the final output (sorted list) to appear. To avoid confusion, let's make your sorted RA# list appear in columns H and I rather than B & C. That is, the sorted RA#'s will appear in column I and corresponding quantities in column H.

    The following formulas can help you achieve this.


    in cell I2, you will need to type in the formula:

    =IF(COUNTIF($C:$C,E2)=0,"",VLOOKUP(E2,$C:$C,1,0))


    in cell H2, you will need to type in the formula:

    =IF(I2="","",INDEX($B:$B,MATCH(E2,$C:$C,0)))


    Both the above formulas may be copied down till the last row of data.

    HTH

    m

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Match Data Across Multiple Columns
    By mikew1 in forum EXCEL HELP
    Replies: 2
    Last Post: December 6th, 2007, 20:29
  2. Sort and Shuffle on Two Columns to find missing data
    By Ger Plante in forum EXCEL HELP
    Replies: 10
    Last Post: January 30th, 2006, 20:54
  3. Replies: 1
    Last Post: October 21st, 2005, 05:30
  4. Sort a column of data into 2 columns
    By braveheart in forum EXCEL HELP
    Replies: 1
    Last Post: June 3rd, 2005, 21:21
  5. Replies: 3
    Last Post: March 19th, 2005, 05:19

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno