Extract data from two columns of one worksheet to another depending on presence of character in third column of a worksheet

Question

I am looking for some help with a formula. On one worksheet, I have data set up similar to:

   A        B            C        D        E
1                      Test1    Test2    Test3
2 ID     Name      
3 T01    Confucius       X       
4 T02    Newton          X
5 T03    Enstein                  X
6 T04    Plato                    X        X
7 T05    Da Vinci                          X

It is similar to comparison of different products against set of features.

What I would like is, on a separate worksheet, when a test name (i.e. row1) is selected, information from column A and column B is returned ONLY if there is a check mark in the column for the respective test selected. In other words, if on a separate worksheet, I select Test3, then T04 Plato and T05 Da Vinci are returned. I would like this dynamic as my data spreadsheet will continue to grow with new IDs/Names and test(x).

I am open to formulaic or filter-type solutions.

pnuts · Answer 1 · 2013-10-21T19:25:27.137

1

I suggest a slight rearrangement of your labels and using a PivotTable:

SO19502213 first example

This though would require refreshing of the PT if further values are added.

Edit re supplementary

I think what you want is a more amenable dataset. Replace Xs with the Test# in a single column (so two rows for Plato), then pivot:

SO19502213 second example

edited Oct 21 '13 at 19:25

answered Oct 21 '13 at 19:01

pnuts

58,317
11
87
139

Thanks for the suggestion. I am wondering if there is something more aesthetically simple for the filtering on the test. My real worksheet actually has 46 tests and growing so I would prefer not to have to show all the test in the separate worksheet. Is there a type of cascading filter for the PT by which I can filter for the test name first, then filter for "x"? – user2904366 Oct 21 '13 at 19:11

James Jenkins · Answer 2 · 2013-10-21T19:23:19.830

0

You can user vlookup but you would need to put the values you are looking for (i.e. test3) in the early columns (i.e. "A") this can be done by using vlookup to a second sheet to put them in order.

A lot of vlookups get resource heavy, so a better solution is probably to try and do something with Access, if you own it.

edited Oct 21 '13 at 19:23

answered Oct 21 '13 at 19:16

James Jenkins

1,954
1
24
43

I do have Access but am even less familiar with it than Excel. I'll look into it. Thanks.1 – user2904366 Oct 21 '13 at 19:54

Extract data from two columns of one worksheet to another depending on presence of character in third column of a worksheet

2 Answers2