Creating a formula to do a teammate count

Hello Midday259. Welcome to the forum.

Try this. It returns what you are expecting in column H.

Formula:

Please Login or Register  to view this content.

Edit: I forgot to mention, before entering that formula above fill in the row headers in column A by:

1. selecting A2:A34
2. hitting the F5 function key (GoTo)
3. clicking the Special... button
4. selecting Blanks
5. hit Enter

All the blank cell will be selected.

Then:

1. type =
2. then the up arrow key
3. then while holding down the Ctrl key hit enter
4. that will fill copy down the missing row headers

VBA offering: matrix replaces columns F:H

Please Login or Register  to view this content.

John's VBA approach is fully automated and may well provide the best performance, which may begin to matter as you add more matches. However, just in case you prefer not to use a VBA approach, here is a "semi-manual" / "semi-automatic" approach to creating all combinations (in the mathematical sense) of two players.

ONE TIME SETUP

1. Create a new blank worksheet. I called it "Comb" for combinations.
2. Create row-1 headers:
   A1: Unique player list
   B1: Player-1
   C1: Player-2
   D1: Unique
3. In G2 (compute necessary rows): =(COUNTA(A:A)-1)^2
4. In G3 (combinations): =COMBIN(COUNTA(A:A)-1, 2)
5. In B2: =INDEX($A$2:$A$30, (INT(ROW()-2)/COUNTA($A$2:$A$30))+1)
6. In C2: =IF(B2=0, 0, INDEX($A$2:$A$30, MOD(ROW()-2, COUNTA($A$2:$A$30))+1))
7. In D2: =IF(B2<C2, "keep", "")

Your "Comb" worksheet (after step-5 below) should now appear as follows with col-B and col-C listing all required combinations of players:

team mate.png

AFTER ADDING MATCHES TO SHEET1

1. Copy Sheet1!C:C to Comb!A:A
2. Select Comb!A:A
3. Ribbon > Data > Remove Duplicates (if there is a warning then select "Continue with current selection." > Select "My data has headers" > OK.
4. With your sample data there should now be 25 unique players in col-A. Necessary rows in G2 should be 625 and Combinations (selecting 2 from the col-A list with order irrelevant) should be 300.
5. Select Comb!B2:D2 and drag down to cover G2 (=625) rows
6. Filter col-D on "keep". With your sample data you should now have 300 rows selected representing all possible combinations of 2 players.
7. With the filter in place, select B2:C625 then copy to Sheet1!F2.

You should now have all 300 combinations in col-F:G of sheet1.

SUMMARY

I included my formula in column-I and, for comparison purposes, Dave's formula (adjusted to not use unique() as I don't have O-365) in column-J.

My formula I2 copied down:

Formula:

Please Login or Register  to view this content.

This formula yields the same results as Dave's. My formula does not require the row headers that Dave requested, but Dave's formula is significantly less complex and I'm sure will perform more efficiently.

My semi-manual method of generation combinations described above will of course work equally well with Dave's approach.

Finally, just a note, my Sheet1 column I formula will require adjustment (beyond extending ranges) when further matches are added. I won't bother pursuing that unless there is further interest.

The attached workbook includes all of the above.

You may want to check back in from time to time.

I haven't given up on this one yet.

Although even if this can be done by formula I suspect John's VBA solution is going to be your best bet.

If you do intend to pursue my approach then obviously change all the 34's in formulas to exactly cover the additional matches. The {1,2,3} arrays needs to be extended to exactly equal the number of matches (there's more elegant ways to do this if you will have a large number of matches (maybe like row(1:50))).

Anyway, thanks for the feedback and rep and good luck whichever approach you use!