Board 8 > I need Excel help

(+) Yeah! (+)

I cant find a solution online anywhere.

I have eight teams, each containing different variations of the same 12 people. Some games are 6v6, some are 4v4v4, and some are 3v3v3v3.

I am trying to calculate how many times each player plays on another players team (e.g., John plays with Carl 4 times, Tracy plays with Kim 2 times, etc.).

I color-coded my spreadsheet to indicate which and how many players are on each team, but I cannot figure out a way to translate this into a Pivot Table.

Anyone know?

can you show the data you have now so i can picture how it's set up?

If each team is listed in a row or column then just do =COUNTIFS(range, name1, range, name2)

You can extend this to see how many times 3 or more players are on the same team, just add the range and the next player.
---

Its a 13x8 table. The top row is the name of each game. Each column contains the names of the 12 players, color-coded to show whos on whose team. I dont have a pic.

Well for a start, Excel can't count colours without addons, so you'll need to find another way to denote teams

How do I give the color criterion to indicate whos on whose team?

Oh. Such as what?

or you could with vba but it seems unnecessarily complicated compared to listing them differently

I have been doing it manually so far but its rrally hard when I shuffle around players.

okay, i got a solution, one minute

Thanks friend.

okay, that was actually really difficult and i'm surprised at the amount of time i spent on it.

first, you should set your games up like this:


then, set up a helper column at the end, which concatenates all 6 potential names


you obviously then need to set up this table for your results:


then enter this formula in the first cell and fill across and down:
=COUNTIFS($H:$H,"*"&$M2&"*",$H:$H,"*"&N$1&"*")

where H is the helper column, and your results table starts in column M (the names)

there's probably more elegant ways thinking about it now - the big breakthrough that helped me figure it all out was concatenating the names. i think if you can get the names all into one string a lot of solutions could present themselves.

That's a very clever solution. I probably would've turned immediately to VBA.
---

Whoaaaaa. Okay, awesome! I will test it out improve when I can get back to my computer. Ill probably have questions.

Thanks, friend.

So what does concatenating do?

concatenate just puts a bunch of text strings together - so the cell is "MoonroofSTELNEROxbridgeSBAllen " in that first example. then the formula searches each of those cells for both names.

and as you can see by the formula in the screenshot, i used & instead of the CONCATENATE function - i think CONCATENATE functions weird when you select a range. so the formula there is just =B2&C2&D2&E2&F2&G2

Ingenious! I knew I could rely on you.

So do I change the formula at all as I proceed downward?

the formula that I posted will work when filled down and across as long as you enter it in cell N2

Hopefully no one has a name that's a subset of someone else's, haha. Would need some separating character then.
---

Sorry. So I put it in the first cell or N2 then? Do I put it in every single blank cell within the last screen shot? I cant see which is N2 in your pic.

yeah, that is the first blank cell (where SBAllen meets SBAllen). and yeah tyder, i thought about that. hope that's not the case cause i worked far longer on this than i should have already!

and if you put it in the first cell and fill across and down, the way the formula is set up it will adjust relative cell references as you go

if i'm not the best excel user on the board i have nothing

So I just copy and paste the same formula in each and every cell?

Highlight the whole table area and press ctrl-r and ctrl-d to fill the formula right and down.
---

Niiiiice.

ah yeah sorry - you can also drag the handle at the bottom right of a cell to fill

I cant wait to test this out tomorrow.

I did it :)

Thanks again, friend!

glad i helped!

i love this topic so much. only thing i don't love is that the solution is already present so i don't get to solve it myself!
---

The neat thing too is that as I continue to add more games, all I have to do is swap some names around and the formula automatically updates the count for me.

I created a Cell Highlight rule to show me when people played with other people 0 times or greater than 5 times. I want the teams to be even, but I also want everyone to play with each other at least once (but not too many times either!). So its tricky, hence the need for this chart.

SeabassDebeste posted...

i love this topic so much. only thing i don't love is that the solution is already present so i don't get to solve it myself!

Same. Would definitely gone down the concatenate route like Steiner
---

Moonroof posted...

The neat thing too is that as I continue to add more games, all I have to do is swap some names around and the formula automatically updates the count for me.

i've always found robustness the most important thing in creating an excel solution!

did it work?
---

SuperNiceDog posted...

did it work?

Moonroof posted...

I did it :)

Thanks again, friend!

christ
---

come on man you can't expect people to read posts

Did Stelner and Moonroof just become best friends?
---

They already were.
---