Listing non-blank cells in Excel based on adjacent column(s)

microsoft-excel worksheet-function vba microsoft-excel-2013

10,871

Solution 1

enter image description here

This will work for your example an can easily be adapted if you have headers, more columns, or more rows.

=IFERROR(INDEX($A$1:$A$4,SMALL(IF(ISBLANK($B$1:$B$4),"",ROW($B$1:$B$4)-ROW($C$1)+1),ROW(C1)-ROW($C$1)+1)),"")

Enter the formula in C1 and hold Ctrl+Shift then press Enter. Expand the formula to C4 to get the full results for your example.

Headers or columns may now be inserted in the example and this will still work. To handle more rows, change the range $A$1:$A$4 and $B$1:$B$4 accordingly

See also Microsoft Support article Finding the nth Value That Meets a Condition.

Solution 2

Why not filter the list in place? Filter the list based on column B for all rows where B is not blank e.g.

enter image description here

Then if you need it in column C, just copy and paste it. Remember that once you filter some cells are hidden so copy/paste might not look correct until you clear the filter.

10,871

warakawa

Updated on September 18, 2022

Comments

warakawa over 1 year
Given columns A and B, I want to list the A-values that have a non-blank B-cell in their row, in column C:
```
A     B     C
One         Two
Two   x     Four
Three
Four  x
...
```
The best I came up with so far is
```
{=INDEX(A1:A4;MATCH(TRUE;B1:B4<>"";0))}
```
which gives me "Two" in C1, but how do I continue?

Note: This a simplified version of my problem: in reality, there are multiple columns like B, so filtering is not an option. Moreover, B and C are not in the same sheet, and I want the C-sheet to update automatically whenever I edit the B-sheet, so copy&paste is not practical either.
- Raystafarian about 10 years
  
  This page at get-digital-help seems like it may help you when you begin to expand across sheets.
warakawa about 10 years

Actually, I stated a simplified version of my problem: in reality, there are multiple columns like B, so filtering is not an option. Moreover, B and C are not in the same sheet, and I want the C-sheet to update automatically whenever I edit the B-sheet, so copy&paste is not practical either.
Raystafarian about 10 years

@warakawa then this suggestion is definitely not an option for you, but I'll leave it in case someone else happens upon it.
Raystafarian about 10 years

I got this to work by changing $A$4 and $B$4 to $6 or $5 and removing column headers.
Calvin about 10 years

Thanks. I updated the formula to work with column headers.