Link to home
Start Free TrialLog in
Avatar of Modifier1000
Modifier1000Flag for United States of America

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
Avatar of mark_harris231
mark_harris231
Flag of United States of America image

If you're not opposed to using an additional column, you can do this by way of vlookup.

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).
ASKER CERTIFIED SOLUTION
Avatar of Steve
Steve
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
Avatar of Modifier1000

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
Oh yeah, using Office 2010.
Could you post a workbook, or include column headings please.

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)

Open in new window


so if data is in A:D and Lookup in F:G then in E2:

=VLOOKUP(D2,F:G,2,0)
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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...
Thank you all!