The dataset below contains device entries for both "Noncompliant" and "Compliant" category, however the focus area is "Noncompliant" devices only for further action.

Required data-set is below:-

So, the requirement for data to be shown are below:-

1. show rows with latest value in "Last Sync Date" column only.
2. show rows with status of "Compliant" value in the "Compliance" column. Means that, when filtered later on "Noncompliant", then no need to show any data, because the latest entry (identified with "Last Sync Date") is indeed of "compliant" status.

As of now, I tried to achieve this using Pivot Table, but getting this issue that when filter on "Noncompliant", then, it shows unqualified data-entry of the device, which has indeed turned into a "Compliant" category in a later sync.

I think that I need some kind of ranking/ dense-ranking to achieve this conditional data. Guess this may not be able to achieve in Pivot alone, so first need to do data-massaging upfront by adding a new column in "Full Data". But unable to make-out so far using MAXIFS, SUMPRODUCT, CONCAT etc. Appreciate your kind help!

CodePudding user response：

A9

=UNIQUE(FILTER(A2:E6,G2:G6="Compliant",""))

F9

=BYROW(B9#,LAMBDA(R,MAX(IF((A2:A6=INDEX(R,,1))*(B2:B6=INDEX(R,,2)*(C2:C6=INDEX(R,,3))*(D2:D6=INDEX(R,,4))*(E2:E6=INDEX(R,,5))*(G2:G6="Compliant"),F2:F6))))

G9

=TRANSPOSE(SUBSTITUTE("Compliant"," ","",SEQUENCE(1,ROWS(UNIQUE(FILTER(A2:E6,G2:G6="Compliant",""))))))