Filtering excel entries from one worksheet on another and copying information.

[herm0154]

Problem: I have two worksheets with about 4000 names, emails, and phone numbers. I have a third worksheet with about 200 email addresses. I need to find those 200 emails in one of the two sheets and copy their phone numbers. I need a help with a formula to do this without having to do control F for 200 email addresses. Any suggestions?

Thank you!

[Pete_UK]

To give you an exact formula to use, we need to know exactly which columns you are using in the sheets, so it would help if you attached a sample Excel workbook.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Note that the Paperclip icon does not work.

Hope this helps.

Pete

[herm0154]

I can't share the actual list because they have sensitive information... Would attaching a sample workbook with the matching cell headings help?

[herm0154]

I've attached a sample worksheet that matches the ones I am working on without the actual information.
Sheet Fi Contains 4000 entries.
Sheet Fii contains 3000 entries.
Sheet email list contains about 200 entries.

I need to match the email list entries to one of the two sheets (fi or fii) and copy TOS and phone number.

I hope this helps.

[Pete_UK]

Put this formula in F2 of the Email_list sheet:

=IFERROR(INDEX(Fi!$D:$D,MATCH(D2,Fi!$O:$O,0)),IFERROR(INDEX(FII!$W:$W,MATCH(D2,FII!L:L,0)),""))

and this one in G2:

=IFERROR(INDEX(Fi!$N:$N,MATCH(D2,Fi!$O:$O,0)),IFERROR(INDEX(FII!$K:$K,MATCH(D2,FII!L:L,0)),""))

then copy the formulae down as far as you need them.

Hope this helps.

Pete

[herm0154]

Excellent!
Thank you for your help!!

[Pete_UK]

You're welcome. In future, it would help if you put some dummy data into your sample file, so it can be tested out more thoroughly.

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 and mark this thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

[herm0154]

I have a question Pete_UK,
Is there a way to have the first formula search and copy the multiple occurrences of number associated with an email? So the current formula finds a number in the cell labeled TOS based on someone's email. But If an email comes up multiple times in FI or FII and has different values for the TOS column only one is copied.

Thank you for your help!

[Pete_UK]

Well, that's how the MATCH function works - it finds the first occurrence of what it is looking for (if it is there) and then stops looking for any others. You would need a different type of formula if you have multiple entries of the same lookup_value.

Please put some dummy data in your sample file to illustrate the kind of thing that you are describing, and what you want to achieve, then I can take a look at it.

Hope this helps.

Pete

[herm0154]

I uploaded a sample workbook.

Again, the original formula you created works great but I discovered that sometimes the email appears more than once in the FI and FII lists and I want to be able to record the TOS numbers that appear with the multiple emails.

[Pete_UK]

I've done a quick check, and there are some email addresses that appear 3 times in the Fi sheet, and some that appear twice in the Fi sheet and in the FII sheet, but none that appear in both sheets.

How do you want this to appear in your Email_list sheet? Do you want me to insert 2 or 3 columns between F and G, so that you will effectively have TOS_1, TOS_2, TOS_3 etc.?

I see that you have duplicate email addresses in the Email_list sheet, so do you want it so that the first address will pick up the first TOS, then the second will pick up the second TOS, and so on?

Pete

[herm0154]

Ideally, I would want the multiple TOS numbers in the same column in the email list separated by a comma (,). I think because the email list is going to be broken down into smaller lists, repeated emails will likely not be a problem. So the multiple TOS numbers is the primary concern.

[Pete_UK]

I've set this up in the attached file. I've use a helper column (column AH) in the Fi and the FII sheets, with this formula in AH2 of the Fi sheet:

=IF(O2="","",O2&"_"&COUNTIF(O$2:O2,O2))

and a similar formula in AH2 of the FII sheet:

=IF(L2="","",L2&"_"&COUNTIF(L$2:L2,L2))

The only difference is the column where the email address is. These formulae should be copied down to the end of the email list in each sheet, and they help to identify duplicates by tagging a sequential number onto the end of each email address.

In the Email_list sheet I have changed the formula in F2 to this:

=IFERROR(INDEX(Fi!$D:$D,MATCH(D2&"_"&COUNTIF(D$2:D2,D2),Fi!$AH:$AH,0)),IFERROR(INDEX(FII!$W:$W,MATCH(D2&"_"&COUNTIF(D$2:D2,D2),FII!$AH:$AH,0)),""))

and in G2 to this:

=IFERROR(INDEX(Fi!$N:$N,MATCH(D2&"_"&COUNTIF(D$2:D2,D2),Fi!$AH:$AH,0)),IFERROR(INDEX(FII!$K:$K,MATCH(D2&"_"&COUNTIF(D$2:D2,D2),FII!$AH:$AH,0)),""))

These will bring the appropriate TOS and Phone numbers across in the appropriate sequence. To clarify this further, I've applied conditional formatting to the cells to give a different colour for each occurrence of the email address, as the key shows.

Hope this helps.

Pete