How do I create a list of phone numbers for people whose reference numbers appear in two columns?

1

I have two sets of data in columns A & B and also in columns C & D. Some of the content in column C & D does not appear in columns A & B:

A = reference numbers
00001, 00002, 00003, 00004

B = phone numbers for column a people
123456789, 234567890, 345678901, 456789012

C = reference numbers (these contain SOME of the reference numbers that appear in column A)
00001, 00002, 00004
(NOTE: 00003 is missing)

D = phone numbers for column C people (these contain SOME of the phone numbers that appear in column B)
123456789, 234567890, 456789012
(NOTE: the phone number 345678901 is missing)

How would I create a separate column that contains the phone number from column B of anyone whose reference numbers appear anywhere in BOTH columns A and C?

Simon

Posted 2013-05-21T10:14:37.747

Reputation: 13

Answers

1

In column E, enter =IF(COUNTIF($A:$A,C1),B1,""). Then drag this formula down as far as you need.

What this is doing is counting the instances of the reference number in column C against the entire column A. If it finds one or more instances, it outputs the phone number found in column D, else it displays a blank.

You can then apply a filter on column E, uncheck blanks, copy the column, remove filter and paste in column F to get a continuous list without blanks (and after this can safely delete column E).

Amer

Posted 2013-05-21T10:14:37.747

Reputation: 1 189

The way I read the question, it sounds like they want the result to be both B & D phone numbers together. – CharlieRB – 2013-05-21T11:46:55.593

I had thought of that possibility, but in the example they give, the phone numbers correspond to the reference numbers. So I assume ref 0001 will have the same phone number in either column B or D. But if it is the other way around Simon, please comment :) Also mention how you would like the two numbers split, i.e. comma, or space, etc – Amer – 2013-05-21T11:51:41.810

Hi, thank you for your help so far. To clarify, a person will have the same phone number and reference number in columns B and D. If the reference number appears in both columns A and C, then we just need the phone number that appears in column B to appear in a new column. – Simon – 2013-05-21T12:01:44.840

Ok, so my original formula stands, but made an edit to the answer to change the D1 to B1. Though it should not make a difference according to your rules. – Amer – 2013-05-21T12:19:16.920

Thank you. This works perfectly. The next challenge is how would I be able to expand this formula on another spreadsheet with the same columns? On this one, I would like to do all the above (so i'm using the =IF(COUNTIF($A:$A,C1),B1,"") formula) I would like to add fields to Column E so that it also lists the telephone number that appears in Column B of any person's reference number (e.g. 00005) that appears in Column C but does not appear in Column A? – Simon – 2013-05-21T16:04:21.757

@Simon, I'm not sure whether this new situation is clear. You have phone numbers in column B without reference numbers in column A? Maybe posting a new question would be appropriate. – Jerry – 2013-05-26T11:31:15.013

Asked: 2013-05-21T10:14:37.747

Viewed: 803 times

Active: 2013-05-21T12:17:59.383