Como obter a contagem das colunas correspondentes mais frequentes?

Question

Precisa de ajuda para descobrir como fazer isso no Excel.

Eu tenho uma longa lista de nomes divididos em duas colunas - o primeiro e o último nome. Eu gostaria de descobrir quais são os 5 nomes completos mais populares , mas - Alguns sobrenomes e nomes são invertidos , e então eu gostaria que "John | Smith" também contasse para "Smith | John". Como posso fazer isso no excel?

Eu tentei colocar ambas as colunas em uma tabela dinâmica e colocar o Nome e Sobrenome nos rótulos de linha e a contagem do Nome nos valores, mas isso me fornece uma longa lista de todos os nomes misturados com todos os outros nomes. e a única maneira de descobrir o nome mais popular é percorrer a tabela inteira e contar o número mais alto que eu vejo. Quando eu adiciono um filtro de valor "top 10" aos resultados, os valores principais são retornados pelo primeiro nome que possui a frequência mais alta, independentemente de qual seja o sobrenome.

Por exemplo. Eu posso ter 5 nomes:

John Smith
Smith John
Frank White
Frank Green
Frank Black

E se eu aplicasse um filtro top 10, me mostraria que Frank era o nome mais comum, seguido por John Smith, embora o que eu realmente queira ver seja John Smith, já que estava lá mais do que qualquer outro nome.

Espero que isso faça sentido. Se alguém pudesse fornecer algumas dicas sobre como eu poderia conseguir isso, eu ficaria muito agradecido. Por favor, deixe-me saber se você tem alguma dúvida, obrigado!

EDIT: Sobre como determinar o que é um primeiro nome e o que é um sobrenome, a ordem dos nomes não importa realmente. O único problema que eu prevejo com isso é que haverá uma duplicata dos nomes mais freqüentes, então, no exemplo que dei, "Smith John" e "John Smith" são os nomes mais populares, com 2 cada. (Se isso faz sentido.) Eu estou bem com isso, porque é muito fácil olhar para uma lista dos 10 melhores e descontar todos os outros resultados.

    

Answer 1

editar: Após três edições da pergunta, isso pode funcionar melhor:

Suponha que as colunas A e B mantenham as partes do nome. Coloque isso na coluna C:

=A1&" "&B1

Em seguida, insira esta fórmula em uma coluna livre:

=(COUNTIF(C$1:C$7;$A1 & " " & $B1) + COUNTIF(C$1:C$7;$B1 & " " & $A1)) * NOT((COUNTIF(C$1:C1;$B1 & " " & $A1) > 0))

Isso conta os pares de nomes independentemente da ordem e determina se o par invertido foi visto antes (observe o índice final em execução no terceiro COUNTIF). Se sim, a contagem será definida para zero. Desta forma, apenas pares únicos contarão.

Use um filtro Top10 nesta coluna para encontrar os mais frequentes.

    

Answer 2

Você pode executar esse código para encontrar todas as correspondências e torná-las iguais, seja first,last ou last,first

Sub test()

Dim LastRow As Integer
LastRow = Cells(Rows.Count, "A").End(xlUp).Row

Dim fname As String
Dim lname As String

For i = 1 To LastRow
    fname = LCase(Cells(i, 1))
    lname = LCase(Cells(i, 2))
        For j = 1 To LastRow
            If LCase(Cells(j, 1)) = lname And LCase(Cells(j, 2)) = fname Then
                Cells(j, 1) = fname
                Cells(j, 2) = lname
            End If
        Next
Next

End Sub

Em seguida, use esses dados para sua tabela dinâmica.