troyh68
New Member
- Joined
- Nov 14, 2016
- Messages
- 24
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.
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") |