I am trying to work out the ranking of top 3 users at different organisations and have the data presented horizontally for each user so it can be inputted into our email system to personalise emails.
I am able to create a ranking vertically but I am not sure how to get the formula to rank based on organisation and return value across.
Here is what I need to have in the end:
Name Organisation Usage First Second Third
User 1 Organisation 1 8 User 3 User 5 User 2
User 2 Organisation 1 10 User 3 User 5 User 2
User 3 Organisation 1 222 User 3 User 5 User 2
User 4 Organisation 1 1 User 3 User 5 User 2
User 5 Organisation 1 14 User 3 User 5 User 2
User 1 Organisation 2 215 User 4 User 1 User 5
User 2 Organisation 2 18 User 4 User 1 User 5
User 3 Organisation 2 12 User 4 User 1 User 5
User 4 Organisation 2 310 User 4 User 1 User 5
User 5 Organisation 2 161 User 4 User 1 User 5
I can return a ranking vertically one organisation at a time using
=INDEX($A$2:$A$6,MATCH(1,INDEX(($C$2:$C$6=LARGE($C$2:$C$6,ROWS(H$1:H1)))*(COUNTIF(H$1:H1,$A$2:$A$6)=0),),0))
If someone could help me run this formula based on each organisation and horizontally that would be fantastic!
Thanks,
Sarah.
Assuming your data starts in A1 Like so:
A B C D E F
---------------------------------------------------------
1 | Name Organisation Usage First Second Third
2 | User 1 Organisation 1 8 User 3 User 5 User 2
3 | User 2 Organisation 1 10 User 3 User 5 User 2
4 | User 3 Organisation 1 222 User 3 User 5 User 2
5 | User 4 Organisation 1 1 User 3 User 5 User 2
6 | User 5 Organisation 1 14 User 3 User 5 User 2
7 | User 1 Organisation 2 215 User 4 User 1 User 5
8 | User 2 Organisation 2 18 User 4 User 1 User 5
9 | User 3 Organisation 2 12 User 4 User 1 User 5
10| User 4 Organisation 2 310 User 4 User 1 User 5
11| User 5 Organisation 2 161 User 4 User 1 User 5
You can change your formula starting in D2 to:
=INDEX($A$2:$A$11,MATCH(1,INDEX(($C$2:$C$11=LARGE(($B$2:$B$11=$B2)*$C$2:$C$11,COLUMNS($C2:C2)))*(COUNTIF($C2:C2,$A$2:$A$11)=0),),0))
What I changed:
($B$2:$B$11=$B2) inside the LARGE which multiplies all the Usage values for other organizations by 0. Which then won't be picked up by the LARGE function.ROWS(H$1:H1) to COLUMNS($C2:C2) so you can rank horizontallyIf the Usage is empty (for all users in the same organization) and you desire the First, Second, and Third column to be blank then also, like so:
A B C D E F
---------------------------------------------------------
1 | Name Organisation Usage First Second Third
2 | User 1 Organisation 1 8 User 3 User 5 User 2
3 | User 2 Organisation 1 10 User 3 User 5 User 2
4 | User 3 Organisation 1 222 User 3 User 5 User 2
5 | User 4 Organisation 1 1 User 3 User 5 User 2
6 | User 5 Organisation 1 14 User 3 User 5 User 2
7 | User 1 Organisation 2
8 | User 2 Organisation 2
9 | User 3 Organisation 2
10| User 4 Organisation 2
11| User 5 Organisation 2
We can accomplish this by checking if the entire Usage for the Organization is 0. Then we can blank out all the ranks for that Organization.
To check if the sum of the usages for the organization is 0 we can use SUMPRODUCT: So for cell D2 that would look like:
=SUMPRODUCT(($C$2:$C$11)*($B$2:$B$11=$B2))=0
Then we can just wrap an IF around everything and blank it if the above statement returns true. So our final formula would look like:
=IF(SUMPRODUCT(($C$2:$C$11)*($B$2:$B$11=$B2))=0,"",INDEX($A$2:$A$11,MATCH(1,INDEX(($C$2:$C$11=LARGE(($B$2:$B$11=$B2)*$C$2:$C$11,COLUMNS($C2:C2)))*(COUNTIF($C2:C2,$A$2:$A$11)=0),),0)))
^^ Throw whatever you want in there
Now if you want the text to say anything else, just put that text inside the quotes for the TRUE condition of the IF statement.