Thanks M, works a treat. Is there a way of extending this out so M2 shows "3", N2 shows "Steve". The more info i can get here the better for my situation.
Thanks again.
Maybe this..
M 1 List 2 3 3 6 4
Array formula in M2 copied down
=IFERROR(INDEX(A$2:A$14,SMALL(IF(FREQUENCY(IF(ISNA(MATCH(A$2:A$14,IF((C$2:C$14<>"")+(D$2:D$14="Yes"),A$2:A$14),0)),MATCH(A$2:A$14,A$2:A$14,0)),ROW(A$2:A$14)-ROW(A$2)+1),ROW(A$2:A$14)-ROW(A$2)+1),ROWS(M$2:M2))),"")
Ctrl+Shift+Enter
M.
Thanks M, works a treat. Is there a way of extending this out so M2 shows "3", N2 shows "Steve". The more info i can get here the better for my situation.
Thanks again.
Hi M,
Tried it but it's not right.
=IFERROR(INDEX(A$2:A$14,SMALL(IF(FREQUENCY(IF(ISNA(MATCH($A$2:$A$14,IF(($C$2:$C$14<>"")+($D$2:$D$14="no"),$A$2:$A$14),0)),MATCH($A$2:$A$14,$A$2:$A$14,0)),ROW($A$2:$A$14)-ROW($A$2)+1),ROW(A$2:A$14)-ROW(A$2)+1),ROWS(N$2:N2))),"")
This gives me the User ID of 9 which is correct.
But
=IFERROR(INDEX(B$2:B$14,SMALL(IF(FREQUENCY(IF(ISNA(MATCH($B$2:$B$14,IF(($C$2:$C$14<>"")+($D$2:$D$14="No"),$B$2:$B$14),0)),MATCH($B$2:$B$14,$B$2:$B$14,0)),ROW($B$2:$B$14)-ROW($B$2)+1),ROW(B$2:B$14)-ROW(B$2)+1),ROWS(N$2:N2))),"")
This gives me the name Trev but it should be Oliver as this is the name for User ID 9.
Any ideas what i'm doing wrong ?
Thanks_{ }
Your formula seems perfect to me. Trev and Oliver satisfy the conditions.
M.
Hi M,
This isn't right.
One of the earlier formulas was the following :-
=SUM(IF(FREQUENCY(IF(ISNA(MATCH(A2:A14,IF((C2:C14<>"")+(D2:D14="No"),A2:A14),0)),MATCH(A2:A14,A2:A14,0)),ROW(A2:A14)-ROW(A2)+1),1))
The result for this was 1. This is because there is 1 generic user ID where UTC=Yes and Last Logon is Blank. This is correct. Next I wanted a list of the results instead of a number.
This formula:-
=IFERROR(INDEX(A$2:A$14,SMALL(IF(FREQUENCY(IF(ISNA(MATCH($A$2:$A$14,IF(($C$2:$C$14<>"")+($D$2:$D$14="no"),$A$2:$A$14),0)),MATCH($A$2:$A$14,$A$2:$A$14,0)),ROW($A$2:$A$14)-ROW($A$2)+1),ROW(A$2:A$14)-ROW(A$2)+1),ROWS(N$2:N2))),"")
This gives me number 9 which is also correct as this is the user that meets the criteria on the above formula. What I tried to do with the following formula was pull the next cell to number 9 which was the name Oliver. However I get the name Trev instead but this person does not meet the criteria as their User ID is 4 and there is also another 4 that does not meet the criteria.
=IFERROR(INDEX(B$2:B$14,SMALL(IF(FREQUENCY(IF(ISNA(MATCH($B$2:$B$14,IF(($C$2:$C$14<>"")+($D$2:$D$14="No"),$B$2:$B$14),0)),MATCH($B$2:$B$14,$B$2:$B$14,0)),ROW($B$2:$B$14)-ROW($B$2)+1),ROW(B$2:B$14)-ROW(B$2)+1),ROWS(O$2:O2))),"")
So Trev should never be part of this formula as there are 2 x User ID 4's and one does not meet the criteria.
Hope this makes sense.
Thanks as always._{ }
Hi M,
All sorted, my error. Got it working.
Thanks again.
Like this thread? Share it with others