How to combine two excel files?

**dreamgirl** · 10-06-2006, 09:09 AM

Hello,
I have got two excel files with one common filed name.The first file has account number,name,city and state.And the 2nd file has got account number and status fields.I want to combine both these excel files into one file by using the common filed account number.Can someone please help me with this.I'm not an expert in excdel so please answer in detail.

many thanks

**Dav** · 10-06-2006, 11:06 AM

You need to provide a few more details. do the account numbers feature once in each sheet? or do they appear more than once in one of the sheets

In terms of creating a solution, how big are the files? How many lines in each?

Regards

Dav

**dreamgirl** · 10-06-2006, 11:20 AM

Hi dav,

Thanks for your reply.
Well, each customer has got a unique account number and so there is no
repetition of it in either of the files.let me explain with an example...
file 1 contents given below

acc.no. name city
1011 ab cd
2945 hgds kf
6373 hdka kshd

file 2 contents
acc.no amount
4373 9837
4928 84399
1011 38434

what i want to do is to match these two files with the common field called account number and put all the details in one file which contains acc.no, customer name, city and amount.

the file size is quite big...around 30k rows...

many thanks

**Dav** · 10-06-2006, 01:03 PM

Well if both files are the same length you could just sort both of them by acc no then copy and paste the results of one file into the cells of the other file to the right. Or if sorting is not an option

in the first workbook cell d2 put something like

=VLOOKUP(A2,'[secondworkbook.xls]sheet1'!$A$2:$B$3000,2,FALSE)

obviously you need to have the correct names of the sheets and the workbooks.

The formula can then be copied down to d3000 and the data is combined

If you select all the formulas and the copy and paste them on top of each other as values the join is made permanent

Regards

Dav

**Myles** · 10-06-2006, 01:28 PM

Another way:

In file Insert 2 columns between Column A and Column B such that your data display becomes:

ColumnA ColumnB ColumnC ColumnD
-------- -------- -------- --------
AcctNo <blank> <blank> amount
4373 <blank> <blank> 9837
4928 <blank> <blank> 84399
1011 <blank> <blank> 38434

Next, copy from the 2nd row down and append data onto contents of File1.
Supply the heading AMOUNT in columnD of file1.

Then, in a general module in File1, insert the following code and run same.

Please Login or Register  to view this content.

**Ruso2006** · 10-06-2006, 05:07 PM

Hello,
Normally I use function SUM (F15* C$11, F15* C$11, F15* C$11)
in order to conduct this operation in a sheet, nevertheless when I use this function to the same with cells in the same position, but in different sheets from a book , I obtain character limits error. (SUM('1'!F15*'1'!$C$11,'2'!F15*'2'!$C$11,'3'!F15*'3'!$C$11))
Exists some function or VB method to do that simpler? Ej. 3D reference.

Many thanks