2

I am using Excel to track a team game where players are divided into teams and subteams within teams. Each player within a subteam scores a certain number of points, and I would like to have a summary string for each player with the number of points other players in the same subteam scored.

Example:

A         B       C          D
PLAYER    TEAM    SUBTEAM    POINTS
Alice     Red     1          70
Bob       Red     1          20
Charlie   Red     1          10
Dave      Red     2          70
Erin      Red     2          30
Frank     Blue    1          55
Grace     Blue    1          45

My desired output looks like this:

A         B       C          D        E
PLAYER    TEAM    SUBTEAM    POINTS   SUMMARY
Alice     Red     1          70       Bob:20, Charlie:10
Bob       Red     1          20       Alice:70, Charlie:10
Charlie   Red     1          10       Alice:70, Bob:20
Dave      Red     2          70       Erin:30
Erin      Red     2          30       Dave:70
Frank     Blue    1          55       Grace:45
Grace     Blue    1          45       Frank:55

The furthest I was able to go is a combination of CONCATENATE, INDEX, and MATCH in an array formula:

{=CONCATENATE(INDEX($A$2:$A$8,MATCH(1,(C2=$C$2:$C$8)*(B2=$B$2:$B$8),0)), ":", INDEX($D$2:$D$8,MATCH(1,(C2=$C$2:$C$8)*(B2=$B$2:$B$8),0)))}

This unfortunately just outputs a summary for the first player in the subteam:

A         B       C          D        E
PLAYER    TEAM    SUBTEAM    POINTS   SUMMARY
Alice     Red     1          70       Alice:70
Bob       Red     1          20       Alice:70
Charlie   Red     1          10       Alice:70
Dave      Red     2          70       Dave:70
Erin      Red     2          30       Dave:70
Frank     Blue    1          55       Grace:45
Grace     Blue    1          45       Grace:45

What I need to do now is:

  1. Excluding the player for the summary (I don't want Alice in the summary for Alice, but only Bob and Charlie)
  2. Getting it to work for multiple matches (there can be an arbitrary number of players in each subteam)
  3. Getting CONCATENATE to work with an unknown number of strings (because as said above, there can be an arbitrary number of players in each subteam).

Ideas appreciated!

st1led
  • 385
  • 2
  • 4
  • 18
  • 1
    CONCATENATE will not return an array. You will need the Excel 2016 functions CONCAT or TEXTJOIN, though I believe these also require an Office 365 subscription. Failing that, you will require VBA. – XOR LX Sep 05 '18 at 10:39

2 Answers2

2

I put together a helper column that concatenates each player/points and the TEXTJOINIFS from TEXTJOIN for xl2010/xl2013 with criteria for the desired results.

enter image description here

  • Spot on! Is there any way to specifically order the joined strings by a column (e.g. column D) other than by alphabetic order? – st1led Sep 05 '18 at 12:27
  • 1
    To order by points (i.e. column D) you could reverse the helper column; e.g. 70:Alice instead of Alice:70. –  Sep 05 '18 at 12:32
2

Unfortunately Excel (prior to Excel 2016) cannot conveniently join text. The best you can do (if you want to avoid VBA) is to use some helper cells and split this "Summary" into separate cells.

See example below. The array formula in cell E4 is dragged to cell J10.

= IFERROR(INDEX($A$4:$D$10,MATCH(SMALL(IF(($B$4:$B$10=$B4)*($C$4:$C$10=$C4)*($A$4:$A$10<>$A4),
  ROW($A$4:$A$10)),E$3),ROW($A$4:$A$10),0),MATCH(E$2,$A$1:$D$1,0)),"")

Note this is an array formula, so you must press Ctrl+Shift+Enter instead of just Enter after typing this formula.

enter image description here

Of course, in this example I assume 3 players. Your requirement of arbitrary amount of players cannot be met with formulas alone, but you can just extend the "Summary" section over to the right as far as necessary.

If you really wanted to, you could even concatenate the "Summary" rows to form a single cell, e.g. something like:

= CONCATENATE(E4,": ",F4,", ",...)
ImaginaryHuman072889
  • 4,953
  • 7
  • 19
  • 51