2

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!

P.b
  • 8,293
  • 2
  • 10
  • 25
Channdeep Singh
  • 103
  • 2
  • 10

1 Answers1

1

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",""))))))
Terio
  • 507
  • 2
  • 5
  • Check if BYROW is present, otherwise update 365. If not, I write formula to drag – Terio Sep 25 '22 at 06:16
  • 1
    For `F9` you can make use of `=MAXIFS(F2:F6,A2:A6,INDEX(A9#,,1),B2:B6,INDEX(A9#,,2),C2:C6,INDEX(A9#,,3),D2:D6,INDEX(A9#,,4),E2:E6,INDEX(A9#,,5))` which spills the result already. – P.b Sep 25 '22 at 07:37
  • @Channdeep Singh getting the OS version to recognize the difference between a different version or an upgrade would be a whole different question and would require a separate database. – P.b Sep 25 '22 at 07:41
  • For fun: `=LET(rng,A1:G6,hdr,INDEX(rng,1,),D,DROP(rng,1),_D1,INDEX(D,,1),_D2,INDEX(D,,2),_D3,INDEX(D,,3),_D4,INDEX(D,,4),_D5,INDEX(D,,5),_D6,INDEX(D,,6),_D7,INDEX(D,,7),r,SEQUENCE(ROWS(D)),M,UNIQUE(INDEX(D,r,SEQUENCE(1,5))),_M1,INDEX(M,,1),_M2,INDEX(M,,2),_M3,INDEX(M,,3),_M4,INDEX(M,,4),_M5,INDEX(M,,5),Synch,MAXIFS(_D6,_D1,_M1,_D2,_M2,_D3,_M3,_D4,_M4,_D5,_M5),Status,BYROW(SEQUENCE(COUNTA(_M1)),LAMBDA(x,INDEX(_D7,XMATCH(1,(_D1=INDEX(_M1,x))*(_D2=INDEX(_M2,x))*(_D3=INDEX(_M3,x))*(_D4=INDEX(_M4,x))*(_D5=INDEX(_M5,x))*(_D6=INDEX(Synch,x)))))),VSTACK(hdr,HSTACK(M,Synch,Status)))` – P.b Sep 25 '22 at 10:16
  • https://i.stack.imgur.com/M4aYY.jpg – P.b Sep 25 '22 at 10:17