Excel - Filtre valores com regras especiais para duplicatas

Question

Eu tenho dois locais (quartos) e um monte de IDs que estão em um relacionamento muitos-para-muitos; isto é, cada ID pode ocorrer uma ou mais vezes em um ou ambos os quartos. Aqui está um pequeno exemplo:

ID:       Location
1         Room_1
2         Room_2
3         Room_1
3         Room_2
4         Room_1
4         Room_1

Gostaria de separar os IDs em dois grupos com base na localização. Se um ID aparecer apenas no Room_1, eu quero que ele entre no Grupo 1. Se um ID aparecer apenas em Room_2, eu quero que ele entre no Grupo 2. Se um ID aparecer em mais de uma instância (linha), como 3 e 4 acima, Quero agrupá-lo no Grupo 2 se algum dos IDs for a Sala 2. Se houver várias instâncias, mas elas estão todas na sala 1, então o ID pode ficar no Grupo 1.

Então, no exemplo, eu gostaria que os IDs 1 e 4 estivessem no primeiro grupo, e os IDs 2 e 3 estão no segundo.

A solução ideal seria dividi-las em folhas separadas, mas qualquer tipo de separação seria ótimo.

Aqui está uma amostra de dados maior:

ID:       Location
1         Room_1
2         Room_2
3         Room_1
3         Room_2
4         Room_1
4         Room_1
5         Room_2
6         Room_1
7         Room_2
7         Room_2

Após o agrupamento, deve ficar assim:

Group 1:

ID:       Location
1         Room_1
4         Room_1
4         Room_1
6         Room_1

Group 2:

ID:       Location
2         Room_2
3         Room_1
3         Room_2
5         Room_2
7         Room_2
7         Room_2

Uma maneira talvez mais direta de expressar isso é:

• Cada ID que aparece somente no Room_1 entra no Grupo 1.

• Cada ID que aparece na Room_2 (em absoluto) entra no Grupo 2.

  

Exceto também eu quero manter cada linha distinta da entrada, mesmo aqueles que são idênticos.

    

Answer 1

Acho que para a solução que você deseja, são necessárias 4 etapas:

STEP1 :

• Importe seus dados para uma tabela ( Guia Tabela )
• Inserir uma tabela dinâmica (PT) nas colunas, não apenas os dados atuais, o caminho todo para baixo; isso fará com que o crescimento dinâmico, que pode ser "Refreshed" para o PT no Menu de Opções quando o PT é selecionado. Observe a configuração do PT à direita da imagem.

STEP2 :

• Basta inserir an = em cada campo para copiar cada célula da "Tabela" Tab ".
• Na segunda coluna, insira a fórmula conforme visto em Copiar a partir de em B2: %código%

STEP3 :

• Insira uma nova planilha "Room_1", um PT na "Guia Copiar".
• Anote o filtro para o quarto_1 / localização.
• Veja a Guia Room_1 .

STEP4 :

• Insira uma nova planilha "Room_2", um PT na "Guia Copiar".
• Anote o filtro da sala_2 / localização.
• Veja a Guia Room_2 .

imagem GIF . Clique para ampliar e ver cada camada.

    

Answer 2

Espero que você tenha realmente descrito seu problema com precisão. e você não vai dizer algo como "Oh, nos meus dados reais , tenho mais de dois quartos."

Suponho que você tenha "ID" na coluna A e "Location" na coluna B , e todo o resto está disponível para valores derivados. (Se esse não for o caso, basta enviar para a primeira coluna não usada.) Supondo que você tenha cabeçalhos ("ID" e "Localização") na Linha 1,

• Na célula C2 , insira =A2 & B2
• Na célula D2 , insira =ISERROR(MATCH(A2 & "Room_2", C$2:C$11, 0))
  Substitua 11 pelo número da última linha em que você tem dados.
• Selecione as células C2 e D2 , e arraste / preencha até a última linha onde você tem dados.

& é o operador de concatenação de strings do Excel. Você também pode dizer CONCATENATE(A2, B2) , mas A2 & B2 é obviamente muito mais conciso. Isso gera valores como 1Room_1 , 2Room_2 , 3Room_1 , 3Room_2 ,…, etc. na coluna C . A função MATCH(A2 & "Room_2", C$2:C$11, 0) (na Coluna D ) pesquisa Coluna C para uma ocorrência de o ID dessa linha concatenado com a string Room_2 . Em outras palavras, há uma linha em que este ID está na Sala 2? Se houver um, MATCH retorna um número de índice e ISERROR retorna FALSE. Caso contrário, MATCH retornará um erro e ISERROR retornará TRUE.

Portanto, a coluna D contém TRUE para cada ID que aparece apenas na Sala 1, e FALSE para qualquer ID que apareça (pelo menos uma vez) na Sala 2:

Emseguida,classifiqueemColunaDnaordemmaiorparaamenor(decrescente),paraqueaslinhasVERDADEIRASvenhamantesdaslinhasFALSAS: