Combining 2 Excel spreadsheets

Hi, I have a question, I have two different spreadsheet, I want to take just one column of both spreadsheets to a 3rd sheet and then delete out all duplicates on that 3rd sheet.

the one column on both sheets is ID#s, one is from a prior date and one is from a future date. Some ID's might not be in the prior date and some might not be in the future date and some are in both. So how do I combine the two different spread sheets to give me a ID listing of both dates without duplicating the ID's.

I want to delete out the rows and keep only the data, that just hides the information, also this would be linked to another sheet, so if deleted, I would get reference errors on the 4th sheet. Anyway of running a macro to do all this and then pasting on the third sheet so this would avoid the reference error. Thanks.

Quote from jindon

Hi,

If you are not familier with what firefytr suggested, here's another solution.
I'm not sure if you are familier with the formula though.

Assuming:
Sheet names are Sheet1 and Sheet2 respectively
ID column in both sheets are in column A and has heading in 1st row
Extract desired data in Sheet3

With Sheet3
1) Set lookup table in range E1:G3
E1:0, F1: Sheet1!, G1:0
E2:=COUNTA(Sheet1!A:A), F2:Sheet2!, G2:=E2-1
E3:=COUNTA(Sheet2!A:A)+E2,

2) extract all the ID in columnA
in cell A2
=IF(ROW()<MAX(E:E),INDIRECT(VLOOKUP(ROW(A1),$E$1:$G$2,2,TRUE)&"a"&ROW()-VLOOKUP(ROW(A1),$E$1:$G$2,3,TRUE)),"")

3) display row number if value in col.A is unique
in cell B2
=IF(AND(A2<>"",COUNTIF($A$1:A1,A1)=1),ROW(),"")

4) extract unique ID
in cell C2
=IF(ROW(A1)-1<COUNT(B:B),INDEX(A:A,SMALL(B:B,ROW(A1)),1),"")

then select A2:C2 and drag down as you desire

copy entire columnC and pastespecial/value

jindon

Display More

works very nicely jindon, thanks for your help, only problem is that it takes years to recalc. I guess formula is too complicated.