Hi guys, need some help with Excel here.

I have two tables, each with 10 columns. One has 100 rows and another has 90 rows. They are identical except for those 10 rows but the longer one is sorted alphabetically by Column A and the shorter one is sorted in a random order.

Is there a way for me to match the Alphabetically Sorted table to the Randomly Sorted so that it is in the same order with the additional people on the bottom?

This has been troubling me for some time. Thanks for any help!

J

 

I would use a VLOOKUP to determine which entries in the sorted table are not in the random table, and then copy and paste only the rows that are not to the end of the random table.

Is there a reason the randomly ordered table must remain so? You might need to sort it for the VLOOKUP to work correctly.

 

VLookup works fine to identify the additional rows. But my client is adamant in keeping the random order. There is no rationale whatsoever

 

If you can identify the additional rows, what's the problem? Just append and be done.

 

Do you have to do this often or every now and then? Do you have excel 2007 or 2010?

You could try the custom sort option and select custom list and the list would be the randomly sorted list which could just copy/paste column A into the custom list. I just tried it with about 10 lines and it worked perfectly.

 

I said Column A assuming it was distinct to each line(ie. a primary key) so whichever column is distinct to each line would be the custom sort list to sort the 100 row sheet.

 

I'm not sure if this helps, but the line of thinking would be to apply a static ordering to the random table and sort the other table by that static numbering.

 

Well, I guess you are trying to match all 10 columns? To simplicity's sake, say we have two columns. You will need to use array function.

Say in the "shorter" data, you have:

A W
B X
C Y
D Z

Say that is cell from A1 to B4.

The long data is:

A W
C O
Z P
B X
D Z
E Q
B R
C Y

Say that is cell E1 to F8, then you can get the position of a matching from shorter one in the longer one by:

{=MATCH(1, IF(A1=$E$1:$E$8,1,0)*IF(B1=$F$1:$F$8,1,0), 0)}

The curly bracket means it is a array function, just enter the formula as it is, and press control+enter, then it becomes an array function.

Above formula match the first record in your shorter data, and it return 1, since they match with 1st record in the longer data.

Hope that helps.