开发者

Google Sheets - Latest date of each summary value - Conditional Formatting

开发者 https://www.devze.com 2022-12-07 20:37 出处:网络
I have a table as follows, which has Unique IDs for each person and some dates NAME DATE Info John 01/11/2022

I have a table as follows, which has Unique IDs for each person and some dates

Google Sheets - Latest date of each summary value - Conditional Formatting

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)))))

Google Sheets - Latest date of each summary value - Conditional Formatting

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))))}

Google Sheets - Latest date of each summary value - Conditional Formatting

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

0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号