Como identificar combinações duplicadas de dois valores em ordem aleatória

Question

Estou tentando identificar as segundas combinações duplicadas de células em duas (ou mais) colunas. Para complicar, a ordem das combinações varia.

Meus dados são parecidos com isto:

aaa   111
222   aaa
111   aaa
111   bbb
bbb   111

Estou à procura de uma fórmula que, para este exemplo, diga-me que as linhas 1, 2 e 4 são as primeiras instâncias dos valores combinados, enquanto as linhas 3 e 5 são as segundas instâncias.

    

Answer 1

Isso não é difícil de fazer com funções / fórmulas se você está lidando com apenas duas colunas. Você precisará de três "colunas auxiliares" além de uma coluna de resultados, e você precisará identificar uma string que nunca aparece nos seus dados. Vamos usar as colunas E a H e a string / :

• E1 - =IF(A1<B1, A1, B1)
• F1 - =IF(A1<B1, B1, A1)
• G1 - =E1 & "/" & F1
• H1 - =COUNTIF(G$1:G1, G1)

E1 é o menor (mínimo) de A1 e B1 , e F1 é o maior de A1 e B1 . Em outras palavras, E1 e F1 contêm os valores de A1 e B1 , mas classificado em ordem crescente. Em seguida, G1 é E1 concatenado com F1 , delimitado com / . Essa é uma combinação exclusiva, independente de pedido, dos valores A1 e B1 . Por exemplo, G1 e G3 contêm 111/aaa porque as linhas 1 e 3 têm 111 e aaa nas Colunas A e B em alguma ordem. Então, a coluna H conta quantas vezes essa combinação apareceu até agora.

Observe que H3 e H5 cada um contém 2 , porque as linhas 3 e 5 são aquelas que têm uma combinação pela segunda vez. Quando eu repeti os dados da linha 1 na linha 6, recebi H6 = 3 , porque essa foi a terceira vez que a combinação de 111 e aaa apareceu.

Contanto que os dados possam ser numéricos ou textuais, isso fica muito complicado muito rápido se você quiser fazer mais de duas colunas. Há uma maneira de tornar mais fácil se os dados forem todos os números, no entanto. Ou, se você estiver disposto a estipular um tamanho máximo para seus dados (por exemplo, nada mais do que três caracteres), pode ser gerenciável.