Trying to label rows in Google Sheets where there are duplicate values in one column in order of of-CodePudding

I have a list of 1000s of users, their assigned devices, and the device's end of life dates.

I need to label everyone's newest device (furthest end of life date) as Primary and all others as Secondary. The problem is with the duplicate users where some have multiple devices. For example, If someone has 3 devices I need to label 2 of them secondary and their newest one primary.

I hope this makes sense. I've attached an example screenshot of what I'm trying to achieve.

Tried using UNIQUE and COUNTIF and pivot tables but I'm not getting anywhere

CodePudding user response：

Put this formula into D1. What it does is, it get the data range A1:C8 to form a QUERY, which arrange the data according to 1st: User, 2nd: EndDate (in descending order),

than it uses FILTER function to filter the query with the row data of BYROW function, to found out if the date of each row is equal to the 1st date column of the filter result of the query or not, if true than apply "Primary", otherwise apply "Secondary".

If you need to change the data range, just change the ref.range of the very last line in the code.

=LAMBDA(DATA,
 LAMBDA(QUERY,
  LAMBDA(USER,PROFILE,DATE,
   BYROW(DATA,
    LAMBDA(ROW,
     IF(INDEX(ROW,,1)="User",
      "Device Usage",
      IF(INDEX(ROW,,3)=INDEX(FILTER(QUERY,USER=INDEX(ROW,,1)),1,3),"Primary","Secondary")
     )
    )
   )
  )(INDEX(QUERY,,1),INDEX(QUERY,,2),INDEX(QUERY,,3))
 )(QUERY({DATA},"ORDER BY Col1,Col3 DESC"))
)(A1:C8)

Just noticed that there are 2 unused variables, the code can be simplified a bit:

=LAMBDA(DATA,
 LAMBDA(QUERY,
  LAMBDA(USER,
   BYROW(DATA,
    LAMBDA(ROW,
     IF(INDEX(ROW,,1)="User",
      "Device Usage",
      IF(INDEX(ROW,,3)=INDEX(FILTER(QUERY,USER=INDEX(ROW,,1)),1,3),"Primary","Secondary")
     )
    )
   )
  )(INDEX(QUERY,,1))
 )(QUERY({DATA},"ORDER BY Col1,Col3 DESC"))
)(A1:C8)

This can also be done without the QUERY function:

=LAMBDA(DATA,
 LAMBDA(USER_COL,
  BYROW(DATA,LAMBDA(ROW,
   LAMBDA(USER,DATE,
    IF(USER="User",
     "Device Usage",
     IF(DATE=MAX(INDEX(FILTER(DATA,USER_COL=USER),,3)),"Primary","Secondary")
    )
   )(INDEX(ROW,,1),INDEX(ROW,,3))
  ))
 )(INDEX(DATA,,1))
)(A1:C8)