Modifier1000
asked on
Re-order data set to match another data set.
Hello Experts,
I had a similar issue before, but I cannot figure out how to accomplish this new issue. I have two data sets in one worksheet. The first data set (left side) has about 4000 records and the second data set (right side) has about 1000 records.
Each data set has its own 'key' column. The keys on the right side do match a key on the left side. If I just reorder the data sets from highest to lowest, I will still have to physically move the data on the right side down until it matches the key on the left side because there are 3000 less records in no particular order. Is there a way to have the data set from the left match the keys and place the corresponding data next to the left side data set?
Image attached.
Matching-data.png
I had a similar issue before, but I cannot figure out how to accomplish this new issue. I have two data sets in one worksheet. The first data set (left side) has about 4000 records and the second data set (right side) has about 1000 records.
Each data set has its own 'key' column. The keys on the right side do match a key on the left side. If I just reorder the data sets from highest to lowest, I will still have to physically move the data on the right side down until it matches the key on the left side because there are 3000 less records in no particular order. Is there a way to have the data set from the left match the keys and place the corresponding data next to the left side data set?
Image attached.
Matching-data.png
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
As Barman indicates, INDEX(MATCH()) is another option and definitely one to learn, especially if this list could/will grow over time. If it's a one-and-done fix of a finite data set, VLOOKUP is a bit more intuitive to "quick-fix".
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Ah yes - working with a handicap today (Excel 2000) and didn't get the formula format prompt. Perhaps grown a bit too dependent on that handhold.
Thanks for the catch, Barman
Thanks for the catch, Barman
ASKER
Well, I did what Mark said at the top response. I did add an extra column so match the equation.
I did try adding the response from Barman, but the results were all zeros. The cells with a red box are incorrect matching. The ones with the blue dots are correct. The results should be unique and not repeated.
Attached is a screenshot of Mark's first response.
VLOOKUP-first-try.png
I did try adding the response from Barman, but the results were all zeros. The cells with a red box are incorrect matching. The ones with the blue dots are correct. The results should be unique and not repeated.
Attached is a screenshot of Mark's first response.
VLOOKUP-first-try.png
ASKER
Oh yeah, using Office 2010.
Could you post a workbook, or include column headings please.
you need to have the formula:
so if data is in A:D and Lookup in F:G then in E2:
=VLOOKUP(D2,F:G,2,0)
you need to have the formula:
=VLOOKUP(<cell with value to match> , <columns with values match must be column 1> , <number of columns across for value to find>, 0)
so if data is in A:D and Lookup in F:G then in E2:
=VLOOKUP(D2,F:G,2,0)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The mark_harris231 and The_Barman worked on my test computer. Going to try it on my production machine shortly.
I'll try out the xtermie way too.
Feedback coming shortly...
I'll try out the xtermie way too.
Feedback coming shortly...
ASKER
Thank you all!
Assuming your left-side data is in columns A-E, and your right-side data is in columns F & G, select column F and insert a column (moving right-side data to columns G & H).
In Cell F2, paste the following formula: =VLOOKUP(E2,$G:$H,2) - copy down for as many records as you have in Column E. Now, any match of values in Column E will pull the company name from Column H. Once finished, you can perform a copy/paste special- values for Column F to convert the formulas to values (and delete columns G & H if desired).