Extract people using numbers mixed with text over different columns

Question

For a sheet/table:

+--------+-------------+------------+----------------+
| Person |    Diag1    |   Diag2    |     Diag3      |
+--------+-------------+------------+----------------+
| A      | 431 - TB    | 652 - PLA  |                |
| B      | 614 - Cough | 884 - Cold | 952 - Headache |
| C      | 747 - BLA   | 949 - POP  |                |
+--------+-------------+------------+----------------+

I have a lookup column:

+------+
| Diag |
+------+
|  431 |
|  650 |
|  949 |
|  555 |
|  484 |
+------+

For each person, if any number in the Diag lookup column lies in any of columns Diag1, Diag2, or Diag 3, that person gets selected from the original table with all associated columns with just the numbers in them.

In this case, sample output:

+--------+-------+-------+-------+
| Person | Diag1 | Diag2 | Diag3 |
+--------+-------+-------+-------+
| A      |   431 |   652 |       |
| C      |   747 |   949 |       |
+--------+-------+-------+-------+

@ScottCraner Lookup for each individual per row. So after scanning for each person, if the lookup no. is missing in Diag 1,2,3 for say Person B, Person B is out. — AS91, Apr 26 '16 at 15:55
With the above data what show what your expected outcome would be. Do this in the original post. — Scott Craner, Apr 26 '16 at 15:57
@findwindow I couldn't find a matching thread in related questions or recent tags. — AS91, Apr 26 '16 at 16:18
Er ok. Your question is actually very poor. It doesn't say what you mean at all per your desired output... — findwindow, Apr 26 '16 at 16:22

score 1 · Accepted Answer · answered Apr 26 '16 at 22:06

This can be done with formulas, though the formulas aren't the prettiest to look at. Using your example data, let's say you have a setup like so: Your original table is in columns A:D, your lookup column "Diag" is in column F, and your results are in columns H:K

In cell H2 and copied down is this array formula. Note that array formulas must be confirmed with CtrlShiftEnter and not just Enter. You'll know it's been done correctly because in the formula bar you will see it surrounded by curly braces {}. Do NOT attempt to put in the curly braces manually.

=IFERROR(INDEX(A:A,SMALL(IF(ISNUMBER(FIND($F$2:$F$6,$B$2:$D$4)),ROW($B$2:$D$4)),ROW(H1))),"")

In cell I2 and copied over and down is this regular formula (no array entry necessary):

=IF(H2="","",IF(VLOOKUP($H2,$A:$D,MATCH(I$1,$A$1:$D$1,0),FALSE)="","",--TRIM(LEFT(SUBSTITUTE(VLOOKUP($H2,$A:$D,MATCH(I$1,$A$1:$D$1,0),FALSE),"-",REPT(" ",999)),999))))

That's a great alternative. The way I eventually did it was unpivoting Diag1,2,3 into a single column and then trimming out the starting numbers from the text. Then I created a new column and put a match/unmatch flag using a COUNTIF inside an IF function to match the lookup and unpivoted lists. — AS91, Apr 28 '16 at 18:53

Extract people using numbers mixed with text over different columns

1 Answers1