Increment x in Col A ONLY IF adjacent Col B cell NOT Blank

[powerjak]

Should be simple, but I'm in a brain-lock. I want to increment by 1, a value in Column A successive row cells, but only IF the adjacent cell in Column B is NOT blank. IF Column B cell ISBLANK(), then Column A cell is to also be blank.

Example:

A B
1 text
2 text
3 text
4 text

5 text
6 text

7 text

I've tried a number of nested IFs using different Fx evaluations of Col B, but so far, nothing I've tried gets me a result that I can replicate down all row cells of Col A to get the results of the example above without any error results.

I'm sure it'll turn out to be a "head slapper" when one of you bright people provide the assist to untwist my brain ... and I'll certainly appreciate the help!

 

[Aladin Akyurek]

Re: Increment x in Col A ONLY IF adjacent Col B cell NOT Bla

Should be simple, but I'm in a brain-lock. I want to increment by 1, a value in Column A successive row cells, but only IF the adjacent cell in Column B is NOT blank. IF Column B cell ISBLANK(), then Column A cell is to also be blank.

Example:

A B
1 text
2 text
3 text
4 text

5 text
6 text

7 text

I've tried a number of nested IFs using different Fx evaluations of Col B, but so far, nothing I've tried gets me a result that I can replicate down all row cells of Col A to get the results of the example above without any error results.

I'm sure it'll turn out to be a "head slapper" when one of you bright people provide the assist to untwist my brain ... and I'll certainly appreciate the help!

In A1 enter: 1

if B1 is not blank.

In A2 enter & copy down:

=IF(B2="","",LOOKUP(9.99999999999999E+307,$A$1:A1)+1)

or

=IF(ISBLANK(B2),"",LOOKUP(9.99999999999999E+307,$A$1:A1)+1)

 

[Travis]

another solution:
In A1 enter 1
In A2 enter =IF(B2<>"",MAX($A$1:A1)+1,"") and copy down

Book1
	A	B	C	D
1	1	text
2	2	text
3	3	text
4	4	text
5
6	5	text
7	6	text
8
9	7	text
Sheet1

 

[powerjak]

Works GREAT - New Wrinkle Just Came Up Though

I've just been informed that the formula should NOT increment based on TWO evaluations. That is, if a cell in Col B is blank, do not increment [as currently provided by your =IF(B2<>"",MAX($A$1:A1)+1,"") formula], however, if Col B is NOT blank, then ALSO DO NOT increment the value in Col A if the word "deceased" is found anywhere in a text string located in Col D.

 

[Travis]

Using my first example, in A2 enter
=IF(AND(B2<>"",ISERROR(SEARCH("decreased",D2))=TRUE),MAX($A$1:A1)+1,"")
and copy down

Edit:
oops, I thought you said "decreased" if it should be "deceased" you will need to change that in my formula

 

[Aladin Akyurek]

Re: Works GREAT - New Wrinkle Just Came Up Though

I've just been informed that the formula should NOT increment based on TWO evaluations. That is, if a cell in Col B is blank, do not increment [as currently provided by your =IF(B2<>"",MAX($A$1:A1)+1,"") formula], however, if Col B is NOT blank, then ALSO DO NOT increment the value in Col A if the word "deceased" is found anywhere in a text string located in Col D.

Book5
	A	B	C	D
1	0
2	ID	X		Y
3				Vort is deceased
4	1	wqa
5	2	tra
6		yta		The deceased...
7	3	otua
8	4	ewa
9	5	2
10	6	6/2/2004
11
Sheet1

A1 must house a 0.

A3, copied down:

=IF((B3="")+ISNUMBER(SEARCH("deceased",D3)),"",LOOKUP(9.99999999999999E+307,$A$1:A2)+1)

 

[powerjak]

WOW - I am impressed! Thanks!!!!

Travis, Aladin, you guys are simply great.

Both of your approaches work exactly as needed and I've already completed the needed patch. And as I suspected, there was a bit of head-slapping involved at this end, as my initial approach was very close to Travis', although I got myself brain-locked on a fine point that his help made very clear.

Aladin, your approach I would never have gotten to myself ... and I wouldn't have considered using a lockup with the largest number that Excel can recognize (9.99999999999999E+307) as part of the approach. Nevertheless, it is clever and works perfectly as well.

Bottomline -- It's a crappy day when you don't learn something new ... and clearly, I can learn an amazing amount of stuff from people like yourselves. Thanks again. You both really made my day!

 

[Aladin Akyurek]

Re: WOW - I am impressed! Thanks!!!!

...I wouldn't have considered using a lockup with the largest number that Excel can recognize (9.99999999999999E+307) as part of the approach. Nevertheless, it is clever and works perfectly as well...

Might look foreign, even clever. But the real reason for it is speed.

 

[KenWright]

Hi Aladin, just curious, but given that i was headed the way of Travis' formula, is there a speed advantage in using ISNUMBER(SEARCH... over perhaps COUNTIF(, eg

=IF((B3="")+COUNTIF(D3,"*deceased*"),"",LOOKUP(9.99999999999999E+307,$A$1:A2)+1)

 

[Aladin Akyurek]

Hi Aladin, just curious, but given that i was headed the way of Travis' formula, is there a speed advantage in using ISNUMBER(SEARCH... over perhaps COUNTIF(, eg

=IF((B3="")+COUNTIF(D3,"*deceased*"),"",LOOKUP(9.99999999999999E+307,$A$1:A2)+1)

It seems IsNumber/Search has a slight edge over CountIf... Tested with a range of 30 cells using FastExcel, 5 runs altogether. Maybe it isn't that surprising since Search is a tuned up text function. Needs a more serious test though.