combine two spreadsheets based on one unique key

[kategorgeous]

hello, I have two spreadsheets with two different sets of information about each account that I need to combine. For example:

Account number A001 Mr Smith has address details in one spreadsheet
Account number A001 Mr Smith has his purchase details in another spreadsheet.

I want to combine Mr Smith's details all into one spreadsheet that I can then import into my Filemaker database. How do I do this?

Excel version 2000 (yeah ... I know!)
Thanks

Kate

[Olly]

Use the vlookup function, to match the Account number.

If you provide samples of your workbooks, I can provide a more detailed formula.

[ben_hensel]

I would sort by account number so they're in the same order, then copypaste one spreadsheet's data smack next to the other's data. Then saveAs new file name.

Then I'd check that I've got them ordered right by filling a helper column with =accountNumber_1 = accountNumber_2 and filter for FALSE (none = good).

Then delete the helper & duplicate columns and save and be done, basically.

Or you could move both sheets into the same workbook, connect them with
sheet1!C1 = sheet2!A1
and pull over&down to to get all the data on one page, then copy/paste values in place, then as above.

Well...
Are there duplicates or missing rows so that the ranges are different? That would require handling too.

[kategorgeous]

Thanks - here are samples of my spreadsheets, I could do with some help using the VLOOKUP
function if that's the way forward.

[Olly]

To add customer Name, Contact Name and Email to your sales spreadsheet:

In workbook [stock_sample.xls] worksheet [Product Sales By Customer] cell F2, enter:

Please Login or Register  to view this content.

In cell G2:

Please Login or Register  to view this content.

In cell H2:

Please Login or Register  to view this content.

Copy these formulae down, to the end of your data range.

[kategorgeous]

FABULOUS! Thank you Thank you! I now have exactly what I wanted. This has to be the best forum on the planet. Happy Christmas from a very happy elf!

[arlu1201]

Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

In future, to mark your thread as Solved, you can do the following -
Select Thread Tools-> Mark thread as Solved.

Incase your issue is not solved, you can undo it as follows -
Select Thread Tools-> Mark thread as Unsolved.

Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.