CODEWITHSUNDEEP
excel
Roloka
Roloka

Reputation: 191

How to get the position of the nth nonblank cell of a column in excel?

I would like to get the position of the nth (e.g. the third) nonblank cell in a range (e.g. A1:A10) in excel. Cells can contain strings, too, not only numbers. Could anybody help me in? Thank you!

Upvotes: 0

Views: 2829

Answers (1)

Gary's Student
Gary's Student

Reputation: 96773

With data in column A, in B1 put the array formula:

= MATCH(FALSE, ISBLANK(A:A), 0)

In B2 put the array formula:

= MATCH(FALSE, ISBLANK(INDIRECT("A" & B1+1 & ":A9999")), 0)+B1

and copy down.

B1 will point to the first non-blank, B2 will point to the second non-blank, etc.

enter image description here

Array formulas must be entered with Ctrl + Shift + Enter rather than just the Enter key.

Upvotes: 1

Related Questions