numbering rows, but skipping blanks

[Guest3731]

I'm using Column A of a spreadsheet to number some items in Column B.
I've been using Excel's automatic series-filling function, where you
drag down from the lower right corner of a cell. However, since some
of the rows in Column B are empty, and I don't want them numbered, I
have to stop numbering and manually "jump" over the blanks in Column
A. Is there a way to automatically number down in Column A, while
skipping over the blanks and continuing where I left off? I.e.

1
2


3
4

5

? Thanks much.

 

[RagDyeR]

Try this in A1:

=IF(B1<>"",COUNTA($B$1:B1),"")

And copy down as needed.
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


I'm using Column A of a spreadsheet to number some items in Column B.
I've been using Excel's automatic series-filling function, where you
drag down from the lower right corner of a cell. However, since some
of the rows in Column B are empty, and I don't want them numbered, I
have to stop numbering and manually "jump" over the blanks in Column
A. Is there a way to automatically number down in Column A, while
skipping over the blanks and continuing where I left off? I.e.

1
2


3
4

5

? Thanks much.

 

[Daniel.C]

In cell A1, paste :
=IF(B1="","",COUNTA($B$1:B1))
then, drag down.
Regards.
Daniel

 

[Bernard Liengme]

Enter your starting value in A1
In A2 use =IF(ISBLANK(B2),"",MAX($A$1:A1)+1)
Adjust cell references if you are starting in other than A1
best wishes

 

[Guest3731]

Enter your starting value in A1
In A2 use =IF(ISBLANK(B2),"",MAX($A$1:A1)+1)
Adjust cell references if you are starting in other than A1
best wishes

Thanks for all of these helpful replies - much obliged.

 

[Guest3731]

Thanks for all of these helpful replies - much obliged.

Hey, not to be a pest, but can this be modified so as to skip over
(not number) A1 if B1 is either blank (empty), or contains a string
containing " no " as a substring? Thanks, if you have a minute for
this -

 

[RagDyer]

What type of data is in Column B ... text, numbers, or both?

Would you want "Nobel Prize" and "hypnotize" to be numbered or skipped?

 

[Guest3731]

What type of data is in Column B ... text, numbers, or both?

Would you want "Nobel Prize" and "hypnotize" to be numbered or skipped?

It is all text - and I guess what I would want to have skipped
(besides the blank B rows) would be "no ", case-insensitive - that is,
an "n", an "o", and some whitespace. Does that make sense?

Many thanks.

 

[RagDyeR]

You'll have to *manually* number A1 if B1 meets the criteria.

Then, enter this formula in A2, and copy down as needed:

=IF(OR(B2="",ISNUMBER(SEARCH("no ",B2))),"",MAX($A$1:A1)+1)

Since you said:
<<<" "no ", case-insensitive - that is, an "n", an "o", and some
whitespace.">>>

An individual "No", or "no" *will* be numbered, since there is no space
after the "o".


--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

What type of data is in Column B ... text, numbers, or both?

Would you want "Nobel Prize" and "hypnotize" to be numbered or skipped?

It is all text - and I guess what I would want to have skipped
(besides the blank B rows) would be "no ", case-insensitive - that is,
an "n", an "o", and some whitespace. Does that make sense?

Many thanks.

 

[Guest3731]

You'll have to *manually* number A1 if B1 meets the criteria.

Then, enter this formula in A2, and copy down as needed:

=IF(OR(B2="",ISNUMBER(SEARCH("no ",B2))),"",MAX($A$1:A1)+1)

Since you said:
<<<" "no ", case-insensitive - that is, an "n", an "o", and some
whitespace.">>>

An individual "No", or "no" *will* be numbered, since there is no space
after the "o".

Thanks!