Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 229
  • Last Modified:

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
0
Modifier1000
Asked:
Modifier1000
  • 4
  • 4
  • 3
  • +1
4 Solutions
 
mark_harris231Commented:
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).
0
 
SteveCommented:
Yes...

Using either Vlookup or Index&Match

see attached
Example.xlsx
0
 
mark_harris231Commented:
Realized I misidentified columns.  Should have been A-D.  Formula should be:

=VLOOKUP(D2,$F:$G,2)  - in Column E
Vlookup.xls
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
mark_harris231Commented:
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".
0
 
SteveCommented:
@Mark... VERY IMPORTANT:

You MUST put the exact match '0' in the Vlookup or it will not get the right data all the time.
If there is not a record in the lookup table you want it to error or you will not see mis-matches.

so... =VLOOKUP(D2,$F:$G,2,0)
0
 
mark_harris231Commented:
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
0
 
Modifier1000Author Commented:
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
0
 
Modifier1000Author Commented:
Oh yeah, using Office 2010.
0
 
SteveCommented:
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)
0
 
xtermieCommented:
If you just want to sort and not retrieve any data from the original list, ou can actually do something quite simple without using any formulas.  Use the original list to create a custom sort order and then sort the re-order list by that custom sort order.
Excel Excel 2010:
 1.Click the File tab and select Options (under Help).
 2.Select Advanced in the left pane.
 3.In the General section, click Edit Custom Lists.
 4.In the List Entries control, enter each element in the sort order. Press Enter after each entry. (If the list is long or already exists as natural data, you can specify the cell reference and click Import).
5.Click Add and Excel will transfer the new custom sort to the Custom Lists control (on the left).
6.Click OK twice.
 
If you’re using Excel 2007, do the following:
 1.Click the Office button and click Excel Options.
 2.Click Popular in the left pane (the default).
 3.In the Top Options For Working With Excel section, click the Edit Custom Lists button.
 4.Continue with step 4 above.
 
To apply the custom sort, select the list you want to sort, click the Data tab, and then click Sort in the Sort & Filter group. Click the Order dropdown, choose Custom Lists, and select the new list list.

Sometimes it is better to use the inherent features of Excel rather than creating complex formulas and workarounds for functionality that exists.
0
 
Modifier1000Author Commented:
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...
0
 
Modifier1000Author Commented:
Thank you all!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now