merge 2 spreadsheets

[consultant]

I have two spreadsheets (transactions & customers) that have a common field (customer number).

I want to merge the data, so that each transaction shows the full customer info (in some cases, there may be a couple transactiosn per customer).

It has been a long time since I had to do something like this and I could probably do it if I pulled it into Access (a VERY long time since I have used this), but I wondered it is possible in 'nataive' Excel.

Any spreadsheet whizzes out there?

[cmcp]

I think what you're looking for is VLOOKUP.

You're basically saying stick this content with that content where they both share this field, or something like that.

We used to use it to update our price csv. But it always went wrong so I'll leave it to the experts.

[spreadsheetsdirect]

Yep, a vlookup will do it. On the sheet you want to bring the data into enter =vlookup(cell with data to match,range on other file to do the match in, column number of data to return, false for an exact match)

so you'll get something like =vlookup(a2,'[other file.xls]Sheet1'!$a:$f,2,FALSE)

[jadexsoln]

Will that return many results for 1 record, doesn't VLOOKUP only return the first match it finds in the look up table? I think consultant is saying that that he as One to Many relationship.

:|

[consultant]

I think I am saying 'one to many'!

The list of customers is unique, however, the transaction may have several referenced to one customer.

I may try digging out access again!

[spreadsheetsdirect]

If you want the transaction file to show transactions plus customer details then a vlookup will do it.

[jadexsoln]

You can do it in Excel since this has the database functions that Access has, so you can do all the normal INNER and OUTER joins and so forth.

Basically, from memory you define each range as a data range then a third range as the query (the SQL) that should be done and output it to a 4th range........ messy I know

Unfortunately since I've converted to OO I no longer have access (excuse the pun) to MS Excel.

However in Access it would take you 5 mins tops.............. :D

[jadexsoln]

Ahh my mistake it's MANY to ONE, therefore Phil and cmcp are correct, VLOOKUP is the function you are after!

:redface:

[consultant]

Guys, I have managed to work out how vlookup works - daunting at first, obvious when you know how.

Thanks for your pointers and offers of assistance.