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.

    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

    2) extract all the ID in columnA
    in cell A2

    3) display row number if value in col.A is unique
    in cell B2

    4) extract unique ID
    in cell C2

    then select A2:C2 and drag down as you desire

    copy entire columnC and pastespecial/value


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