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?

Improve this question

1 Answer 1

Reset to default
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).

Improve this answer
6
  • The way I read the question, it sounds like they want the result to be both B & D phone numbers together.
    – CharlieRB
    May 21, 2013 at 11:46
  • 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
    May 21, 2013 at 11:51
  • 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
    May 21, 2013 at 12:01
  • 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
    May 21, 2013 at 12:19
  • 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
    May 21, 2013 at 16:04

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .