Excel Help, Match Sort Tables
#1
I don't have a Ferrari in
Thread Starter
Excel Help, Match Sort Tables
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 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
#2
Team Owner
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.
Is there a reason the randomly ordered table must remain so? You might need to sort it for the VLOOKUP to work correctly.
#3
I don't have a Ferrari in
Thread Starter
VLookup works fine to identify the additional rows. But my client is adamant in keeping the random order. There is no rationale whatsoever
#4
Team Owner
#5
Drifting
iTrader: (1)
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.
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.
#6
Drifting
iTrader: (1)
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.
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.
Trending Topics
#8
Burning Brakes
Join Date: Jan 2001
Location: New York
Posts: 947
Likes: 0
Received 0 Likes
on
0 Posts
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.
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.
Last edited by uu; 10-18-2011 at 05:50 PM.
Thread
Thread Starter
Forum
Replies
Last Post
08_UA7_Gr33k
Member Cars for Sale
13
02-11-2016 02:17 PM
InFaMouSLink
Non-Automotive & Motorcycle Sales
2
11-11-2015 12:19 PM
LogicWavelength
3G TL Photograph Gallery
33
11-01-2015 09:38 AM
InFaMouSLink
Car Parts for Sale
6
10-27-2015 06:52 PM