Raid Readiness Query - baerrach/msf-googlesheet-tools GitHub Wiki
Create a new tab in your googlesheet.
Enter the 5 character names that make up the team in columns A-E and put the team name in column F. Column H will join the character names together for use in the query later.
Add a new row for each additional team.
For example, the Spotlight II raids require Alpha Flight and Uncanny Avenger:
| A | B | C | D | E | F | G | H |
|---|---|---|---|---|---|---|---|
| Northstar | Guardian | Wolverine | Sasquatch | Sunfire | Alpha Flight | =JOIN("|", $A1:$E1) | |
| Storm | Hercules | Beast | Falcon | JeanGrey | Uncanny Avenger | =JOIN("|", $A2:$E2) |
In row 7 join all the character names from the H rows:
=JOIN(" | ", H1:H2
This will be used in the query to match all the names from the roster.
Note: Most raid analysis will require 5 teams, that is why we start at row 7 so you can duplicate the tab and make minor modifications instead of starting from scratch each time. You will need to adjust H1:H? based on how many rows you need.
In row 8 we build up the full query to use: ="select B, COUNT(B) where B matches '(" & A7 & ")' group by B"
In row 10 execute the query: =QUERY(Roster!A:W, "select A, B, G, N, O, P, Q, R where B matches '" &A7 & "'")
This will return everyone's roster with the follow data:
| Header Name | Header Column |
|---|---|
| Name | A |
| Character Id | B |
| Gear Tier | G |
| Striker | N |
| Fortifier | O |
| Healer | P |
| Raider | Q |
| Skirmisher | R |
In row 10, in the columns after the query the requirements will be calculated.
In this example:
- Column I is calculating Iso 3.1 requirements (a value of 11 or greater)
- Column J is calculating Iso 3.2 requirements (a value of 12 or greater)
- Column K is calculating Iso 3.3 requirements (a value of 13 or greater)
Unfortunately we have to check each iso type to see if any of them meet the requirements.
| I10 | J10 | K10 |
|---|---|---|
| Iso 3.1 | Iso 3.2 | Iso 3.3 |
| =OR(D11>=11,E11>=11,F11>=11,G11>=11,H11>=11) | =OR(D11>=12,E11>=12,F11>=12,G11>=12,H11>=12) | =OR(D11>=13,E11>=13,F11>=13,G11>=13,H11>=13) |
You will need to copy and paste this formula down the columns for each row in the roster result.
Over to the side the analysis is done.
At O9 enter the team name, and in O10 the query =QUERY($A$10:$J, "Select A, count(I) where I = TRUE and B matches '" & $H1 & "' group by A")
The result will show which people in your alliance have that team and the count of how many characters meet the requirements.
Repeat this for each team that needs analysis a couple of columns over.
You'll want to repeat this again a few columns over, but using the queries:
- Iso 3.2
=QUERY($A$10:$K, "Select A, count(J) where J = TRUE and B matches '" & $H1 & "' group by A") - Iso 3.3
=QUERY($A$10:$K, "Select A, count(K) where K = TRUE and B matches '" & $H1 & "' group by A")
You should be able to take this example and change the character names and requirements check in order to analyse other raid requirements.