Formula need to pick the adjacent header of last value greater than Zero

[Negi1984]

Hi All,

I have some data from column R to column CZ. Row 1 contains the Headers.
Now I need to look for last non-zero value and pick the header next that cell.

I am using the below formula right now but its not giving me the next header.
its giving me the header of cell where last non zero value exist.

=LOOKUP(2,1/(R2:CW2<>0),$R$1:$CW$1)
Please suggest, what changes I can do in the above formula. or any other formula to solve the issue.

Regards,
Rajender

 

[Ron Coderre]

Try something like this:

=LOOKUP(2,1/(R2:CW2<>0),[COLOR=#ff0000]$S$1:$CX$1[/COLOR])

Is that something you can work with?

 

[Negi1984]

Hi Ron,

the given formula not 100% ok in some cases.
I am sharing the dummy data with desired output in below table.

Assuming out data start from A1 and in column K I need the output as mentioned below :-
Basically the logic for output is where every last cell from A to J is zero and its previous cell value is more than 0, than I need to pickup that cell Header.
Thanks in advance for your valuable support. if any macro based custom function is available than its also ok for me.

Jan-14	Feb-14	Mar-14	Apr-14	May-14	Jun-14	Jul-14	Aug-14	Sep-14	Oct-14	Output
1	0	0	0	0	0.25					Feb-14
1	1	1	1	1	1	1	1	1	1	#N/A
1	1			1	1	0	0	0	0	Jul-14
1	1	1	1	1	1	1	1	1	1	#N/A
1	1	1	1	1	0	1	0	0	0	Sep-14
1	1	1	1	1	0	0	0	0	0	May-14
0	0	0	0	0	0	0	0	0	0	#N/A
1	1	1	1	1	1	0	0	0	0	Jun-14
1	1	1	1	1	1	1	1	1	1	#N/A

<tbody>
</tbody><colgroup><col><col><col><col><col><col><col><col><col><col><col></colgroup>

Try something like this:

=LOOKUP(2,1/(R2:CW2<>0),[COLOR=#ff0000]$S$1:$CX$1[/COLOR])

Is that something you can work with?

 

[Aladin Akyurek]

Is what you show in column L the desired output? If it's, what is logic as it does not seem to have a consistent pattern...

 

[Negi1984]

Hi Aladin,

Thanks you for noted this. My mistakenly. Here is the updated table.

Jan-14	Feb-14	Mar-14	Apr-14	May-14	Jun-14	Jul-14	Aug-14	Sep-14	Oct-14	Output needed
1	0	0	0	0	0.25					Feb-14
1	1	1	1	1	1	1	1	1	1	#N/A
1	1			1	1	0	0	0	0	Jul-14
1	1	1	1	1	1	1	1	1	1	#N/A
1	1	1	1	1	0	1	0	0	0	Aug-14
1	1	1	1	1	0	0	0	0	0	Jun-14
0	0	0	0	0	0	0	0	0	0	#N/A
1	1	1	1	1	1	0	0	0	0	Jul-14
1	1	1	1	1	1	1	1	1	1	#N/A

<tbody>
</tbody>

Is what you show in column L the desired output? If it's, what is logic as it does not seem to have a consistent pattern...

 

[Ron Coderre]

With your new sample data in A1:J10
Try this regular formula, copied down...

K2: =IFERROR(LOOKUP(2,1/(A2:J2=1),$B$1)/1,"n/a")

Is that something you can work with?

 

[Negi1984]

Hi Ron,

Thanks for the formula. I tried to change few number and check the result and found in below cases output not correct :-

As per me output for 1st & 2nd row should be Jul-14.

Jan-14	Feb-14	Mar-14	Apr-14	May-14	Jun-14	Jul-14	Aug-14	Sep-14	Oct-14	Output
1	0	0	0	0	0.25	0				Feb-14
1	1	1	1	0	1	0	1			Sep-14

<colgroup><col width="64" style="width: 48pt;" span="12">
<tbody>










































Regards,
Rajender </tbody>

 

[Aladin Akyurek]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Jan-14	Feb-14	Mar-14	Apr-14	May-14	Jun-14	Jul-14	Aug-14	Sep-14	Oct-14	Output	intermediate result	Result
2	1	0	0	0	0	0.25					Feb-14	100001####	Feb-14
3	1	1	1	1	1	1	1	1	1	1	#N/A	1111111111	#N/A
4	1	1			1	1	0	0	0	0	Jul-14	11##110000	Jul-14
5	1	1	1	1	1	1	1	1	1	1	#N/A	1111111111	#N/A
6	1	1	1	1	1	0	1	0	0	0	Aug-14	1111101000	Aug-14
7	1	1	1	1	1	0	0	0	0	0	Jun-14	1111100000	Jun-14
8	0	0	0	0	0	0	0	0	0	0	#N/A	0000000000	#N/A
9	1	1	1	1	1	1	0	0	0	0	Jul-14	1111110000	Jul-14
10	1	1	1	1	1	1	1	1	1	1	#N/A	1111111111	#N/A
11	1	0	0	0	0	0.25	0				Jul-14	1000010###	Jul-14
12	1	1	1	1	0	1	0	1			Jul-14	11110101##	Jul-14
Sheet1

Install first the following udf Sreverse as module by means of Alt+F11...

Public Function Sreverse(Rng As Range)
Sreverse = StrReverse(Rng.Text)
End Function

In L2 control+shift+enter, not just enter, and copy down:

=TEXTJOIN("",TRUE,IF(ISNUMBER(A2:J2),IF(ABS(A2:J2)>0,1,0),"#"))

In M2 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($A$1:$J$1,COLUMNS($A2:$J2)-LOOKUP(9.99999999999999E+307,FIND("01",Sreverse(L2)))+1),#N/A)

Note. If Sreverse can be modified to admit an array refernce, we can eliminate the intermediate result.

 

[Ron Coderre]

Here's a regular formula I *think* you can use....

K2: =TEXT(INDEX($B$1:$K$1,LEN(SUMPRODUCT((A2:J2=1)*10^{0,1,2,3,4,5,6,7,8,9}))
/MIN(MOD(COUNTIF(A2:J2,">0"),10),1)),"dd-mmm")

Does that help?

 

[Negi1984]

Hi Ron,

Still the output is not ok for few cases like below :-
Please assist.

Jan-14	Feb-14	Mar-14	Apr-14	May-14	Jun-14	Jul-14	Aug-14	Sep-14	Oct-14	Output	RON Formula
1	0	0	0	0	0.5	0				Jul-14	01-Feb
1	1	1	1	0	1	0	1			Jul-14	01-Sep

<tbody>
</tbody><colgroup><col><col><col><col><col><col><col><col span="2"><col><col><col></colgroup>