excelexcel-formulagroup-bypivot-tableranking-functions

Unable to rank/ pivot data in Excel

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

User Manufacturer Model OS OS Version Last Synch Date Compliance
a OnePlus HD1901 Android 11 9/22/2022 13:15 Compliant
a OnePlus HD1901 Android 11 9/1/2022 12:27 Noncompliant
a OnePlus HD1901 Android 11 9/2/2022 8:03 Compliant
a Apple iPad Air 2 iOS/iPadOS 15.6.1 9/6/2022 6:33 Compliant
b Apple iPhone 12 iOS/iPadOS 16 9/6/2022 6:33 Compliant

Required data-set is below:- Required Data

User Manufacturer Model OS OS Version Last Synch Date Compliance
a OnePlus HD1901 Android 11 9/22/2022 13:15 Compliant
a Apple iPad Air 2 iOS/iPadOS 15.6.1 9/6/2022 6:33 Compliant
b Apple iPhone 12 iOS/iPadOS 16 9/6/2022 6:33 Compliant

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. Pivot

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!

Solution

  • enter image description here

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