How can I merge two sheets?

J

JD

Hi, I could use some help with the following problem. I have two
sheets which I need to merge into a third.

Sheet 1 (Sold):
DATE AMOUNT
01-Jan-04 8
02-Jan-04 6
05-Jan-04 8

Sheet 2 (Bought)
DATE AMOUNT
01-Jan-04 8
02-Jan-04 6
03-Jan-04 8

Combine into this sheet:
DATE BOUGHT SOLD
01-Jan-04 8 8
02-Jan-04 6 6
03-Jan-04 8 0
05-Jan-04 0 8

So this new sheet contains a row for each date that is in either of
the two sources.

Thanks for your help.
 
F

Frank Kabel

Hi
if this is a one-time operation you may try the following:
- create a new sheet and copy column A (the dates of both
sheets (Sold + Bought) into this new sheet directly below
each other
- select the date range on this new sheet and goto 'data -
filter - Advanced Filter'. Check 'Unique Values' and
create a new list of only unique dates.
- Use this newly created list, sort if (it you like)

Now add the following two formulas in the second row
assumption: row 1 is a heading row):
B2:
=VLOOKUP(A2,'Bought'!$A$1:$B$1000,2,0)

C2:
=VLOOKUP(A2,'Sold'!$A$1:$B$1000,2,0)
copy both formulas down.

After this you may select column B+C, copy them and insert
them again as values (Edit - Paste special) to remove the
formulas
 
Top