List row numbers where cell values exist

**maxald** · 05-28-2010, 04:06 AM

Hi! I have a column where there are some empty cells. What I am trying to achive is to get a list with row numbers to all cells that are NOT empty (without using VBA). Is it possible?

This is what I have:

Row | Column A
1 | X
2 | [empty]
3 | X
4 | X
5 | [empty]
6 | X

This is what I need:

Row | Column A
1 | 1
2 | 3
3 | 4
4 | 6

**Marcol** · 05-28-2010, 05:08 AM

Try this

in B1

Please Login or Register  to view this content.

Fill down

Filter Column B for Non-blanks

Copy and paste to desired location.

Hope this helps.

If you need any further information, please feel free to ask further

However

If this takes care of your needs, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED. It helps everybody!
Also
If you are satisfied by any members response to your problem please consider using the scales icon top right of thier post to show your appreciation.

**maxald** · 06-02-2010, 09:47 AM

Thanks for reply! Problem is I need a list with no empty rows in between... thats the tricky part.

Like a filtered list but with a formula so there will be no hidden rows.

What I need (if row 2 and 5 is empty):

Row | Column A
1 | 1
2 | 3
3 | 4
4 | 6

Not:

Row | Column A
1 | 1
2 |
3 | 3
4 | 4
5 |

**martindwilson** · 06-02-2010, 09:59 AM

do you want the row numbers or just the contents without blanks? if its the latter see http://www.cpearson.com/excel/noblanks.htm

**maxald** · 06-03-2010, 08:18 AM

Thanks for reply. Yes, I'd like to do exactly what that macro did, but Im not allowed to use macros so I am trying to do this with standard formulas/functions. I'd also want to avoid filtering. Row number would be just enough, from there I'll do it on my own.

Suppose OFFSET is what I need to get things running, but I don't know how...

**martindwilson** · 06-03-2010, 01:07 PM

use the first example ,the array formula with blanks/no blanks ranges you can either name the range as suggested or put in the actual range instead--no macro required

**maxald** · 06-04-2010, 02:24 AM

That formula gives me the correct rownumber, yes, but then again I have a list with some empty cells. Problem is I need a formula that not only ignores the empty cells but go down the list and find the next cell thats not empty. Think of it like a filtering (its super easy that way) but with a fomula only. Maybe it's not possible, filtering sure is the way to go but I dont want that extra step.

Please see my attachment for an example.