Joining spreadsheets based on Client Id Number

Hi

I have two spreadsheets .

Spreadsheet 1 has sales rep name , client id , name of client

Spreadsheet 2 has client id ( not in same sequence as spreadsheet 1 ) , client name , client address , tel numbers

I would like to combine spreadsheet 1 , i.e. rep name , client id , name of client together with address and contact details on spreadsheet 2  . Spreadsheet one is based on client id , and only contains active client id numbers , whereas spreadsheet 2 contains all existing client id numbers .

How do I do it ?

March 19th, 2015 11:19am

Assuming Client ID is in column B of both SS1 and 2, and by spreadsheet you mean separate files, not separate tabs within one workbook.

Open both spreadsheets, then in SS1 type in a cell on row 2 of the first blank columns to the right of your data:

=VLOOKUP($B2,

then navigate to SS2, and select the entire columns of information with Client ID as the first column of the selection, and press F4 until you get all $ for both columns, (should look like this:

=vlookup(B2,'[File Name.xlsx]Sheet Name'!$B:$E

and then type 

, COLUMN(B2), False)

and press Enter. Then copy that cell down and to the right to match your data set and extract as many columns of data as you have.

Free Windows Admin Tool Kit Click here and download it now
March 19th, 2015 2:38pm

In addition to the vlookup solution, you can consider using PowerPivot or Power Query. For large datasets vlookup will heavily consume resources, and will slow down your interactivity whenever your workbook is recalculated.

March 20th, 2015 7:36am

In addition to the vlookup solution, you can consider using PowerPivot or Power Query. For large datasets vlookup will heavily consume resources, and will slow down your interactivity whenever your workbook is recalculated.

Free Windows Admin Tool Kit Click here and download it now
March 20th, 2015 11:35am

Excel 2010 with free Power Query Add-In.
Compatible with Office 2013 Pro Plus.
As Gil so cogently commented, Power Query offers many advantages.
PQ also dispenses with old-fashioned reference designators, like $AK$47.
http://www.mediafire.com/view/j8er32s7bo91395/03_20_15.xlsx

March 20th, 2015 4:14pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics