Help with if max collect formula
Hello,
Sorry I know there are already questions on this but I'm not getting it.
I have a column [Users] of duplicate users, and a second column with unique numbers [Row ID]
In a third column I am trying to get "Ignore" or "Select" based on the highest number per user
So in the example above the PCUK832 should say ignore (because the project with the highest number for person B is PCUK831).
I have followed some other help to get the following:
=IF(MAX(COLLECT([Row ID]:[Row ID], User:User, User132)) <> 0, "SELECT", "IGNORE")
but although there is no error, it is not doing what I want.
I'm wondering if the issue is that the column name [User] keeps coming out of those brackets [] ?
Just to clarify I want the formula to find the max row id for each unique user and "SELECT" those, ignoring the rest.
Anyway, I would really appreciate any help. I will be thinking about this throughout my winter break <Doh!>.
Many thanks,
Charlotte
Best Answers
-
Would this work for you?
=IF(MAX(COLLECT([Row ID]:[Row ID], User:User,User@row)) = [Row ID]@row, "Select", "Ignore")
This is comparing the Max RowID for the User in the same row and returning Select if the current RowID is equal to the Maximum RowID for that user, or Ignore if there is a higher value somewhere.
You can see here that the first entry for "a" is 19 and the second is 0, therefore the first entry is selected as 19 is higher than 0. Likewise "b"s first entry is 18 and b's second entry is 17, therefore the first entry is set to Select and the second Ignore.
This logic works for me - does this do what you are looking for?
Kind regards
Debbie
-
Hi Charlotte
Give this a go
=IF(ISBLANK(User@row),"",IF(MAX(COLLECT([Row ID]:[Row ID], User:User,User@row)) = [Row ID]@row, "Select", "Ignore"))
Should remove the Selects from empty users...
Fingers crossed this helps
Debbie
Answers
-
Would this work for you?
=IF(MAX(COLLECT([Row ID]:[Row ID], User:User,User@row)) = [Row ID]@row, "Select", "Ignore")
This is comparing the Max RowID for the User in the same row and returning Select if the current RowID is equal to the Maximum RowID for that user, or Ignore if there is a higher value somewhere.
You can see here that the first entry for "a" is 19 and the second is 0, therefore the first entry is selected as 19 is higher than 0. Likewise "b"s first entry is 18 and b's second entry is 17, therefore the first entry is set to Select and the second Ignore.
This logic works for me - does this do what you are looking for?
Kind regards
Debbie
-
Hi Debbie,
Thank you very much especially for getting back to me so quickly!
Just to say though that it writes "Select" on all the rows when there is no user filled in (this is not an issue for me because it does only say "Select" on the highest row ID and I will be adding a filter on the reports for "Select" & Current User).
Many thanks,
Charlotte
-
Hi Charlotte
Give this a go
=IF(ISBLANK(User@row),"",IF(MAX(COLLECT([Row ID]:[Row ID], User:User,User@row)) = [Row ID]@row, "Select", "Ignore"))
Should remove the Selects from empty users...
Fingers crossed this helps
Debbie
-
Thanks Debbie,
That second answer is perfect!
