Return a time from an array with multiple filters

troyh68

New Member
Joined
Nov 14, 2016
Messages
20
I am looking for a formula that will find the last Product Name within a given Hour of time. In the attached spreadsheet I am in need of the Hour (Column H) The Product (Column I) will be easy to get with an If Statement. I have tried to do IF statements, Nested IF's and COUNTIFS I can almost get it but then I miss one of the variables. I feel like INDEX MATCH will work but I have very little experience with them.

Maybe this will help tell the story also. I work for a small nationwide bakery and we are producing different buns each day and want to get to a Pieces per Hour KPI.

Summarize
Need to be able to have the formula recognize that the Hour has changed OR if the Product has changed OR if the HOUR and the Product has changed.

Pieces per Hour EXAMPLE.xlsx
ABCDEFGHIJ
1EXAMPLE OF WHAT I NEED
2Rack #Time StampProductLocationDate HelperHour HelpHourProduct
319/27/21 10:50 AMBurger9/27/202110:00
429/27/21 10:50 AMBurger9/27/202110:0010:00Burger
539/27/21 11:28 AMBurger9/27/202111:00
649/27/21 11:31 AMBurger9/27/202111:00
759/27/21 11:32 AMBurger9/27/202111:00
869/27/21 11:32 AMBurger9/27/202111:00
979/27/21 11:39 AMBurger9/27/202111:0011:00Burger
1089/27/21 12:02 PMBurger9/27/202112:00
1199/27/21 12:04 PMBurger9/27/202112:00
12109/27/21 12:05 PMBurger9/27/202112:00
13119/27/21 12:09 PMBurger9/27/202112:0012:00Burger
1419/27/21 12:09 PMMini9/27/202112:00
1529/27/21 12:10 PMMini9/27/202112:00
1639/27/21 12:10 PMMini9/27/202112:00
1749/27/21 12:10 PMMini9/27/202112:00
1859/27/21 12:10 PMMini9/27/202112:00
1969/27/21 12:11 PMMini9/27/202112:0012:00Mini
2019/27/21 12:15 PMSausage9/27/202112:00
2129/27/21 12:15 PMSausage9/27/202112:00
2239/27/21 12:15 PMSausage9/27/202112:00
2349/27/21 12:15 PMSausage9/27/202112:0012:00Sausage
24
Sheet1
Cell Formulas
RangeFormula
F3:F23F3=IF(C3="","",INT(C3))
G3:G23G3=IF(F3="","",HOUR(C3)&":00")
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
66,064
Office Version
  1. 365
Platform
  1. Windows
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

How about
+Fluff 1.xlsm
ABCDEFGHI
1
2Rack #Time StampProductLocationDate HelperHour HelpHourProduct
3127/9/21 10:50Burger4446610:00  
4227/9/21 10:50Burger4446610:0010:00Burger
5327/9/21 11:28Burger4446611:00  
6427/9/21 11:31Burger4446611:00  
7527/9/21 11:32Burger4446611:00  
8627/9/21 11:32Burger4446611:00  
9727/9/21 11:39Burger4446611:0011:00Burger
10827/9/21 12:02Burger4446612:00  
11927/9/21 12:04Burger4446612:00  
121027/9/21 12:05Burger4446612:00  
131127/9/21 12:09Burger4446612:0012:00Burger
14127/9/21 12:09Mini4446612:00  
15227/9/21 12:10Mini4446612:00  
16327/9/21 12:10Mini4446612:00  
17427/9/21 12:10Mini4446612:00  
18527/9/21 12:10Mini4446612:00  
19627/9/21 12:11Mini4446612:0012:00Mini
20127/9/21 12:15Sausage4446612:00  
21227/9/21 12:15Sausage4446612:00  
22327/9/21 12:15Sausage4446612:00  
23427/9/21 12:15Sausage4446612:0012:00Sausage
Main
Cell Formulas
RangeFormula
F3:F23F3=IF(C3="","",INT(C3))
G3:G23G3=IF(F3="","",HOUR(C3)&":00")
H3:H23H3=IF(OR(HOUR(C3)<>HOUR(C4),D3<>D4),HOUR(C3)/24,"")
I3:I23I3=IF(H3="","",D3)
 

JEC

Well-known Member
Joined
Aug 21, 2021
Messages
571
Office Version
  1. 365
Platform
  1. Windows
In H2 and fill down:
Excel Formula:
=IF(OR(H3<>H2,E2<>E3),H2,"")
In I2 and fill down:
Excel Formula:
=IF(I2<>"",E2,"")
 
Solution

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
66,064
Office Version
  1. 365
Platform
  1. Windows
Both of those will give you a circular reference. ;)
 

JEC

Well-known Member
Joined
Aug 21, 2021
Messages
571
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Hmm formula's are based on the sample data. I don't see where the circular reference is. Also didn't get a warning.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
66,064
Office Version
  1. 365
Platform
  1. Windows
The circular reference is because you said to put the formula in H2, but it's referring to itself.
Hmm formula's are based on the sample data.
:unsure: Then why are you also looking at column E which is blank?
 

JEC

Well-known Member
Joined
Aug 21, 2021
Messages
571
Office Version
  1. 365
Platform
  1. Windows
Ahh I see where it went wrong :LOL: . I did copy the data the normal way. Didn't use the button in the left corner this time, because it pasted all formula's instead of values.
 

troyh68

New Member
Joined
Nov 14, 2016
Messages
20
Ahh I see where it went wrong :LOL: . I did copy the data the normal way. Didn't use the button in the left corner this time, because it pasted all formula's instead of values.
Thank you for this. Much simpler than I thought it would be.
 

Forum statistics

Threads
1,148,364
Messages
5,746,276
Members
424,003
Latest member
paaskanama

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
Top