Excel 2016: Populate column with list based on matching criteria from another sheet

Question

I tried to phrase the title as best I could and I'm going to do my best to explain. I'll take any suggestions on re-naming the title, if needed.

I have two sheets that I'm trying to use for tracking rounds played by players in a sport. They're essentially the same data, just presented differently.

In one sheet, I have 12 rows, each row representing a player, with the player's name in Column B. 

For each row, there are 11 columns, each representing each round of a game.

e.g.,

================================================
|  A  |    B    |    C    |   ...   |    M     |
================================================
| No. | Player  | Round 1 |   ...   | Round 11 |
------------------------------------------------
|  1  | Male 1  |    I    |         |    I     |
|  2  | Male 2  |    I    |         |    I     |
|  3  | Male 3  |    I    |    I    |          |
|  4  | Male 4  |    I    |    I    |          |
|  5  | Male 5  |    I    |    I    |    I     |
|  6  | Male 6  |         |    I    |    I     |
|  7  | Male 7  |         |    I    |    I     |
|  8  | Female1 |    I    |         |          |
|  9  | Female2 |    I    |    I    |          |
|  10 | Female3 |    I    |    I    |    I     | 
|  11 | Female4 |         |    I    |    I     |  
|  12 | Female5 |         |         |    I     |

Only 5 male players can play in a round. Only 3 female players can play in a round.

So if the player is in, I enter an "I" in the column for the applicable round, and empty if they are not playing that round.

In the other sheet, I have rows that represent the players that are "IN" and the players that are "OUT". I then have columns that represent the round of the game.

In these cells, however, I'm currently manually entering (copy/paste) the names of the players who are in and/or out for that round.

================================================
|  A  |    B    |    C    |   ...   |    M     |
================================================
| No. | Status  | Round 1 | Round 2 | Round 3 |
|-----------------------------------------------
| 1   |   IN    | Male 1  | Male 3  | Male 1  |
| 2   |   IN    | Male 2  | Male 4  | Male 2  |
| 3   |   IN    | Male 3  | Male 5  | Male 6  |
| 4   |   IN    | Male 4  | Male 6  | Male 7  |
| 5   |   IN    | Male 5  | Male 7  | Male 7  |
| 6   |   IN    | Female1 | Female2 | Female3 |
| 7   |   IN    | Female2 | Female3 | Female4 |
| 8   |   IN    | Female3 | Female4 | Female5 |
| 9   |   OUT   | Male 6  | Male 1  | Male 3  |
| 10  |   OUT   | Male 7  | Male 2  | Male 4  |
| 11  |   OUT   | Female4 | Female1 | Female1 |
| 12  |   OUT   | Female5 | Female5 | Female2 |

How can I populate the players' names auto-magically for the columns in the second sheet based on the row and status from the first sheet?

EDIT:

Updated my question to provide more precise details on the data I'm using.

EDIT:

Added column names to the examples.

Answer 1

I was able to do it with two “helper matrices”.  A “helper cell” is a cell that derives/computes some intermediate value from the input data.  This intermediate value is then used in another cell(s) to produce the desired result.  It’s common to have a “helper column” in a sheet of tabular data.  I look forward to seeing whether somebody can streamline my answer and make it more elegant, but this is the best I was able to come up with in 59 minutes.

It’s possible to put helpers on the same sheet as the actual data, in different column(s) or row(s), typically hidden.  Or you can put them on separate sheets.  Since this answer calls for two of them, I’ll illustrate both techniques; you can then choose to use one technique consistently.

I’ll assume that your source data (presented in your question) are in Sheet1!A2:D8, with the headers in Row 1.  Put

=IF(B2="", "blank", B2)

into cell AB2, and drag/fill down to AB8 and to the right to Column AD.  This results in

   |    AA    |    AB    |    AC    |    AD    |
---+----------+----------+----------+----------+
 1 |          |          |          |          |
 2 |          |   I      |   blank  |   I      |
 3 |          |   I      |   blank  |   I      |
 4 |          |   I      |   I      |   blank  |
 5 |          |   I      |   I      |   blank  |
 6 |          |   I      |   I      |   I      |
 7 |          |   blank  |   I      |   I      |
 8 |          |   blank  |   I      |   I      |

I trust no explanation is necessary.

Now it gets more complicated.  Create Sheet3 and enter the following formulas:

• B2 → =MATCH("I", Sheet1!AB$2:AB$8, 0)
• B3 → =MATCH("I", OFFSET(Sheet1!AB$2,B2,0):Sheet1!AB$8, 0) + B2
• B7 → =MATCH("blank", Sheet1!AB$2:AB$8, 0)
• B8 → =MATCH("blank", OFFSET(Sheet1!AB$2,B7,0):Sheet1!AB$8, 0) + B7

Drag/fill cell B3 down to B6. If you ever change your scheme so you have more than two “out” players at a time, you will need to drag/fill cell B8 down appropriately.  And, as before, drag/fill Column B to the right to Column D.  You should get:

   |    A    |    B    |    C    |    D    |
---+---------+---------+---------+---------+
 1 |         |         |         |         |
 2 |         |      1  |      3  |      1  |
 3 |         |      2  |      4  |      2  |
 4 |         |      3  |      5  |      5  |
 5 |         |      4  |      6  |      6  |
 6 |         |      5  |      7  |      7  |
 7 |         |      6  |      1  |      3  |
 8 |         |      7  |      2  |      4  |

Explanation:

• B2 contains the (relative) location of the first I in the range Sheet1!AB$2:AB$8.  This is 1, because Sheet1!AB2 contains I, and it is the first cell in that range.  Similarly, C2 and D2 contain 3 and 1, respectively, because Sheet1!AC4 and Sheet1!AD2 (the third and first cells, corresponding to Player3 and Player1, respectively), are the locations of the first I in those columns.

• Looking at B3 — OFFSET(Sheet1!AB$2,B2,0) is OFFSET(Sheet1!AB$2,1,0), which is equivalent to Sheet1!AB3.  So this MATCH is looking at the range Sheet1!AB3:AB8.  This MATCH will also return 1, because Sheet1!AB3 contains I, and it is the first cell in that range.  Then we add B2 (1) to that, getting 2, which is the location of the second I in Sheet1!AB2:AB8.

• Similarly, the MATCH in cell D4 is looking at the range Sheet1!AD4:AB8.  This MATCH will return 3, because Sheet1!AD6 is the first cell in that range that contains I.  Adding D3 (2) gives us 5, because that row corresponds to Player5.

• Then we do the same thing in Rows 7 and 8, only looking for blank.

  I wouldn’t have needed the Sheet1!AA:AD helper matrix, except, apparently, you can’t use MATCH("", …) to find a blank cell.

Now we can just coast downhill and put =INDEX(Sheet1!$A$2:$A$8, Sheet3!B2) into Sheet2!B2.

+--------+---------+---------+---------+
| Status | Round 1 | Round 2 | Round 3 |
+--------+---------+---------+---------+
| IN     | Player1 | Player3 | Player1 |
| IN     | Player2 | Player4 | Player2 |
| IN     | Player3 | Player5 | Player5 |
| IN     | Player4 | Player6 | Player6 |
| IN     | Player5 | Player7 | Player7 |
| OUT    | Player6 | Player1 | Player3 |
| OUT    | Player7 | Player2 | Player4 |

Answer 2

This VBA code will do the trick

Public Sub players_rounds()
    first_sheet = "Sheet1"
    second_sheet = "Sheet2"
    Dim wkb As Workbook
    Dim wk1 As Worksheet
    Dim wk2 As Worksheet
    Set wkb = ThisWorkbook
    Set wk1 = wkb.Worksheets(first_sheet)
    Set wk2 = wkb.Worksheets(second_sheet)
    wk1_lastColumn = wk1.Cells(1, Columns.Count).End(xlToLeft).Column
    wk1_lastRow = wk1.Cells(Rows.Count, 1).End(xlUp).Row
    count_in = 0
    For i = 2 To wk1_lastRow
        If wk1.Cells(i, 2) = "I" Then count_in = count_in + 1
    Next i
    wk2.Cells.Clear
    wk2.Rows(1).value = wk1.Rows(1).value
    wk2.Cells(1, 1).value = "Status"
    count_out = wk1_lastRow - count_in - 1

    For i = 2 To count_in + count_out + 1
        If i <= count_in + 1 Then
            wk2.Cells(i, 1).value = "IN"
        Else
            wk2.Cells(i, 1).value = "OUT"
        End If
    Next i

    For i = 2 To wk1_lastRow
        thisplayer = wk1.Cells(i, 1)
        For j = 2 To wk1_lastColumn
            playervalue = wk1.Cells(i, j)
            playerround = wk1.Cells(1, j)
            If playervalue = "I" Then
                firstrow = 2
                lastrow = count_in + 1
            Else
                firstrow = count_in + 2
                lastrow = count_in + count_out + 1
            End If
            For k = 2 To wk1_lastColumn
                If wk2.Cells(1, k) = playerround Then
                    For m = firstrow To lastrow
                        If wk2.Cells(m, k) = "" Then
                            wk2.Cells(m, k) = thisplayer
                            m = lastrow
                            k = wk1_lastColumn
                        End If
                    Next m
                End If
            Next k
        Next j
    Next i
End Sub

Open VBA / Macros with Alt+ F11, click on ThisWorkbook right-click and insert a module under it. Paste the code on the right side.

Check that the values of the variables first_sheet and second_sheet match with the names of your worksheets.

Whenever you execute this macro it will update the second sheet according to the values on first sheet.

To make it even more automagical double in Macros, double click the first sheet, in the right side select Worksheet and Change.

Make it look like this:

Private Sub Worksheet_Change(ByVal Target As Range)
    Call players_rounds
End Sub

Now, every time you change something in the first sheet, the second one updates.