Locating first and last non-zero value cells based on value of cell value in COL A

Darren_workforce

Board Regular
Joined
Oct 13, 2022
Messages
102
Office Version
  1. 365
Platform
  1. Windows
I have the following formulas that pull the first and last non-zero cells and populate the header (time interval) in the respective column on the MON tab.
=INDEX(MON!$B$5:$AQ$5,MATCH(TRUE,INDEX(MON!B6:AQ6<>0,),0)) first non-zero in the row
=INDEX(MON!$B$5:$AQ$5,MATCH(TRUE,INDEX(MON!B6:AQ6<>0,),1)) last non-zero in the row.
1668136068864.png


The thing I'd like help with is my AGENT tab. I have a list of employee down column B. If John Smith's name is in B2, then C2 should display 8:00 (first non-zero his row on the MON tab).

Lastly, if the formula is not terribly long, I'd like to try and concatenate C2 so that it would read "first non-zero cell time interval" &" - " &"last non-zero cell time interval"
1668135991482.png

Any help is greatly appreciated!!
 

Attachments

  • 1668135890258.png
    1668135890258.png
    4.6 KB · Views: 9
  • 1668135989435.png
    1668135989435.png
    2.2 KB · Views: 10

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
C2:
Excel Formula:
=LET(m,MATCH($B2,$A$5:$A$8,0),i,INDEX($B$5:$Q$8<>0,m),TEXTJOIN(" - ",0,TEXT(INDEX($B$5:$AQ$5,MATCH(TRUE,i,0)),"hh:mm"),TEXT(INDEX($B$5:$AQ$5,MATCH(TRUE,i,1)),"hh:mm")))
If it is too long then, you may only use the INDEX parts. Have a nice day :)
 
Last edited by a moderator:
Upvote 0
It may be even shorter:
Excel Formula:
=LET(i,INDEX($B$5:$Q$8<>0,MATCH($B2,$A$5:$A$8,0)),TEXTJOIN(" - ",0,TEXT(INDEX($B$5:$AQ$5,MATCH(TRUE,i,0)),"hh:mm"),TEXT(INDEX($B$5:$AQ$5,MATCH(TRUE,i,1)),"hh:mm")))
 
Upvote 0
Another option
Excel Formula:
=LET(f,FILTER(MON!$B$5:$AQ$5,FILTER(MON!B6:AQ100,MON!A6:A100=B2)<>0),"first non-zero cell time interval "&TAKE(f,,1)&" - "&TAKE(f,,-1)&" last non-zero cell time interval")
 
Upvote 0
C2:
Excel Formula:
=LET(m,MATCH($B2,$A$5:$A$8,0),i,INDEX($B$5:$Q$8<>0,m),TEXTJOIN(" - ",0,TEXT(INDEX($B$5:$AQ$5,MATCH(TRUE,i,0)),"hh:mm"),TEXT(INDEX($B$5:$AQ$5,MATCH(TRUE,i,1)),"hh:mm")))
If it is too long then, you may only use the INDEX parts. Have a nice day :)
I used this one and of the 3 provided, this is the closest to doing what I had hoped! One thing I had failed to realize in my explanation is that the employee breaks (b) and lunches (l) are plotted along the same row. Because they break up the 1s, it throws your formula off. Can the formula possibly be adjusted so that it still displays the first non-zero cell along the row and then the hyphen and lastly, the first zero cell after the series of 1s?
 
Upvote 0
It may be even shorter:
Excel Formula:
=LET(i,INDEX($B$5:$Q$8<>0,MATCH($B2,$A$5:$A$8,0)),TEXTJOIN(" - ",0,TEXT(INDEX($B$5:$AQ$5,MATCH(TRUE,i,0)),"hh:mm"),TEXT(INDEX($B$5:$AQ$5,MATCH(TRUE,i,1)),"hh:mm")))
I made another effort with this one. Apparently I didn't adjust the formula accurately the first time. Seeing this do the trick makes this the better of the 2! My request from before still applies, though: instead of the last non-zero cell being located in the formula, can it be replaced with the first zero value cell after the series of 1s begins?
 
Upvote 0
Another option
Excel Formula:
=LET(f,FILTER(MON!$B$5:$AQ$5,FILTER(MON!B6:AQ100,MON!A6:A100=B2)<>0),"first non-zero cell time interval "&TAKE(f,,1)&" - "&TAKE(f,,-1)&" last non-zero cell time interval")
I'm getting the #NAME error with this. Apologies I didn't actually need the "first non-zero cell" and "last non-zero cell". Those were just illustrating the values that I wanted to populate within the results cell.
 
Upvote 0
This should work:
Excel Formula:
=TEXTJOIN(" - ",0,TEXT(INDEX($B$5:$AQ$5,MATCH(TRUE,INDEX($B$6:$AQ$8<>0,MATCH($B2,$A$6:$A$8,0)),0)),"hh:mm"),TEXT(INDEX($B$5:$AQ$5,MAX(INDEX(($B$6:$AQ$8<>0)*COLUMN($B$1:$AQ$1),MATCH($B2,$A$6:$A$8,0)))),"hh:mm"))
 
Upvote 0
See if this one does what you want.

Darren_workforce.xlsm
ABCDEFGHIJKLM
58:008:158:308:459:009:159:309:4510:0010:1510:3010:45
6John Smith111111111100
7Brad Cooper000011110000
8Mary Moore000001000000
MON


Darren_workforce.xlsm
BC
1
2John Smith8:00 - 10:30
3Mary Moore9:15 - 9:30
4Brad Cooper9:00 - 10:00
AGENT
Cell Formulas
RangeFormula
C2:C4C2=LET(s,TEXTJOIN(REPT(" ",20),1,FILTER(MON!B$5:AQ$5,FILTER(MON!B$6:AQ$20,MON!A$6:A$20=B2)=1)),TRIM(TEXT(LEFT(s,20),"h:mm")&TEXT(RIGHT(s,20)+1/96," - h:mm")))
 
Upvote 0
Solution
See if this one does what you want.

Darren_workforce.xlsm
ABCDEFGHIJKLM
58:008:158:308:459:009:159:309:4510:0010:1510:3010:45
6John Smith111111111100
7Brad Cooper000011110000
8Mary Moore000001000000
MON


Darren_workforce.xlsm
BC
1
2John Smith8:00 - 10:30
3Mary Moore9:15 - 9:30
4Brad Cooper9:00 - 10:00
AGENT
Cell Formulas
RangeFormula
C2:C4C2=LET(s,TEXTJOIN(REPT(" ",20),1,FILTER(MON!B$5:AQ$5,FILTER(MON!B$6:AQ$20,MON!A$6:A$20=B2)=1)),TRIM(TEXT(LEFT(s,20),"h:mm")&TEXT(RIGHT(s,20)+1/96," - h:mm")))
Thank you VERY MUCH!!!! It feels complicated but it did exactly what I was looking for. Supervisors have been wanting a more readable version of my scheduler and this helps make it very easy to transform into just that. Thank you again!!!
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,972
Members
448,537
Latest member
Et_Cetera

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