I have a excel sheet which displays the status of various products on my website throughout the day, along with the timestamp of the status as so:
| Timestamp | Product 1 | Product 2 | Product 3 | Product 4 |
|-------------------------|-----------|-----------|-----------|-----------|
| 25/03/2021 08:15:52.020 | 999 | | 0 | 999 |
| 25/03/2021 08:15:53.000 | 0 | 101 | 0 | |
| 25/03/2021 08:15:53.070 | 0 | 0 | | 0 |A status of 0 means a product is available, while blank cells/other numbers indicate a product is unavailable.
For each Product I would like to get the first timestamp for which the product is available (i.e. when the value in the cell is 0 for the first time, not non-0 or blank). The end result being something like this:
| | Timestamp |
|-----------|-------------------------|
| Product 1 | 25/03/2021 08:15:53.000 |
| Product 2 | 25/03/2021 08:15:53.070 |
| Product 3 | 25/03/2021 08:15:52.020 |
| Product 4 | 25/03/2021 08:15:53.070 |Is this possible to do using pure excel functions? Or would I need to use some Python/Pandas to get my desired table? Thank you!!
3 Answers
This can be done using INDEX/MATCH
Assuming your data is in A1:E4, for product 1, you would use
=INDEX($A2:$A4,MATCH(0, B2:B4,0))(change B2:B4 to C2:C4 and so on for the other products)
Expanding on @cybernetic's solution, the formula in you second sheet can look up the column it should use. So sheet2, B2 is (assuming Z product columns, and 100 rows):
=INDEX(Sheet1!$A$2:$A$100, MATCH(0, INDEX(Sheet1!$B$2:$Z$100,,MATCH(A2, Sheet1!$B$1:$Z$1,0)),0))Breaking it down, the first MATCH from the right looks up the product name and returns a column index 1 through 25 (relative to B - Z). The first INDEX from the right returns a column vector containing the status info for the current product. The second MATCH looks for the first occurrence of zero in this column vector and returns the row index. The second INDEX uses this final index to pick out the timestamp on that row.
EDIT: Better yet, assuming the order of the listed products in sheet 1 and 2 stays the say, you don't have to search (MATCH), but can simply use the row number of the product in sheet 2 as column index in sheet 1...
=INDEX(Sheet1!$A$2:$A$100, MATCH(0, INDEX(Sheet1!$B$2:$Z$100,,ROW()-2+1),0))where ROW()-2+1 gives an index of 1, 2, 3, 4... starting at row 2.
1You could also use INDEX and XMATCH function.
For Product 1 is =INDEX(A2:E4;XMATCH(0;B2:B4);1).
Just change B2:B4 to C2:C4 and so on for other products.
