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/2开发者_JAVA百科019 | 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
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
精彩评论