Merging Data from two Excel Workbooks using a Unique Identifier

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.
Scott JacksonRetired Air Force Jet JockAsked:
Who is Participating?
 
byundtConnect With a Mentor Commented:
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
 
gplanaCommented:
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
 
Scott JacksonRetired Air Force Jet JockAuthor Commented:
I don't have MS Access. Prefer to try with Excel.
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
Saqib Husain, SyedConnect With a Mentor EngineerCommented:
You can use Vlookup to collect the addresses from the second sheet to the first sheet.
0
 
Scott JacksonRetired Air Force Jet JockAuthor Commented:
ah, never used Vlookup and I am not really inclined to stumble through it.  Not very intuitive. We'll see
0
 
Saqib Husain, SyedEngineerCommented:
What sort of a solution were you looking for? VBA?
0
 
byundtCommented:
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
 
mark_harris231Commented:
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
 
Scott JacksonRetired Air Force Jet JockAuthor Commented:
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
 
byundtCommented:
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
 
byundtConnect With a Mentor Commented:
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
 
Scott JacksonRetired Air Force Jet JockAuthor Commented:
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
 
Scott JacksonRetired Air Force Jet JockAuthor Commented:
thank you that worked
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.