CODEWITHSUNDEEP
excelexcel-formula
user4038080
user4038080

Reputation:

excel: list all values of a column of a range based on a condition

I have a range in a Excel Sheet table that contains 2 columns like A and B.
I want to list in another column all values from column A if the corresponding value in column B is not null. I know a way to do it, but it produces a lot of "empty raws" like in column C

=if(B<>"";A;"")

I would like to do it in a compact way, with no "empty raws" like in column D

enter image description here

Upvotes: 2

Views: 2889

Answers (1)

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96773

Here is an example for data down to row #19. In C1 enter the array formula:

=IFERROR(INDEX($A$1:$A$19,SMALL(IF($B$1:$B$19<>"",ROW($B$1:$B$19)),ROW(1:1))),"")

and copy downwards. (You may need to use semi-colons in place of commas)

Array formulas must be entered with Ctrl + Shift + Enter rather than just the Enter key. If this is done correctly, the formula will appear with curly braces around it in the Formula Bar.

enter image description here

Upvotes: 2

Related Questions