I have a table as follows, which has Unique IDs for each person and some dates
| NAME | DATE | Info |
|---|---|---|
| John | 01/11/2022 | Praesent accumsan. |
| John | 29/11/2022 | Phasellus fermentum. |
| John | 30/11/2022 | Curabitur molestie. |
| Peter | 09/05/2019 | Cras mollis est. |
| Peter | 06/05/2019 | Nulla eu metus. |
| Peter | 06/05/2019 | Proin commodo. |
| Peter | 20/09/2022 | Nunc rhoncus dui. |
| Peter | 22/09/2022 | Aliquam accumsan. |
| Beth | 11/08/2021 | Integer sollicitudin. |
| Beth | 13/09/2021 | Integer eget dolor. |
| Beth | 13/09/2021 | Cras vitae massa non. |
| Sarah | 02/12/2021 | Cras interdum nibh. |
| Sarah | 13/04/2022 | Mauris cursus augue. |
| Sarah | 13/04/2022 | Sed varius lacus. |
| Sarah | 14/04/2022 | Aliquam lacinia. |
| Sarah | 18/05/2022 | Fusce scelerisque. |
| Sarah | 19/05/2022 | Suspendisse viverra. |
| Sarah | 02/06/2022 | Ut nec dui molestie. |
| Sarah | 07/06/2022 | Maecenas ac neque nec. |
| Sarah | 19/10/2022 | Mauris sodales tellus. |
| Sarah | 19/10/2022 | Pellentesque auctor. |
| Sarah | 20/10/2022 | Morbi fringilla felis. |
| Sarah | 21/10/2022 | Praesent fringilla. |
| Mathew | 18/01/2021 | Fusce sagittis dui. |
| Mathew | 18/01/2021 | Nunc at erat eget. |
| Mathew | 19/01/2021 | Sed nec mauris eu. |
| Mathew | 19/01/2021 | Aenean a arcu nec. |
| Mathew | 03/02/2021 | Nunc mollis turpis. |
I want to get the latest date for each ID, and tag it somehow, I thought about doing it by Conditional formatting, this table is currently on googlesheets.
For example, John's would be 30/11/2022, Peter 22/09/2022, beth would be the multiple 13/09/2021 ones, Sarah would be 21/10/2022, Mathew would be 03/02/2021.
This simplified version has only 5 IDs (that I converted to names) and some dates and info, but the real one has hundreds of IDs and hundreds of dates for each one.
This table will keep self populating with newer info all the time, but for focus purposes only the last input on each ID is important.
I tried Maxif or other approaches with no success, even a tag on a new column would really help. I mean, maxif did showed the latest date on a new column but I wasn't able to pinpoint the line it belonged to for each ID.
Any help would be appreciated :)
Thanks, Rafael
CodePudding user response：
You can have a formula like this:
=MATCH($A2&$B2,UNIQUE($A2:$A)&BYROW(UNIQUE($A2:$A),LAMBDA(each,MAX(FILTER($B$2:$B,$A$2:$A = each)))))
But, if you have a really big table, you could create an auxiliary column with the formula in order to make it faster:
={UNIQUE(A2:A),BYROW(UNIQUE(A2:A),LAMBDA(each,MAX(FILTER(B2:B,A2:A = each))))}
And then you can set a conditional formatting like this:
=($A2<>0)*MATCH($A2&$B2,ARRAYFORMULA($E:$E&$F:$F),0)
Or you could even join those two columns in just one
CodePudding user response：
You will definitely need a helper column if you have a lot of data. I tried using a fairly simple match/countifs formula on 50K of data and it took about 20 minutes to update.
However there is a solution available 