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.

  • 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


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