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

[JakkeJakobsen]

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

Role

Rank

Activity

Specs

Readiness

Public Note

# of signups

% of expected

% of max

MS

OS

Farm

Progression

ilvl

Any given reason for readiness

(50% of all raids)

(of all raids)

To Paragons

Garrosh & HC

MS

Tanks

Rank

# of signups

% of expected

% of max

MS

OS

Farm

Progression

ilvl

Public Note

Name1

Member

27

135.0%

84.4%

Blood

Frost

Ready

Ready

573

Name2

Member

25

125.0%

78.1%

Blood

Unholy

Ready

Ready

571

Name3

Member

12

60.0%

37.5%

Protection

Retribution

Ready

Ready

570

Name4

Trialist

10

50.0%

31.3%

Blood

Frost

Ready

Not Ready

555

AoE aggro issues

Name5

Member

6

30.0%

18.8%

Guardian

Guardian

Ready

Ready

571

Name6

Member

2

10.0%

6.3%

Blood

Frost

Ready

Not Ready

537

Low ilvl

Name7

Officer

1

5.0%

3.1%

Blood

Frost

Ready

Ready

562

<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.

Name	Effect	Deathknight
Buffs		Blood	Frost & Unholy
Attack Power	10% AP	Yes	Yes
Critical Strike	5% Crit Chance	No	No
Haste	5% Haste	No	Yes
Heroism	30% Haste	No	No
Mastery	458 Mastery	Yes	No
Multistrike	5% Multistrike	No	No
Spell Power	10% SP	No	No
Stamina	10% Stamina	No	No
Stat Multiplier	5% str, agi, int	No	No
Versatility	3% Versatility	No	Yes
Debuff
Mortal Wounds		No	No

<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 GOAL	BossX
		RAIDLEADER	Name2
		ASSISTANT	Name4
		MASTERLOOTER	Name6
		SORTED BY	Name2, Name3
TANK			Name1
TANK			Name4
HEALER			Name8
HEALER			Name14
HEALER			Name17
RANGED			Name20
RANGED			Name13
MELEE			Name33
MELEE			Name34
SUPPORT			Name29

<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.

 

[JakkeJakobsen]

Bumping

 

[JakkeJakobsen]

No one got any idea how to nest functions to get this work? Or whatever method can be used here?

 

[JakkeJakobsen]

I will have to continue bumping this