# Combining data from two excel workbooks into one workbook, Excel 2007



## LG2 (Jan 25, 2010)

This question has been asked many times, in different ways ... and none of the answers help me. So, here we go, again.

Excel 2007

I have 2 excel workbooks. There is different information in each workbook, but it is about the same accounts. Both workbooks have some of the same information in the columns:
Unit # : Record # : Name : Start Date : End Date.
Then .... in one workbook I have columns with billing information (approx 5 columns) and the other workbook has misc info (approx 5 columns). Each individual record is one row of data. I want to combine the information in to a separate workbook where it would have (accross one row, without duplicating data): 

Unit # : Record # : Name : Start Date : End Date : billing 1 : billing 2 : billing 3 : misc 1 : misc 2: misc 3: ....etc.


Can anyone help me?
Thank you!


----------



## mduffel (May 11, 2011)

Are you wanting to just do this once so you have all the data in one sheet?


----------



## etaf (Dec 28, 2008)

you could use " vlookup " to bring that information together - if its a one off - otherwise a macro - assuming some unique information to identify the user - across all three workbooks

which fields could be used as unique ?
User , record - to make the unique record you could combine the fields


----------



## LG2 (Jan 25, 2010)

Yes, I would be combining this information to have it all in one spread sheet.

Both reports will have the same, unique, identifiers. For each line of date, in both reports, the the record number is the same, unique to each account.


----------



## etaf (Dec 28, 2008)

then you should be able to use vlookup and the record as a reference 

any chance of some sample data in spreadsheets and i'll work out the code for a vlookup 

but it will be 

=vlookup( cell for the - record number , range in other spreadsheet , column number in other spreadsheet to return, false-to find an exact match)


----------



## LG2 (Jan 25, 2010)

P.S. I am completely unfamiliar with Macros.


----------



## etaf (Dec 28, 2008)

i was not thinking of a macro - not my area of expertise these days been a long while since i wrote those :smile: - so i was thinking of a vlookup function to do this once


----------



## LG2 (Jan 25, 2010)

Thank you for your offer of help. I have put together a bit of an example. Of course, it it very condensed. The data I have to work with is over 1 year worth of information. And, the current column count is up to "AO". Also, since these are 2 different types of reports, not all clients and client numbers will be on both sheets. I just want to bring over 2 columns, Payor 1 & Payor 2, into the client info sheet and have them tag into colums AP & AQ, only if the client # matches. If there are blanks in the payor 2 column, I need to have that reflected. I can have the 2 reports in seperate workbooks or I can place them in 1 workbook, whichever would be easier to work with.

I just tried to attach my excel spreadsheet, but I keep getting "invalid file". What format do you need?


----------



## etaf (Dec 28, 2008)

probably a zip file here


> Valid file extensions: bmp doc gif jpe jpeg jpg pdf png psd rar txt zip


so you should be able to zip the files UP 

I assume


> Also, since these are 2 different types of reports, not all clients and client numbers will be on both sheets.


BUT


> Both reports will have the same, unique, identifiers. For each line of date, in both reports, the the record number is the same, unique to each account.


so there will be a unique identifier


----------



## LG2 (Jan 25, 2010)

You are correct on both statements.


Here is the workbook.


----------



## etaf (Dec 28, 2008)

ok, 
so on the all info sheet 
I have copied the "client info" sheet into the "all Info" sheet - by using a simple = cell 
then to match the "payor info" sheet i have used vlookup 
=VLOOKUP($B3,'Payor Info'!$B$2:$F$315,1,FALSE)
this copies the client #
then the next column copied over 
=VLOOKUP($B3,'Payor Info'!$B$2:$F$315,2,FALSE)
copies start date 
Note the difference
=VLOOKUP($B3,'Payor Info'!$B$2:$F$315,*2*,FALSE)
so that looks up the 2nd column of data

BUT only 1 payor info matches on this sample 

i have attached the spreadsheet

i hope that helps a little 

If you then wanted to just have the data in the spreadsheet rather than the formula
you can do a copy and paste special - value


----------



## LG2 (Jan 25, 2010)

Thank you. I am going to try to work with this formula to see how I do. 

As an aside, the reason only one client # matched was probably because I have over 10,000 lines of data and I probably didn't copy over enough matching data.


----------



## etaf (Dec 28, 2008)

> As an aside, the reason only one client # matched was probably because I have over 10,000 lines of data and I probably didn't copy over enough matching data.


 yep, I assumed 

I have excel - 2003 
range is columns upto IV
and rows upto 65,536

if this is a one off - I can attempt to setup for you


----------



## LG2 (Jan 25, 2010)

A bit of confusion ... you have "B2" referenced in your formula. But, B2 is a column title. Or, are you using that to pull the title, as well as the data over?


----------



## etaf (Dec 28, 2008)

B2 is for the look up range - so yep, it should be B3 
not that it will make a lot of difference as the title will never be looked up


----------



## etaf (Dec 28, 2008)

the $ 's are to stop the column and row changing when its copied


----------



## LG2 (Jan 25, 2010)

Thank You!!!!
When I tried to use Excel for the formula template ... well, let's say I won't make that mistake, again. But, when I typed in the formulas, it worked perfectly (or, so I think).
This is what I ended up with ... does it look ok?
=VLOOKUP(B3,'Experiment for CC report cost p'!B:E,1,FALSE)
=VLOOKUP(B3,'Experiment for CC report cost p'!B:E,2,FALSE)
=VLOOKUP(B3,'Experiment for CC report cost p'!B:E,3,FALSE)
=VLOOKUP(B3,'Experiment for CC report cost p'!B:E,4,FALSE)
where there were no matching ID numbers, it returned a "#N/A" in the cell


----------



## etaf (Dec 28, 2008)

looks good - maybe worth checking a few samples 
if you want to get rid of the #n/a we can do that with an IF and ISERROR statement - means changing all the formula


=IF(ISERROR(VLOOKUP($B5,'Payor Info'!$B$2:$F$315,1,FALSE)),"",VLOOKUP($B5,'Payor Info'!$B$2:$F$315,1,FALSE))

so you add the IF 
then the ISERROR ( ) 
which test for #N/A
and copy the formula for vlookup into the ( )
then , 
now what you want to enter in the cell if an #N/A
blank cell is ""
then , 
and what to do if no error 
so the formula

the false looks for an exact match with out the false - it will find the nearest match


----------



## RSpecianjr (Jan 20, 2010)

Hey everyone,

You can also use:

=IFERROR(VLOOKUP($B5,'Payor Info'!$B$2:$F$315,1,FALSE),"")

This will speed it up a good bit so it doesn't have to calculate the VLOOKUP twice for each formula. = )

Regards,

Robert D. Specian Jr.


----------



## etaf (Dec 28, 2008)

*RSpecianjr* thanks for that , new there was a better way as i was writing, but had a bit of a brain seize


----------

