• 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.

• 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

• Quote from jindon

If you are not familier with what firefytr suggested...

Good suggestion. Although personally I'd just opt for Advanced Filter. It may take a little bit to learn how to use, but will take 3 seconds to complete this task when armed with it. It's use is unparalleled in situations like this.

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

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!