CODEWITHSUNDEEP
excelexcel-formula
David Leal
David Leal

Reputation: 6759

Select a subset of excel Table given a value of given column

I have a Table in excel like the following one:

col1 col2
A    1
B    0
A    2
B    2
B    3

I was not able to find a way to select a subset of the table like this one using an excel function.

col11 col22
B    0
B    2
B    3

based on the value "B" of column col1 or just be able to select the col22' from the given subset.

I would like to have a solution that does not require to VBA nor array formula. I found on Internet the function FILTER, but it is not available yet and Structured References does not have such functionality.

I would like to use for example the result col22 as a column at another place of my spreadsheet. Other languages such as R, provide a function subset that does this in a pretty simple way. In excel it is really easy to filter using the Excel interface (filter button), but I am not aware of a function that does something similar programatically.

Upvotes: 1

Views: 13781

Answers (3)

Davide Clementino
Davide Clementino

Reputation: 1

Goal: extract a subset of the current sheet only when values in the first column match a value in the first column of another 'Sheet1':

=VLOOKUP(A2,Sheet1!$A:$B,2,FALSE)

Add the formula above in the first cell of the first empty column, then sort Z-A this column and remove all "#N/A" rows, then re-order again A-Z the first column and remove this temporary column.

Upvotes: 0

Ehsan Tabatabaei
Ehsan Tabatabaei

Reputation: 153

I had the same problem and the only thing I found on the internet was the FILTER function too.

I wanted to vlookup a subset of a table based on another column. My solution was to make another column that is the concatenation of the two. Then I used vlookup on this column.

I don't know what are you going to do with the subset table that you want to have. But you can do it in a different way as I did. May it helps.

Upvotes: 1

Bad_Neighbor
Bad_Neighbor

Reputation: 362

Assuming col1 is A and col 2 is B and your result set's col1 and col2 are E and F respectively, try this formula in column C: =IF(A2=$E$2,MAX($C$1:C1)+1,0)

Now, in cell E2 (i left the first row for a header), i physically typed "B" to make this the input source and allowing for dynamic reporting. for as many cells below that as you wish, the following formula will only show the total number of "B"s that are in your input source: =IF(ROW(A2)>MAX(C:C),"",E2) Notice i did not fix the cells with $ as your following row depends on the former.

Cell F2 (the top of your col2 field for the result set) include: =INDEX(B:B,MATCH(G2,C:C,0)) and in G2 put: =IF(E2="","",G1+1) and copy both down as far as you have formulas in column E (or result set col1).

The reason for the IF statements is for formatting rather than displaying errors on failed lookups.

Upvotes: 0

Related Questions