Appending Data from One Sheet to Another

[richandjo]

Hello, all.

Sorry if this a simple one but I can't seem to figure it out.

I have 2 sheets of data. Sheet1 contains name and telephone number. Sheet2 contains name and email address. Note that there are more names in Sheet2 than in Sheet1.

I would like Excel to compare the two sheets and to identify which names that are in Sheet1 are also present in Sheet2 (ignoring any that show in Sheet2 only). I would then like Excel to transpose/copy/append (whatever the term is) email data from Sheet2 (but only for names that figure in Sheet1 also) onto the corresponding column in Sheet1.

I have attached an example book containing the 3 sheets, hopefully showing what I am trying to achieve.

Thanks, in anticipation, for your help.

Richard

[Pete_UK]

You can use this formula in cell C2 of Sheet1:

=IF(COUNTIF(Sheet2!$A:$A,A2),VLOOKUP(A2,Sheet2!A:B,2,0),"")

Copy down as required.

Hope this helps.

Pete

[richandjo]

You can use this formula in cell C2 of Sheet1:

=IF(COUNTIF(Sheet2!$A:$A,A2),VLOOKUP(A2,Sheet2!A:B,2,0),"")

Copy down as required.

Hope this helps.

Pete

Unbelievable.

Have been trying to figure this out for ages and, within 5 minutes, you've found the solution.

Thanks so much, Pete. Incredibly helpful.

Only remaining question is why I didn't ask here before!

Best wishes...

Richard

[Pete_UK]

Well, Richard, you've been a member of the forum longer than I have, but have only made 12 posts, so maybe you should visit here more often !!

Glad to be able to help, and thanks for the rep.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Pete

[alansidman]

An alternative solution with Power Query.

Load each of your ranges/tables to the Power Query Editor and then merge (Left Join) the two tables on the common name field

Please Login or Register  to view this content.

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

[richandjo]

Hi (again), Pete.

I'm moving the formula into the real sheets and am trying to follow what everything means (given that it's not working).

Sheet2!$A:$A - means the whole of Sheet2 column A?
What is the significance of the ,A2 after Sheet2!$A:$A? Is that referring to A2 of Sheet1 or Sheet2?
VLOOKUP(A2,Sheet2!A:B,2,0),"") - is the A2 referring to A2 of Sheet1 or Sheet2?
Sheet2!A:B - looking for data between the whole columns A and B in Sheet2?
A:B,2,0),"") - what is the significance of the 2 and the 0?

Sorry - and thanks.

[Pete_UK]

Hi Richard,

Yes, Sheet2!$A:$A does mean the whole of Sheet2 column A

If you see a cell reference without a sheet reference in front of it, then it refers to the cell within the sheet where the formula lies, so that does refer to the cell A2 within Sheet1.

Basically, the first part of the formula, i.e. COUNTIF(Sheet2!$A:$A,A2) is counting how many times the contents of A2 in Sheet1 occurs within column A of Sheet2. If this is a positive number (i.e. greater than 0) then the name in A2 does also exist within Sheet2 and so the second part of the formula (i.e. VLOOKUP(A2,Sheet2!A:B,2,0) is carried out, otherwise a blank ("") is returned.

The VLOOKUP function tries to find a match between the contents of A2 (on Sheet1, remember) and the first column given in the second parameter, i.e. column A of Sheet2. The 4th parameter (zero) means that the formula is looking for an exact match, and if one is found (which is bound to happen, as the first part of the formula determined that for us) then it will return data from the second column of the table (because of the 2 which is the 3rd parameter) which is on the corresponding row where the match is found.

I hope that explains what is happening more clearly, and that you can apply it to your real workbook. Any problems, just get back to me again.

Cheers,

Pete

[richandjo]

alansidman

Thanks for your reply.

I'm going to try Pete_UK's solution and see how I get on.