Solved

Merging Data from two Excel Workbooks using a Unique Identifier

Posted on 2013-11-02
13
4,004 Views
Last Modified: 2013-11-03
I have two large workbooks.  One of which contains names, a unique identifier, and certifications.  The other one has the same unique identifier, names and address information.  Since I am trying examine and sort the certifications by regions I need to have the certs and address info in the same sheet.

Is there a way to merge these two using say the unique identifier common to both so that I can work with the resultant file easier.

I have over 300,000 records in one of the files so they are pretty large.
0
Comment
Question by:StoneyJ50
  • 5
  • 4
  • 2
  • +2
13 Comments
 
LVL 15

Expert Comment

by:gplana
ID: 39619319
I would import these two workbooks into two Microsoft Access tables. Then I will join results by using a query on Microsoft Access, just joining them by the common field, and then import to a new Excel file.
0
 

Author Comment

by:StoneyJ50
ID: 39619340
I don't have MS Access. Prefer to try with Excel.
0
 
LVL 43

Assisted Solution

by:Saqib Husain, Syed
Saqib Husain, Syed earned 50 total points
ID: 39619352
You can use Vlookup to collect the addresses from the second sheet to the first sheet.
0
 

Author Comment

by:StoneyJ50
ID: 39619366
ah, never used Vlookup and I am not really inclined to stumble through it.  Not very intuitive. We'll see
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39619407
What sort of a solution were you looking for? VBA?
0
 
LVL 80

Expert Comment

by:byundt
ID: 39619448
To get either a formula or VBA solution to the problem, you'll need to define the layout of your two workbooks.

The best way to do this is to post each workbook. We don't need any more than a few rows of data, all of which could be fake if it needs to be confidential.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 10

Expert Comment

by:mark_harris231
ID: 39619870
Concur with ssaqibh - This is exactly the type of scenario for which VLOOKUP is designed.  It's reasonably straight-forward, but there are numerous tutorials online.  It is one of the mainstays of Excel formulas and is worth mastering.

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

- lookup_value: the cell location of the unique ID  (for demonstration, let's use Sheet1!A2)
- table_array: the range of cells in the table that contains the information you want to pull in (e.g., Sheet2!B2:C500)
- col_index_num: the location of the column containing the information you want to pull in.  If we assume from the example that the unique ID is in Column B and the certifications are in Column C, you would enter a "2" in this space (i.e., the 2nd column in the range B:C)

Pulling it together for this example, the VLOOKUP would be: VLOOKUP(sheet1!A2,Sheet2!B2:C500,2).  Your scenario is slightly more complicated in that you are referencing two separate workbooks, but VLOOKUP is able to accommodate this.  More detail about filenames/paths would be needed as byundt indicates.  However, I think it would be in your interests to attempt to master the VLOOKUP syntax.

(I prefer the INDEX/MATCH combination myself, but it is a bit more "intimidating" in appearance.  However for large spreadsheets, it can be significantly faster.)
0
 

Author Comment

by:StoneyJ50
ID: 39619896
Workbooks set up per attachment.  The unique identifier is common to both.  Workbook 1 ( 68,552 records) contains location info while Workbook 2 (303,957 records) contains mechanic certification status.  My goal is to have a workbook that has both location data and certification status on the same row for each unique id.
Workbook-1.jpg
Workbook-2.jpg
0
 
LVL 80

Expert Comment

by:byundt
ID: 39619922
I suggest using a formula in workbook 2 like:
=IFERROR(VLOOKUP($A2,'[Workbook-1.xlsx]Sheet1'!$A:$E,COLUMNS($D2:D2)+1,FALSE),"")

This formula may be copied down and across.

The equivalent formula in Workbook 1 is:
=IFERROR(VLOOKUP($A2,'[Workbook-2.xlsx]Sheet1'!$A:$C,COLUMNS($F2:F2)+1,FALSE),"")
Workbook-1.xlsx
Workbook-2.xlsx
0
 
LVL 80

Assisted Solution

by:byundt
byundt earned 450 total points
ID: 39619944
If you know that every Unique ID in Workbook-1 may be found in Workbook-2 and vice-versa, then you can use the much faster and simpler 3-parameter form of VLOOKUP as suggested by mark_harris231. It is faster because it uses a binary search as opposed to row by row. The possible pitfall is that an incorrect result will be returned if a Unique ID is present in one workbook but not the other.

The 4-parameter form of VLOOKUP returns an error value if a Unique ID is present in one workbook but not the other. That's why I put the VLOOKUP inside an IFERROR function to return an empty string (looks like a blank) when there was no match.

If you are changing data in the workbooks, then there may be an annoying recalc delay for all the VLOOKUP formulas to update. The following formula gives both speed and accuracy, albeit at the cost of being harder to understand. Note that the data needs to be sorted in ascending order by column A--as your sample data appeared to be. The formula for Workbook-1 is:
=IFERROR(IF(VLOOKUP($A2,'[Workbook-2.xlsx]Sheet1'!$A:$A,1)<>$A2,"",VLOOKUP($A2,'[Workbook-2.xlsx]Sheet1'!$A:$C,COLUMNS($H2:H2)+1)),"")

The equivalent formula for Workbook-2 is:
=IFERROR(IF(VLOOKUP($A2,'[Workbook-1.xlsx]Sheet1'!$A:$A,1)<>$A2,"",VLOOKUP($A2,'[Workbook-1.xlsx]Sheet1'!$A:$E,COLUMNS($H2:H2)+1)),"")
Workbook-1.xlsx
Workbook-2.xlsx
0
 

Author Comment

by:StoneyJ50
ID: 39620007
Every id in Workbook 1 is in Workbook 2.  But not every id in Workbook 2  is in Workbook 3.
I have never worked with VLOOKUP   Do I just paste the formulas in no matter what cell is selected?
0
 
LVL 80

Accepted Solution

by:
byundt earned 450 total points
ID: 39620025
Please take a look at the sample workbooks I posted. They show exactly how to use the formulas.

=IFERROR(VLOOKUP($A2,'[Workbook-1.xlsx]Sheet1'!$A:$E,COLUMNS($D2:D2)+1,FALSE),"")
The reference to $A2 should be to a Unique ID in the same row as the formula. In other words, if you put the formula in cell D4, you should be using $A4.

The reference to Workbook-1.xlsx should be to the name of the other workbook. Sheet1 should be the name of the worksheet in that other workbook that contains the data. The reference to columns $A:$E should be to the lookup table (Unique ID, City, State, Zip & Area) in the workbook and worksheet.

The reference to COLUMNS($D2:D2) should be to the top left cell containing the formula. You will copy down and across to get the other information.

To copy a formula down and across:
1.  Put the formula in the top left cell of the range that will get formulas
2.  Select that cell
3.  Drag the little square in bottom right corner of the selection marquee across as many columns as you need
4.  Double-click the little square to copy the formula down as many rows as you have data
0
 

Author Comment

by:StoneyJ50
ID: 39620417
thank you that worked
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now