Excel 2016: preencher coluna com lista baseada em critérios correspondentes de outra planilha

Question

Eu tentei colocar o título da melhor maneira possível e vou fazer o meu melhor para explicar. Vou dar sugestões para renomear o título, se necessário.

Eu tenho duas planilhas que estou tentando usar para acompanhar rodadas jogadas por jogadores em um esporte. Eles são essencialmente os mesmos dados, apenas apresentados de forma diferente.

Em uma folha, tenho 12 linhas, cada linha representando um jogador, com o nome do jogador na coluna B.

Para cada linha, existem 11 colunas, cada uma representando cada rodada de um jogo.

por exemplo,

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

Apenas 5 masculinos jogadores podem jogar em uma rodada. Apenas 3 jogadores feminino podem jogar em uma rodada.

Então, se o jogador estiver dentro, eu insiro um "Eu" na coluna para a rodada aplicável e esvazio se ele não estiver jogando nessa rodada.

Na outra folha, tenho linhas que representam os jogadores que estão "IN" e os jogadores que estão "OUT". Eu então tenho colunas que representam a rodada do jogo.

Nessas células, no entanto, estou inserindo manualmente (copie / cole) os nomes dos jogadores que estão dentro e / ou fora dessa rodada.

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

Como posso preencher os nomes dos jogadores de forma automática para as colunas na segunda folha com base na linha e no status da primeira folha?

EDITAR:

Atualizei minha pergunta para fornecer detalhes mais precisos sobre os dados que estou usando.

EDITAR:

Adicionado nomes de coluna aos exemplos.

    

Answer 1

Consegui fazer isso com duas "matrizes auxiliares". Uma “célula auxiliar” é uma célula que deriva / calcula algum valor intermediário dos dados de entrada. Este valor intermediário é então usado em outra (s) célula (s) para produzir o resultado desejado. É comum ter uma "coluna auxiliar" em uma planilha de dados tabulares. Estou ansioso para ver se alguém pode agilizar minha resposta e torná-la mais elegante mas isso é o melhor que consegui em 59 minutos.

É possível colocar ajudantes na mesma planilha que os dados reais. em coluna (s) ou linha (s) diferente (s), normalmente ocultas. Ou você pode colocá-los em folhas separadas. Como essa resposta chama dois deles, ilustrarei as duas técnicas; você pode então escolher usar uma técnica de forma consistente.

Suponho que seus dados de origem (apresentados na sua pergunta) estão em Sheet1!A2:D8 , com os cabeçalhos na Linha 1. Coloque

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

na célula AB2 e arraste / preencha para AB8 e à direita para Coluna AD . Isso resulta em

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

Eu confio que nenhuma explicação é necessária.

Agora fica mais complicado. Crie Sheet3 e insira as seguintes fórmulas:

• 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

Arraste / preencha a célula B3 para B6 . Se você mudar o seu esquema para ter mais de dois jogadores de cada vez, você precisará arrastar / preencher adequadamente a célula B8 . E, como antes, arraste / preencha a coluna B para a direita para Coluna D . Você deve receber:

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

Explicação:

• B2 contém a localização (relativa) do primeiro I no intervalo Sheet1!AB$2:AB$8 . Isso é 1 , porque Sheet1!AB2 contém I , e é a primeira célula nesse intervalo. Da mesma forma, C2 e D2 contêm 3 e 1 , respectivamente, porque Sheet1!AC4 e Sheet1!AD2 (a terceira e primeira células, correspondendo a Player3 e Player1, respectivamente), são os locais do primeiro I nessas colunas.
• Olhando para B3 - OFFSET(Sheet1!AB$2,B2,0) is OFFSET(Sheet1!AB$2,1,0) , que é equivalente a Sheet1!AB3 . Então, esse MATCH está olhando para o intervalo Sheet1!AB3:AB8 . Esse MATCH também retornará 1 , porque Sheet1!AB3 contém I e é a primeira célula no intervalo que . Em seguida, adicionamos B2 ( 1 ) a isso, recebendo 2 , que é a localização do segundo I em Sheet1!AB2:AB8 .
• Da mesma forma, o MATCH na célula D4 está a olhar para o intervalo Sheet1!AD4:AB8 . Este MATCH retornará 3 , porque Sheet1!AD6 é a primeira célula no intervalo que que contém I . Adicionar D3 ( 2 ) nos dá 5 , porque essa linha corresponde a Player5.

• Em seguida, fazemos o mesmo nas linhas 7 e 8, procurando apenas blank .

  Eu não precisaria da matriz de ajuda Sheet1!AA:AD , exceto que, aparentemente, você não pode usar MATCH("", …) para encontrar uma célula em branco.

Agora podemos apenas descer a costa e coloque =INDEX(Sheet1!$A$2:$A$8, Sheet3!B2) em 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

Este código VBA fará o truque

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

Abra VBA / Macros com Alt + F11 , clique em ThisWorkbook clique com o botão direito e insira um módulo sob ele. Cole o código no lado direito.

Verifique se os valores das variáveis first_sheet e second_sheet correspondem aos nomes das suas planilhas.

Sempre que você executar esta macro, ela atualizará a segunda planilha de acordo com os valores da primeira planilha.

Para torná-lo ainda mais automagical em Macros, clique duas vezes na primeira folha, no lado direito, selecione Worksheet e Change .

Faça com que pareça:

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

Agora, toda vez que você alterar alguma coisa na primeira folha, a segunda é atualizada.