How do I combine 2 excel files so that the data for each item appears on one line?

Question

I have two files, one with data of about 2000 items (each different item is coded with a 3-4 letter acronym), the other with about 200 items (coded in the same way) but with different attributes. So for example the first file has AAAA: length 6 width 3 etc and the second file has AAAA: Weight 5.6 volume 7.8. I want to end up with all the data elements on the same line.

I'm using Excel and don't want to export to a database etc.

Thanks

Answer 1

VLOOKUP is what you want to use. Assuming the 2000 items contains the 200 items then the formula you want will be along the lines of:

=IF(ISERROR(VLOOKUP(cellref_of_ AAAA_in_big_range, small_range_with _AAAA_leftmost, column_of_info_required, FALSE)),"",VLOOKUP(cellref_of_ AAAA_in_big_range, small_range _with_AAAA_leftmost, column_of_info_required, FALSE))

replace the lower case stuff with what it describes in your workbook, and repeat for each column you want to return. This will populate those rows in the 2000 that have no match in the 200 with empty cells. To populate with zero instead replace the "" in the formula with 0.

Answer 2

you can just copy and paste it, so that you have all the data in 1 file. You then have to do a lot of work to change the layout, maybe exporting it to a database is much easier.

Answer 3

put both sheets in one workbook and use the vlookup function.

when you open the vlookup function it will explain how to use it.

It will do the search and paste based on the common info ex. AAAA

Answer 4

if you make the columns on each one the same, which is pretty easy. you can just cut and paste the whole database instead of just cutting and pasting each column(which could make it easy to mix match some of your info). Then you can sort it how ever you want.

Answer 5

You could try using the Merge facility - I use it for letters, labels, invoices, etc.