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: 8339
  • Last Modified:

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.
0
Scott Jackson
Asked:
Scott Jackson
  • 5
  • 4
  • 2
  • +2
3 Solutions
 
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
 
Saqib Husain, SyedEngineerCommented:
You can use Vlookup to collect the addresses from the second sheet to the first sheet.
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
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
 
byundtCommented:
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
 
byundtCommented:
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
 
Scott JacksonRetired Air Force Jet JockAuthor Commented:
thank you that worked
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

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