Return a time from an array with multiple filters

[troyh68]

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
	A	B	C	D	E	F	G	H	I	J
1								EXAMPLE OF WHAT I NEED
2		Rack #	Time Stamp	Product	Location	Date Helper	Hour Help	Hour	Product
3		1	9/27/21 10:50 AM	Burger		9/27/2021	10:00
4		2	9/27/21 10:50 AM	Burger		9/27/2021	10:00	10:00	Burger
5		3	9/27/21 11:28 AM	Burger		9/27/2021	11:00
6		4	9/27/21 11:31 AM	Burger		9/27/2021	11:00
7		5	9/27/21 11:32 AM	Burger		9/27/2021	11:00
8		6	9/27/21 11:32 AM	Burger		9/27/2021	11:00
9		7	9/27/21 11:39 AM	Burger		9/27/2021	11:00	11:00	Burger
10		8	9/27/21 12:02 PM	Burger		9/27/2021	12:00
11		9	9/27/21 12:04 PM	Burger		9/27/2021	12:00
12		10	9/27/21 12:05 PM	Burger		9/27/2021	12:00
13		11	9/27/21 12:09 PM	Burger		9/27/2021	12:00	12:00	Burger
14		1	9/27/21 12:09 PM	Mini		9/27/2021	12:00
15		2	9/27/21 12:10 PM	Mini		9/27/2021	12:00
16		3	9/27/21 12:10 PM	Mini		9/27/2021	12:00
17		4	9/27/21 12:10 PM	Mini		9/27/2021	12:00
18		5	9/27/21 12:10 PM	Mini		9/27/2021	12:00
19		6	9/27/21 12:11 PM	Mini		9/27/2021	12:00	12:00	Mini
20		1	9/27/21 12:15 PM	Sausage		9/27/2021	12:00
21		2	9/27/21 12:15 PM	Sausage		9/27/2021	12:00
22		3	9/27/21 12:15 PM	Sausage		9/27/2021	12:00
23		4	9/27/21 12:15 PM	Sausage		9/27/2021	12:00	12:00	Sausage
24
Sheet1

Cell Formulas
Range	Formula
F3:F23	F3	=IF(C3="","",INT(C3))
G3:G23	G3	=IF(F3="","",HOUR(C3)&":00")

 

[Fluff]

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
	A	B	C	D	E	F	G	H	I
1
2		Rack #	Time Stamp	Product	Location	Date Helper	Hour Help	Hour	Product
3		1	27/9/21 10:50	Burger		44466	10:00
4		2	27/9/21 10:50	Burger		44466	10:00	10:00	Burger
5		3	27/9/21 11:28	Burger		44466	11:00
6		4	27/9/21 11:31	Burger		44466	11:00
7		5	27/9/21 11:32	Burger		44466	11:00
8		6	27/9/21 11:32	Burger		44466	11:00
9		7	27/9/21 11:39	Burger		44466	11:00	11:00	Burger
10		8	27/9/21 12:02	Burger		44466	12:00
11		9	27/9/21 12:04	Burger		44466	12:00
12		10	27/9/21 12:05	Burger		44466	12:00
13		11	27/9/21 12:09	Burger		44466	12:00	12:00	Burger
14		1	27/9/21 12:09	Mini		44466	12:00
15		2	27/9/21 12:10	Mini		44466	12:00
16		3	27/9/21 12:10	Mini		44466	12:00
17		4	27/9/21 12:10	Mini		44466	12:00
18		5	27/9/21 12:10	Mini		44466	12:00
19		6	27/9/21 12:11	Mini		44466	12:00	12:00	Mini
20		1	27/9/21 12:15	Sausage		44466	12:00
21		2	27/9/21 12:15	Sausage		44466	12:00
22		3	27/9/21 12:15	Sausage		44466	12:00
23		4	27/9/21 12:15	Sausage		44466	12:00	12:00	Sausage
Main

Cell Formulas
Range	Formula
F3:F23	F3	=IF(C3="","",INT(C3))
G3:G23	G3	=IF(F3="","",HOUR(C3)&":00")
H3:H23	H3	=IF(OR(HOUR(C3)<>HOUR(C4),D3<>D4),HOUR(C3)/24,"")
I3:I23	I3	=IF(H3="","",D3)

 

[JEC]

In H2 and fill down:

=IF(OR(H3<>H2,E2<>E3),H2,"")

In I2 and fill down:

=IF(I2<>"",E2,"")

 

[Fluff]

Both of those will give you a circular reference.

 

[JEC]

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]

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.

Then why are you also looking at column E which is blank?

 

[JEC]

Ahh I see where it went wrong . 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]

Ahh I see where it went wrong . 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.