Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Sort and Match Uneven Columns of Data

1. I agreed to these rules
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. Established Member
Join Date
24th June 2003
Location
Posts
302

## 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. I agreed to these rules
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. Established Member
Join Date
24th June 2003
Location
Posts
302

## 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.

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

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

#### 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