Copying data from one sheet to another: only if it's not already on the first sheet?

Hi everyone,

I've been googling and googling this question and can't seem to come up with an answer. What I'm trying to do is; copy data from one sheet to another only if that data isn't already on the first sheet.

Example:
Sheet 1 column a has 1) bars of soap 2) windex. Column b has 1) $2 2) $10.

Sheet 2 has; 1) bars of soap 2) windex 3) detergent. Column b has; 1) $2 2) $10 3) $5

How would I get the data from sheet 2 that's missing from sheet 1 (the detergent and $ 5) onto my first sheet? I'm assuming some if statements and lookups are used?

Thanks so much,
Atwellj

Try this.

Vlookup

To copy only the filtered data, press F5 key > Special > Check Visible Cells > Ok. Then copy and paste to the required cells.

Pl see attached file.

Thanks for the responses guys. I'm still having a bit of trouble.

kvsrinivasamurthy, i know its just a matter of a few tweeks but the formulas is bit out of my league currently. The criteria i want searched through is in column C (C5 - C80) of sheet one. the criteria i want brought across is in Column D of sheet 2 (D4 - D250). I've tried editing your formula but keep getting told I'm creating a circular reference.

I'm sorry i cant upload the actual spread sheet its got a lot of confidential business data. so I've made a bit of a mock up. attempt1.xlsx

Thanks again for all the help.

ARRAY formula in C2, then drag down.

Please Login or Register  to view this content.