Excel estrae oggetti unici dall'elenco

Ho una grande list di aziende che collaborano con la mia azienda. La mia azienda è suddivisa in diversi gruppi di ricerca. Una società potrebbe collaborare con un unico gruppo di ricerca più volte nell'elenco o con più gruppi di ricerca.

Vorrei sapere quali aziende collaborano solo con un singolo gruppo di ricerca. L'esempio dei dati è sotto, puoi vedere che l'azienda A collabora solo con il gruppo 1 ma più volte, ma la società B si collega con molti gruppi. Come posso contare questo?

Dati esempi:

Group Company 1 A 1 B 1 C 1 A 1 C 2 D 2 D 2 E 2 E 2 B 2 D 3 D 3 F 3 B 3 F 4 G 4 B 4 B

Sarebbe un risultato binario, 1 = società è univoco al gruppo, 0 = società non è univoco al gruppo.

Un'estensione a questo (sebbene non inclusa nella mia domanda) sarebbe, quanti gruppi le aziende collaborano in media

C'è una soluzione per ciò che vuoi raggiungere. A seguito di una soluzione, utilizzare un paio di colonne di aiuto e alla fine darà la conseguenza se l'azienda è unica per il gruppo o less e il count delle aziende di gruppo che collaborano.

Assumendo che i dati del Group e della Company siano rispettivamente nella Column A e nella Column B procedere come segue:

Fase 1: Ottieni una combinazione unica di Gruppo e Società

Nella Cell D2 inserire la seguente formula e trascinare / copiare come necessario.

 =IFERROR(INDEX($A$2:$A$19 & "," & $B$2:$B$19,MATCH(0,INDEX(COUNTIF($D$1:D1,$A$2:$A$19 & "," & $B$2:$B$19),0,0),0)),"")

Fase 2: get i conteggi di each combinazione nei dati

Nella Cell E2 inserire la seguente formula e trascinare / copiare fino alla row where Column D visualizza i valori.

 =COUNTIFS($A$2:$A$19,LEFT(D2,(FIND(",",D2,1)-1)),$B$2:$B$19,MID(D2,FIND(",",D2)+1,256))

Questa formula darà il count della presenza di ciascuna combinazione dalla Column D nei tuoi dati. Ad esempio, il gruppo 1 e la società A si verificano due volte nei tuoi dati, il gruppo 2 e la società D si verificano 3 volte nei tuoi dati e così via.

Fase 3: get l'elenco delle aziende uniche dalla Column B

Nella Cell F2 inserire la seguente formula e trascinare / copiare come richiesto.

 =IFERROR(INDEX($B$2:$B$14,MATCH(0,INDEX(COUNTIF($F$1:F1,$B$2:$B$14),0,0),0)),"")

Fase 4: get il numero di gruppi con cui each azienda collabora

Nella Cell G2 inserire la seguente formula e trascinare / copiare fino alla row where i valori di visualizzazione della Column F

 =COUNT(IF(MID($D$2:$D$12,FIND(",",$D$2:$D$12)+1,256)=F2,$E$2:$E$12))

Questa è una formula di matrix in modo da impegnarla premendo Ctrl + Shift + Enter

Fase 5: controllare se l'azienda è unica al gruppo o less

Nella Cell H2 inserire la seguente formula e trascinare / copiare fino alla row where Column G visualizza i valori.

 =IF(COUNT(IF(MID($D$2:$D$12,FIND(",",$D$2:$D$12)+1,256)=F2,$E$2:$E$12))=1,1,0)

Ancora una volta, questa è una formula di matrix in modo da impegnare premendo Ctrl + Shift + Enter

o utilizzare invece questa formula =IF(G2=1,1,0)

immettere qui la descrizione dell'immagine

EDIT: Come per requisito menzionato nel commento

In Cell J2 immettere:

 =IFERROR(INDEX($B$2:$B$19 & "," & $A$2:$A$19,MATCH(0,INDEX(COUNTIF($D$1:J1,$A$2:$A$19 & "," & $B$2:$B$19),0,0),0)),"")

Nella Cell K2 immettere:

 =COUNTIFS($A$2:$A$19,MID(J2,FIND(",",J2)+1,256),$B$2:$B$19,LEFT(J2,(FIND(",",J2,1)-1)))

Nella Cell L2 immettere:

 =IFERROR(INDEX($A$2:$A$19,MATCH(0,INDEX(COUNTIF($L$1:L1,$A$2:$A$19),0,0),0)),"")

In Cell M2 immettere:

 =COUNT(IF(VALUE(MID($J$2:$J$12,FIND(",",$J$2:$J$12)+1,256))=L2,$E$2:$E$12))

Questa è una formula di arrays.

In Cell N2 immettere:

 =IF(N2=1,1,0)

 =IF(COUNT(IF(VALUE(MID($J$2:$J$12,FIND(",",$J$2:$J$12)+1,256))=L2,$E$2:$E$12))=1,1,0)

Questa formula è anche una formula di matrix.

Vedi image per riferimento:

immettere qui la descrizione dell'immagine