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

N

Negi1984

Board Regular
Joined
May 6, 2011
Messages
199
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
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Sort by date Sort by votes
Try something like this:
Code: 
=LOOKUP(2,1/(R2:CW2<>0),[COLOR=#ff0000]$S$1:$CX$1[/COLOR])
Is that something you can work with?
 
Upvote 0
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.

[TABLE="width: 938"]
<tbody>[TR]
[TD]Jan-14[/TD]
[TD]Feb-14[/TD]
[TD]Mar-14[/TD]
[TD]Apr-14[/TD]
[TD]May-14[/TD]
[TD]Jun-14[/TD]
[TD]Jul-14[/TD]
[TD]Aug-14[/TD]
[TD]Sep-14[/TD]
[TD]Oct-14[/TD]
[TD]Output[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0.25[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Feb-14[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]#N/A[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]Jul-14[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]#N/A[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]Sep-14[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]May-14[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]#N/A[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]Jun-14[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]#N/A[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col><col><col><col><col><col></colgroup>[/TABLE]
Ron Coderre said:
Try something like this:
Code: 
=LOOKUP(2,1/(R2:CW2<>0),[COLOR=#ff0000]$S$1:$CX$1[/COLOR])
Is that something you can work with?
Click to expand...
 
Upvote 0
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...
 
Upvote 0
Hi Aladin,

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

Jan-14Feb-14Mar-14Apr-14May-14Jun-14Jul-14Aug-14Sep-14Oct-14Output needed
100000.25Feb-14
1111111111#N/A
11110000Jul-14
1111111111#N/A
1111101000Aug-14
1111100000Jun-14
0000000000#N/A
1111110000Jul-14
1111111111#N/A

<tbody>
</tbody>

Aladin Akyurek said:
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...
Click to expand...
 
Upvote 0
With your new sample data in A1:J10
Try this regular formula, copied down...
Code: 
K2: =IFERROR(LOOKUP(2,1/(A2:J2=1),$B$1)/1,"n/a")
Is that something you can work with?
 
Upvote 0
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.
[TABLE="width: 768"]


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

[TD="class: xl63, width: 64, bgcolor: #4472C4"]Jan-14[/TD]

[TD="class: xl63, width: 64, bgcolor: #4472C4"]Feb-14[/TD]

[TD="class: xl63, width: 64, bgcolor: #4472C4"]Mar-14[/TD]

[TD="class: xl63, width: 64, bgcolor: #4472C4"]Apr-14[/TD]

[TD="class: xl63, width: 64, bgcolor: #4472C4"]May-14[/TD]

[TD="class: xl63, width: 64, bgcolor: #4472C4"]Jun-14[/TD]

[TD="class: xl63, width: 64, bgcolor: #4472C4"]Jul-14[/TD]

[TD="class: xl63, width: 64, bgcolor: #4472C4"]Aug-14[/TD]

[TD="class: xl63, width: 64, bgcolor: #4472C4"]Sep-14[/TD]

[TD="class: xl63, width: 64, bgcolor: #4472C4"]Oct-14[/TD]

[TD="class: xl64, width: 64, bgcolor: red"]Output[/TD]

[TD="class: xl65, width: 64, bgcolor: red"] [/TD]

[/TR]

[TR]

[TD="class: xl66, bgcolor: #B4C6E7"]1[/TD]

[TD="class: xl66, bgcolor: #B4C6E7"]0[/TD]

[TD="class: xl66, bgcolor: #B4C6E7"]0[/TD]

[TD="class: xl66, bgcolor: #B4C6E7"]0[/TD]

[TD="class: xl66, bgcolor: #B4C6E7"]0[/TD]

[TD="class: xl66, bgcolor: #B4C6E7"]0.25[/TD]

[TD="class: xl66, bgcolor: #B4C6E7"]0[/TD]

[TD="class: xl66, bgcolor: #B4C6E7"] [/TD]

[TD="class: xl66, bgcolor: #B4C6E7"] [/TD]

[TD="class: xl66, bgcolor: #B4C6E7"] [/TD]

[TD="class: xl67, bgcolor: #B4C6E7"]Feb-14[/TD]

[TD="class: xl68, bgcolor: #B4C6E7"] [/TD]

[/TR]

[TR]

[TD="class: xl69, bgcolor: #D9E1F2"]1[/TD]

[TD="class: xl69, bgcolor: #D9E1F2"]1[/TD]

[TD="class: xl69, bgcolor: #D9E1F2"]1[/TD]

[TD="class: xl69, bgcolor: #D9E1F2"]1[/TD]

[TD="class: xl69, bgcolor: #D9E1F2"]0[/TD]

[TD="class: xl69, bgcolor: #D9E1F2"]1[/TD]

[TD="class: xl69, bgcolor: #D9E1F2"]0[/TD]

[TD="class: xl69, bgcolor: #D9E1F2"]1[/TD]

[TD="class: xl69, bgcolor: #D9E1F2"] [/TD]

[TD="class: xl69, bgcolor: #D9E1F2"] [/TD]

[TD="class: xl67, bgcolor: #B4C6E7"]Sep-14[/TD]

[TD="class: xl70, bgcolor: #D9E1F2"] [/TD]

[/TR]


Regards,
Rajender</tbody>[/TABLE]
 
Upvote 0

Book1
ABCDEFGHIJKLM
1Jan-14Feb-14Mar-14Apr-14May-14Jun-14Jul-14Aug-14Sep-14Oct-14Outputintermediate resultResult
2100000.25Feb-14100001####Feb-14
31111111111#N/A1111111111#N/A
411110000Jul-1411##110000Jul-14
51111111111#N/A1111111111#N/A
61111101000Aug-141111101000Aug-14
71111100000Jun-141111100000Jun-14
80000000000#N/A0000000000#N/A
91111110000Jul-141111110000Jul-14
101111111111#N/A1111111111#N/A
11100000.250Jul-141000010###Jul-14
1211110101Jul-1411110101##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.
 
Upvote 0
Here's a regular formula I *think* you can use....
Code: 
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?
 
Upvote 0
Hi Ron,

Still the output is not ok for few cases like below :-
Please assist.
[TABLE="width: 835"]
<tbody>[TR]
[TD]Jan-14[/TD]
[TD]Feb-14[/TD]
[TD]Mar-14[/TD]
[TD]Apr-14[/TD]
[TD]May-14[/TD]
[TD]Jun-14[/TD]
[TD]Jul-14[/TD]
[TD]Aug-14[/TD]
[TD]Sep-14[/TD]
[TD]Oct-14[/TD]
[TD]Output[/TD]
[TD]RON Formula[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0.5[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Jul-14[/TD]
[TD]01-Feb[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Jul-14[/TD]
[TD]01-Sep[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col><col><col span="2"><col><col><col></colgroup>[/TABLE]
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Question Question
Value of 1 column before the last value in a contiguous range
Replies
3
Views
222
Kaper
Kaper
J
  • Solved
Value in column adjacent to the value found by LOOKUP
Replies
5
Views
140
Fluff
Fluff
N
  • Question Question
Formula help to search a table for a date greater than today that has a non-blank cell and return the date in a separate cell.
Replies
2
Views
549
Alex Blakenburg
A
T
  • Solved
Index and Match (?) for the last non-zero value in a row
Replies
3
Views
732
Fluff
Fluff
M
  • Solved
run formula based on cell value to the last row
Replies
5
Views
405
MMM_84
M

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top