Reconstruir dados em colunas com linhas mescladas

Question

Eu tenho os seguintes dados

patient-id  last name   first name  date of birth   value   date of measurement
A1          A           BC          1900-01-01      1       1900-01-03 0:00
A1          A           BC          1900-01-01      2       1900-01-04 0:00
A1          A           BC          1900-01-01      3       1900-01-03 0:00
A1          A           BC          1900-01-01      2       1900-01-02 0:00
A1          A           BC          1900-01-01      1       1900-01-05 0:00
D5          D           EF          1900-01-02      4       1900-01-04 0:00
D5          D           EF          1900-01-02      5       1900-01-03 0:00
D5          D           EF          1900-01-02      4       1900-01-02 0:00
F2          G           HI          1900-01-03      6       1900-01-04 0:00
F2          G           HI          1900-01-03      5       1900-01-01 0:00
F2          G           HI          1900-01-03      6       1900-01-05 0:00

Cada paciente tem no máximo 10 linhas. Quero mesclá-lo em uma única linha por paciente.

Por exemplo, os dados acima tornam-se:

patient-id  last name   first name  date of birth   value1  date of measurement1    value2  date of measurement2    value3  date of measurement3    value4  date of measurement4    value5  date of measurement5    value6  date of measurement6    value7  date of measurement7    value8  date of measurement8    value9  date of measurement9    value10 date of measurement10
A1          A           BC          1900-01-01      1       1900-01-03 0:00 2   1900-01-04 0:00 3   1900-01-03 0:00 2   1900-01-02 0:00 1   1900-01-05 0:00                                     
D5          D           EF          1900-01-02      4       1900-01-04 0:00 5   1900-01-03 0:00 4   1900-01-02 0:00                                                     
F2          G           HI          1900-01-03      6       1900-01-04 0:00 5   1900-01-01 0:00 6   1900-01-05 0:00                                                     

Alguém sabe como fazer isso?

Answer 1

Você pode fazer isso com uma coluna auxiliar e uma tabela dinâmica

1. coluna auxiliar: adicione uma nova coluna em sua tabela com fórmula:
   =COUNTIF($A$1:A2,A2)
   Isso atribuirá números para cada data de medição por ID do paciente

2. tabela dinâmica:
   • adicione colunas constantes (que você precisa apenas uma vez) como "linhas"
   • adicione a coluna recém-criada como "colunas"
   • adicione colunas para replicar como "valores"
   • certifique-se de que "resumir campo de valor por" esteja definido como "soma" ou "média" para cada um deles
   • definir o formato numérico correto para datas
   • isso funciona apenas para dados contendo apenas informações numéricas (= sem texto)

Answer 2

Uma solução alternativa, menos elegante que uma Tabela Dinâmica e que talvez exija um pouco mais de trabalho.

Vamos supor que seus dados atuais estejam em uma planilha chamada Old.

1. Em Old, certifique-se de que os dados estejam classificados por ID do paciente
2. Da mesma forma que a solução de Máté, adicione uma coluna auxiliar com a fórmula=COUNTIF($A$1:A2;A2)

Você deve ter algo assim:

---

1. Crie uma nova planilha (por exemplo New)
2. Copie as colunas A a D de OldparaNew
3. Em New, selecione as colunas A a D, clique emDados > Remover duplicatas > Ok
4. Agora você deve ter uma linha exclusiva por paciente (veja a área verde abaixo)

Use a captura de tela abaixo para construir o restante da planilha:

1. A área cinza são os cabeçalhos dinâmicos. Se você copiar/colar 9 vezes à direita, eles serão atualizados automaticamente, de 1 a 10.
2. Na área branca existem 2 fórmulas: uma para buscar o “valor” e outra para buscar a “data da medição”. Você também pode copiá-los/colá-los à direita e eles serão atualizados automaticamente.

Aqui está o SUMIFS:

=SUMIFS(Old!$E:$E,Old!$G:$G,E$2,Old!$A:$A,$A4)              
=SUMIFS(Old!$F:$F,Old!$G:$G,F$2,Old!$A:$A,$A4)              

---

Observação:Isso também funciona apenas com valores numéricos. Para valores não numéricos, únicos para cada medição e paciente, acredito que a única solução seria uma macro VBA.