Se você pode usar o VBA, você pode obter a solução em um clique de um botão. Esta solução proposta usa principalmente fórmulas com muito pouco suporte a VBA e é uma solução de 2 passos.
Como você está usando a Tabela Dinâmica, facilite a visualização dos seus dados com a troca de linhas & Colunas na Tabela Dinâmica, ou seja, Let Years be rows e Customers be columns.
Salve sua pasta de trabalho como xlsm (pasta de trabalho do Excel habilitado para macro) Na planilha onde sua tabela existe pressione ALT + F11 para abrir o Editor VBA. No menu Inserir, insira um módulo e cole o seguinte código nele. Salve o código e a pasta de trabalho.
Function TEXTJOIN(delimiter As String, ignore_empty As Boolean, ParamArray cell_ar() As Variant)
For Each cellrng In cell_ar
For Each cell In cellrng
If ignore_empty = False Then
result = result & cell & delimiter
Else
If cell <> "" Then
result = result & cell & delimiter
End If
End If
Next cell
Next cellrng
TEXTJOIN = Left(result, Len(result) - Len(delimiter))
End Function
Esta é uma função TEXTJOIN. Isso está disponível na assinatura do Office 365, mas não em outras versões. O código do VBA é herdado do artigo abaixo.
Fonte link
Vocêprecisacriarumatabelasemelhanteparaserpreenchidacom1sendoconvertidaparaOLD&NOVO,comomostradonaimagemacima,começandoalinha#9nesteexemplo.
FórmulanacélulaB9
=IF(ISBLANK(B5),"",IF(ISERROR(MATCH(B5,B4,0)),"NEW","OLD"))
Fórmula na célula B10
=IF(ISBLANK(B6),"",IF(ISERROR(MATCH(B6,B4:B5,0)),"NEW","OLD"))
Fórmula na célula B11
=IF(ISBLANK(B7),"",IF(ISERROR(MATCH(B7,B4:B6,0)),"NEW","OLD"))
Agora arraste a fórmula três ao longo do comprimento da tabela. Seus 1s não são traduzidos para OLD & NOVO como relevante. Este é o Passo 1.
Crie uma tabela de anos abaixo disso e duas colunas OLD & NOVO
Fórmula em B15
=CONCATENATE(COUNTIF(B9:I9,"OLD"),"(",TEXTJOIN(",",TRUE,IF(B9:I9="OLD",$B$3:$I$3,"")),")")
Pressione CTRL + SHIFT + ENTER para criar uma fórmula de matriz. Agora você verá a fórmula envolvida em chaves e os resultados pretendidos para o ano nessa célula, conforme necessário. Arraste esta fórmula até o número desejado de anos em questão.
De forma semelhante, coloque a seguinte fórmula de matriz em C15
=CONCATENATE(COUNTIF(B9:I9,"NEW"),"(",TEXTJOIN(",",TRUE,IF(B9:I9="NEW",$B$3:$I$3,"")),")")
e arraste-o para baixo.
Este é um processo de duas etapas. Para uma versão melhor, pode-se optar por um código VBA completo que pode conseguir isso com um clique de um botão.
Esta solução deve funcionar somente nesta pasta de trabalho devido ao código VBA da função TEXTJOIN criado sob encomenda. No entanto, se você tiver uma assinatura do Office 365, ela deverá funcionar perfeitamente no Excel 2016.
