blank cells

[ahhhmed]

Hi,

Column A2:A10 have names

column B2:B10 have numbers but some cells are blanks


in A22:A32 I want to list down all the names from A2:A10 that have blank cells in front of them in B2:B10


What formula can I use here? I want the results with no blank cells between them.

Any help is highly appreciated

 

[Luke M]

We can use the same principle explained here:

http://chandoo.org/wp/2011/11/18/formula-forensics-003/


Array formula will be:

=IF(COUNT(B$2:B$10)<ROWS(A$22:A22),"",INDEX(A:A,SMALL(IF(ISNUMBER(B$2:B$10),ROW(B$2:B$10)),ROW(A1))))

 

[ahhhmed]

Thanks Luke M, but the formula does not give the required results.

 

[fred]

Try


=IF(COUNTIF(B$2:B$10,0)>ROWS(A$22:A22),"",INDEX(A:A,SMALL(IF(ISNUMBER(B$2:B$10),ROW(B$2:B$10)),ROW(A1))))


crtl+shift+enter


you can also extend the formula where iferror(above formula,"") to remove any #NUM! between lines 22 to 29. you don't have that many data to go to line 32.

 

[Luke M]

Hmm, I thought I had responded yesterday...looks like I was mistaken.

Ahhmed, I misread your first post. I thought you wanted the names with numbers, not blanks. Switching functions:

=IF(COUNTIF(B$2:B$10,"")<ROWS(A$22:A22),"",INDEX(A:A,SMALL(IF(ISBLANK(B$2:B$10),ROW(B$2:B$10)),ROW(A1))))