numbering rows, but skipping blanks


G

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.
 
Ad

Advertisements

R

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.
 
D

Daniel.C

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

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
 
G

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.
 
G

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 -
 
Ad

Advertisements

R

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?
 
G

Guest3731

RagDyer said:
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.
 
R

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

RagDyer said:
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.
 
G

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!
 
Joined
Feb 24, 2016
Messages
1
Reaction score
0
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
 
Ad

Advertisements

Joined
Feb 3, 2017
Messages
1
Reaction score
0
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
 
Joined
Jan 10, 2018
Messages
2
Reaction score
0
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" <notconfusedaboutthattoday@gmail.com> 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.
 
Ad

Advertisements

Joined
Oct 3, 2019
Messages
1
Reaction score
0
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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top