First And Last Value (1 Viewer)

[Andy Teal]

i want first and last value(time) of a given date in two different columns
im able to get the first value through vlookup with a helper column, but have trouble with the last value

				Result
Helper Column	Name	Date	Time in/Out	First Value	Last Value
Young|45068	Young	22/05/2023	8.24.01	8.24.01	17.35.59
Young|45068	Young	22/05/2023	17.35.59
Young|45069	Young	23/05/2023	8.20.47	8.20.47	17.24.43
Young|45069	Young	23/05/2023	17.18.13
Young|45069	Young	23/05/2023	17.24.43

i have attached excel file, please provide solution for the above query.

 

[Uncle Gizmo]

ChatGPT Said:-

Since the VBA language doesn't directly support finding the last match in a range with a specified value, the simplest solution would be to use an array formula to find the last timestamp for a particular date. However, please note that array formulas can be computationally expensive and may slow down Excel for large datasets.

Suppose your `Helper Column` is in column A, `Name` is in column B, `Date` is in column C, `Time in/Out` is in column D, `First Value` is in column E, and `Last Value` is in column F. You want to fill in the `Last Value` for each row in column F.

You can use the following array formula in cell F2 to find the last timestamp for a given date:

=MAX(IF($A$2:$A$6=A2, $D$2:$D$6))
```

Here's how to input it:

1. Click on cell F2.
2. Paste the formula into the formula bar.
3. Instead of hitting Enter, press Ctrl + Shift + Enter. Excel will surround the formula with `{}`, indicating it's an array formula.

This formula checks each row in the range A2:A6. If the `Helper Column` value is equal to the `Helper Column` value in the current row (A2), it considers the corresponding `Time in/Out` value (from D2 : D6). It uses the `MAX` function to return the latest (max) `Time in/Out` value among the considered ones.

Please adjust the ranges `$A$2:$A$6` and `$D$2:$D$6` according to your actual data range.

If your dataset is large, a VBA macro might be a more efficient solution.[/code]

 

[The_Doc_Man]

Please note that if the Excel sheet isn't sorted, First and Last have uncertain meanings. Min and Max are more certain in their meaning.