Searching for a name, then matching a cell against another cell (or range), then counting how many

JakkeJakobsen

Board Regular
Joined
Sep 10, 2014
Messages
89
This will be hard to explain, so I will copy in parts of my sheet for you to see. Yes, this is for a game! :P

RoleRankActivitySpecsReadinessPublic Note
# of signups% of expected% of maxMSOSFarmProgressionilvlAny given reason for readiness
(50% of all raids)(of all raids)To ParagonsGarrosh & HCMS
TanksRank# of signups% of expected% of maxMSOSFarmProgressionilvlPublic Note
Name1Member27135.0%84.4%BloodFrostReadyReady573
Name2Member25125.0%78.1%BloodUnholyReadyReady571
Name3Member1260.0%37.5%ProtectionRetributionReadyReady570
Name4Trialist1050.0%31.3%BloodFrostReadyNot Ready555AoE aggro issues
Name5Member630.0%18.8%GuardianGuardianReadyReady571
Name6Member210.0%6.3%BloodFrostReadyNot Ready537Low ilvl
Name7Officer15.0%3.1%BloodFrostReadyReady562

<tbody>
</tbody>

This is a part of "Sheet1", which also got 4 similar ranges of names (here under "Tanks", call it A4 and so on). In "Sheet2", I got a list of things each of these specializations can do (which us under "MS" in the "Specs" part). This is only for "Death Knight", which is spelled wrong here, will be fixed ofc. But Blood, Frost and Unholy will basicly be split totally into three different parts, so will all the other parts be for other character classes.
NameEffectDeathknight
BuffsBloodFrost & Unholy
Attack Power10% APYesYes
Critical Strike5% Crit ChanceNoNo
Haste5% HasteNoYes
Heroism30% HasteNoNo
Mastery458 MasteryYesNo
Multistrike5% MultistrikeNoNo
Spell Power10% SPNoNo
Stamina10% StaminaNoNo
Stat Multiplier5% str, agi, intNoNo
Versatility3% VersatilityNoYes
Debuff
Mortal WoundsNoNo

<colgroup><col style="width: 194px"><col width="137"><col width="140"><col width="134"></colgroup><tbody>
</tbody>

Sheet3, however, well a part of it is seen below:
RAID GOALBossX
RAIDLEADERName2
ASSISTANTName4
MASTERLOOTERName6
SORTED BYName2, Name3
TANKName1
TANKName4
HEALERName8
HEALERName14
HEALERName17
RANGEDName20
RANGEDName13
MELEEName33
MELEEName34
SUPPORTName29

<tbody>
</tbody>

Now, Name1 and Name3 is from the range you can see in the sample of "Sheet1" at the top. Below the last row here in "Sheet3", there will be a counter. It will count how many of each "thing" we have from "Sheet2" in total.

In other words, it will check what names in "Sheet3" is a match to "Sheet1". It will then match the name's MS spec in "Sheet1" to what it can do in "Sheet2", and count them, or change the BG color, in "Sheet3".

THis is the best way I can explain it. Match name between 'Sheet3'!A7 and 'Sheet1'A4:A10, then check what spec is in that names' row, then count it in "Sheet3".
This was written before I added the "Sheet2" into this for you to see.

Okay, say that both "Name1" and "Name4" are "Blood", which has a match in "Sheet2" under Death Knight Blood. The "things" as I called them that a Blood Death Knight can do is give Mastery and Attack Power.

What I want is to find out which name in the list on "Sheet3" can do what, and count them. "Sheet3" will have a list of all the buffs ("things") as "Sheet2" has, but with the counters behind instead of Yes/no and soforth.

Hard to wrap my head around it myself, so I hope you can understand what I want, because I barely can.

NB! This is Google Spreadsheets, not Excel, but for the mostpart, all formulas works in Google as well.
 
Last edited:

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

JakkeJakobsen

Board Regular
Joined
Sep 10, 2014
Messages
89
No one got any idea how to nest functions to get this work? Or whatever method can be used here?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,492
Messages
5,601,994
Members
414,490
Latest member
Rip181

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top