compare/combine data from two worksheets into one

[beancounter]

I have 20,000 records in sheet 1 & the roughly the same amount in sheet 2. Both sheets 1 & 2 have a common field "account number".

On sheet 3, I want to look at both sheets 1 & 2, compare account number and where account numbers match up return the data (in various columns) from both sheets 1 & 2. What is the best way to do this?????

Thanks,
John

 

[iliace]

You need to use VLOOKUP, if your account number is to the left of the rest of the data. If it isn't, you can use an INDEX/MATCH combination.

So here's a simple example, because it's not very clear exactly what information you want:

Excel Workbook
	A	B	C	D	E	F	G
1	Account Number	Date	Balance		Account Number	Date	Balance
2	10100	5/21/2008	415.22		10100	5/21/2008	415.22
3	10200	5/22/2008	316.81		10500	6/4/2008	944.15
4	10300	7/6/2008	682.18		10800	6/29/2008	566.12
5	10400	6/21/2008	768.91
6	10500	6/4/2008	944.15
7	10600	5/21/2008	819.63		Account Number	Date	Balance
8	10700	5/19/2008	336.74		10200	5/22/2008	316.81
9	10800	6/29/2008	566.12		10300	7/6/2008	682.18
10	10900	5/31/2008	517.34		10900	5/31/2008	517.34
Sheet1

 

[beancounter]

Sorry for being vague - here's the layout

Sheet 1 Column a = Account # / Column b = Cust Name / Column c = Cust Address
Sheet 2 Column a = Account # / Column b = Amt Due / Column c = Due Date

Result I want

Sheet 3 Column a = Account # / Column b = Cust Name / Column c = Cust Address / Column d = Amt Due / Column e = Due Date

Not all account #'s are included on both sheets 1 & 2 because of new accounts, closed accounts etc. Just want to return data where both exist.

Thanks,
John