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!

 

[Ramya SK]

My test case id in excel is something like this "TPTP(5.9.9)4.1_140 " .[This Is derived from lookup formula =(LOOKUP(2,1/((A4:A153<>"")*(A4:A153<>"-")),A4:A153)) . ] How can I increment TPTP(5.9.9)4.1_140 to TPTP(5.9.9)4.1_141? please help

 

[Yolandi]

Good morning all,

I have this problem, I would also like to number the cells, but instead of just numbering it I have to put three letters, numbers and points in it. eg. num01.17.01

The numbering is fine, but how do I get the whole "reference" number in the cell

Thank you

 

[JoeyOreally]

This worked perfectly, I have a new wrinkle though, I want to have the number system in column A to also have a separate color with separate number system if column B is a different font color

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!
=====================================================


"Guest3731" <> wrote in message
news:...
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.

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!
=====================================================


"Guest3731" <[email protected]> wrote in message
news:8b2d95d4-6c8b-444e-9b52-e3bd901eba5b@e39g2000hsf.googlegroups.com...
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.

 

[Rahul97]

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!
=====================================================



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.

Hey,
I have a case where i want to increase number of column A as TC_001,TC_002,TC_003.... when Column B contains String as "Step 1" and ignore rest of values in column B
How can i build the formula for this?