Enregistrez le nom de chaque valeur qui apparaît 5 fois dans 5 colonnes

J'ai cinq colonnes de noms de livres. Je search une formule qui savea le nom de n'importe quel livre dans les 5 colonnes de la 6ème colonne.

Actuellement, je peux le faire pour deux colonnes en utilisant ce qui précède, mais j'ai du mal à l'étendre à plusieurs colonnes et à définir un seuil minimum.

=IF(ISERROR(MATCH(A4,$B$4:$B$329,0)),"",A4)

Si quelqu'un peut l'aider serait très apprécié

Les données:

 ABCDE Book 1 Book 1 Book 1 Book 3 Book 1 Book 4 Book 2 Book 2 Book 23 Book 4 Book 6 Book 5 Book 5 Book 14 Book 23 Book 9 Book 22 Book 3 Book 2 Book 17 Book 3 Book 6 Book 7 Book 6 Book 2 Book 7 Book 27 Book 14 Book 15 Book 2 Book 6 Book 4 Book 6 Book 23 Book 13 Book 66 Book 9 Book 34 Book 32 Book 17 Book 18 ABCDE ABCDE Book 1 Book 1 Book 1 Book 3 Book 1 Book 4 Book 2 Book 2 Book 23 Book 4 Book 6 Book 5 Book 5 Book 14 Book 23 Book 9 Book 22 Book 3 Book 2 Book 17 Book 3 Book 6 Book 7 Book 6 Book 2 Book 7 Book 27 Book 14 Book 15 Book 2 Book 6 Book 4 Book 6 Book 23 Book 13 Book 66 Book 9 Book 34 Book 32 Book 17 Book 18 Livre 1 Livre 1 Livre 1 Livre 3 Livre 1 ABCDE Book 1 Book 1 Book 1 Book 3 Book 1 Book 4 Book 2 Book 2 Book 23 Book 4 Book 6 Book 5 Book 5 Book 14 Book 23 Book 9 Book 22 Book 3 Book 2 Book 17 Book 3 Book 6 Book 7 Book 6 Book 2 Book 7 Book 27 Book 14 Book 15 Book 2 Book 6 Book 4 Book 6 Book 23 Book 13 Book 66 Book 9 Book 34 Book 32 Book 17 Book 18 Livre 4 Livre 2 Livre 2 Livre 23 Livre 4 ABCDE Book 1 Book 1 Book 1 Book 3 Book 1 Book 4 Book 2 Book 2 Book 23 Book 4 Book 6 Book 5 Book 5 Book 14 Book 23 Book 9 Book 22 Book 3 Book 2 Book 17 Book 3 Book 6 Book 7 Book 6 Book 2 Book 7 Book 27 Book 14 Book 15 Book 2 Book 6 Book 4 Book 6 Book 23 Book 13 Book 66 Book 9 Book 34 Book 32 Book 17 Book 18 Livre 6 Livre 5 Livre 5 Livre 14 Livre 23 ABCDE Book 1 Book 1 Book 1 Book 3 Book 1 Book 4 Book 2 Book 2 Book 23 Book 4 Book 6 Book 5 Book 5 Book 14 Book 23 Book 9 Book 22 Book 3 Book 2 Book 17 Book 3 Book 6 Book 7 Book 6 Book 2 Book 7 Book 27 Book 14 Book 15 Book 2 Book 6 Book 4 Book 6 Book 23 Book 13 Book 66 Book 9 Book 34 Book 32 Book 17 Book 18 Livre 9 Livre 22 Livre 3 Livre 2 Livre 17 ABCDE Book 1 Book 1 Book 1 Book 3 Book 1 Book 4 Book 2 Book 2 Book 23 Book 4 Book 6 Book 5 Book 5 Book 14 Book 23 Book 9 Book 22 Book 3 Book 2 Book 17 Book 3 Book 6 Book 7 Book 6 Book 2 Book 7 Book 27 Book 14 Book 15 Book 2 Book 6 Book 4 Book 6 Book 23 Book 13 Book 66 Book 9 Book 34 Book 32 Book 17 Book 18 Livre 3 Livre 6 Livre 7 Livre 6 Livre 2 ABCDE Book 1 Book 1 Book 1 Book 3 Book 1 Book 4 Book 2 Book 2 Book 23 Book 4 Book 6 Book 5 Book 5 Book 14 Book 23 Book 9 Book 22 Book 3 Book 2 Book 17 Book 3 Book 6 Book 7 Book 6 Book 2 Book 7 Book 27 Book 14 Book 15 Book 2 Book 6 Book 4 Book 6 Book 23 Book 13 Book 66 Book 9 Book 34 Book 32 Book 17 Book 18 Livre 7 Livre 27 Livre 14 Livre 15 ABCDE Book 1 Book 1 Book 1 Book 3 Book 1 Book 4 Book 2 Book 2 Book 23 Book 4 Book 6 Book 5 Book 5 Book 14 Book 23 Book 9 Book 22 Book 3 Book 2 Book 17 Book 3 Book 6 Book 7 Book 6 Book 2 Book 7 Book 27 Book 14 Book 15 Book 2 Book 6 Book 4 Book 6 Book 23 Book 13 Book 66 Book 9 Book 34 Book 32 Book 17 Book 18 Livre 2 Livre 6 Livre 4 Livre 6 ABCDE Book 1 Book 1 Book 1 Book 3 Book 1 Book 4 Book 2 Book 2 Book 23 Book 4 Book 6 Book 5 Book 5 Book 14 Book 23 Book 9 Book 22 Book 3 Book 2 Book 17 Book 3 Book 6 Book 7 Book 6 Book 2 Book 7 Book 27 Book 14 Book 15 Book 2 Book 6 Book 4 Book 6 Book 23 Book 13 Book 66 Book 9 Book 34 Book 32 Book 17 Book 18 Livre 23 Livre 13 Livre 66 Livre 9 ABCDE Book 1 Book 1 Book 1 Book 3 Book 1 Book 4 Book 2 Book 2 Book 23 Book 4 Book 6 Book 5 Book 5 Book 14 Book 23 Book 9 Book 22 Book 3 Book 2 Book 17 Book 3 Book 6 Book 7 Book 6 Book 2 Book 7 Book 27 Book 14 Book 15 Book 2 Book 6 Book 4 Book 6 Book 23 Book 13 Book 66 Book 9 Book 34 Book 32 Book 17 Book 18 Livre 34 Livre 32 ABCDE Book 1 Book 1 Book 1 Book 3 Book 1 Book 4 Book 2 Book 2 Book 23 Book 4 Book 6 Book 5 Book 5 Book 14 Book 23 Book 9 Book 22 Book 3 Book 2 Book 17 Book 3 Book 6 Book 7 Book 6 Book 2 Book 7 Book 27 Book 14 Book 15 Book 2 Book 6 Book 4 Book 6 Book 23 Book 13 Book 66 Book 9 Book 34 Book 32 Book 17 Book 18 Livre 17 ABCDE Book 1 Book 1 Book 1 Book 3 Book 1 Book 4 Book 2 Book 2 Book 23 Book 4 Book 6 Book 5 Book 5 Book 14 Book 23 Book 9 Book 22 Book 3 Book 2 Book 17 Book 3 Book 6 Book 7 Book 6 Book 2 Book 7 Book 27 Book 14 Book 15 Book 2 Book 6 Book 4 Book 6 Book 23 Book 13 Book 66 Book 9 Book 34 Book 32 Book 17 Book 18

Résultat attendu:

 ABCDE Answer Book 1 Book 1 Book 1 Book 3 Book 1 Book 1 Book 4 Book 2 Book 2 Book 23 Book 4 Book 6 Book 6 Book 5 Book 5 Book 14 Book 23 Book 9 Book 22 Book 3 Book 2 Book 17 Book 3 Book 6 Book 7 Book 6 Book 2 Book 7 Book 27 Book 14 Book 15 Book 2 Book 6 Book 4 Book 6 Book 23 Book 13 Book 66 Book 9 Book 34 Book 32 Book 17 Book 18 ABCDE Réponse ABCDE Answer Book 1 Book 1 Book 1 Book 3 Book 1 Book 1 Book 4 Book 2 Book 2 Book 23 Book 4 Book 6 Book 6 Book 5 Book 5 Book 14 Book 23 Book 9 Book 22 Book 3 Book 2 Book 17 Book 3 Book 6 Book 7 Book 6 Book 2 Book 7 Book 27 Book 14 Book 15 Book 2 Book 6 Book 4 Book 6 Book 23 Book 13 Book 66 Book 9 Book 34 Book 32 Book 17 Book 18 Livre 1 Livre 1 Livre 1 Livre 3 Livre 1 Livre 1 ABCDE Answer Book 1 Book 1 Book 1 Book 3 Book 1 Book 1 Book 4 Book 2 Book 2 Book 23 Book 4 Book 6 Book 6 Book 5 Book 5 Book 14 Book 23 Book 9 Book 22 Book 3 Book 2 Book 17 Book 3 Book 6 Book 7 Book 6 Book 2 Book 7 Book 27 Book 14 Book 15 Book 2 Book 6 Book 4 Book 6 Book 23 Book 13 Book 66 Book 9 Book 34 Book 32 Book 17 Book 18 Livre 4 Livre 2 Livre 2 Livre 23 Livre 4 Livre 6 ABCDE Answer Book 1 Book 1 Book 1 Book 3 Book 1 Book 1 Book 4 Book 2 Book 2 Book 23 Book 4 Book 6 Book 6 Book 5 Book 5 Book 14 Book 23 Book 9 Book 22 Book 3 Book 2 Book 17 Book 3 Book 6 Book 7 Book 6 Book 2 Book 7 Book 27 Book 14 Book 15 Book 2 Book 6 Book 4 Book 6 Book 23 Book 13 Book 66 Book 9 Book 34 Book 32 Book 17 Book 18 Livre 6 Livre 5 Livre 5 Livre 14 Livre 23 ABCDE Answer Book 1 Book 1 Book 1 Book 3 Book 1 Book 1 Book 4 Book 2 Book 2 Book 23 Book 4 Book 6 Book 6 Book 5 Book 5 Book 14 Book 23 Book 9 Book 22 Book 3 Book 2 Book 17 Book 3 Book 6 Book 7 Book 6 Book 2 Book 7 Book 27 Book 14 Book 15 Book 2 Book 6 Book 4 Book 6 Book 23 Book 13 Book 66 Book 9 Book 34 Book 32 Book 17 Book 18 Livre 9 Livre 22 Livre 3 Livre 2 Livre 17 ABCDE Answer Book 1 Book 1 Book 1 Book 3 Book 1 Book 1 Book 4 Book 2 Book 2 Book 23 Book 4 Book 6 Book 6 Book 5 Book 5 Book 14 Book 23 Book 9 Book 22 Book 3 Book 2 Book 17 Book 3 Book 6 Book 7 Book 6 Book 2 Book 7 Book 27 Book 14 Book 15 Book 2 Book 6 Book 4 Book 6 Book 23 Book 13 Book 66 Book 9 Book 34 Book 32 Book 17 Book 18 Livre 3 Livre 6 Livre 7 Livre 6 Livre 2 ABCDE Answer Book 1 Book 1 Book 1 Book 3 Book 1 Book 1 Book 4 Book 2 Book 2 Book 23 Book 4 Book 6 Book 6 Book 5 Book 5 Book 14 Book 23 Book 9 Book 22 Book 3 Book 2 Book 17 Book 3 Book 6 Book 7 Book 6 Book 2 Book 7 Book 27 Book 14 Book 15 Book 2 Book 6 Book 4 Book 6 Book 23 Book 13 Book 66 Book 9 Book 34 Book 32 Book 17 Book 18 Livre 7 Livre 27 Livre 14 Livre 15 ABCDE Answer Book 1 Book 1 Book 1 Book 3 Book 1 Book 1 Book 4 Book 2 Book 2 Book 23 Book 4 Book 6 Book 6 Book 5 Book 5 Book 14 Book 23 Book 9 Book 22 Book 3 Book 2 Book 17 Book 3 Book 6 Book 7 Book 6 Book 2 Book 7 Book 27 Book 14 Book 15 Book 2 Book 6 Book 4 Book 6 Book 23 Book 13 Book 66 Book 9 Book 34 Book 32 Book 17 Book 18 Livre 2 Livre 6 Livre 4 Livre 6 ABCDE Answer Book 1 Book 1 Book 1 Book 3 Book 1 Book 1 Book 4 Book 2 Book 2 Book 23 Book 4 Book 6 Book 6 Book 5 Book 5 Book 14 Book 23 Book 9 Book 22 Book 3 Book 2 Book 17 Book 3 Book 6 Book 7 Book 6 Book 2 Book 7 Book 27 Book 14 Book 15 Book 2 Book 6 Book 4 Book 6 Book 23 Book 13 Book 66 Book 9 Book 34 Book 32 Book 17 Book 18 Livre 23 Livre 13 Livre 66 Livre 9 ABCDE Answer Book 1 Book 1 Book 1 Book 3 Book 1 Book 1 Book 4 Book 2 Book 2 Book 23 Book 4 Book 6 Book 6 Book 5 Book 5 Book 14 Book 23 Book 9 Book 22 Book 3 Book 2 Book 17 Book 3 Book 6 Book 7 Book 6 Book 2 Book 7 Book 27 Book 14 Book 15 Book 2 Book 6 Book 4 Book 6 Book 23 Book 13 Book 66 Book 9 Book 34 Book 32 Book 17 Book 18 Livre 34 Livre 32 ABCDE Answer Book 1 Book 1 Book 1 Book 3 Book 1 Book 1 Book 4 Book 2 Book 2 Book 23 Book 4 Book 6 Book 6 Book 5 Book 5 Book 14 Book 23 Book 9 Book 22 Book 3 Book 2 Book 17 Book 3 Book 6 Book 7 Book 6 Book 2 Book 7 Book 27 Book 14 Book 15 Book 2 Book 6 Book 4 Book 6 Book 23 Book 13 Book 66 Book 9 Book 34 Book 32 Book 17 Book 18 Livre 17 ABCDE Answer Book 1 Book 1 Book 1 Book 3 Book 1 Book 1 Book 4 Book 2 Book 2 Book 23 Book 4 Book 6 Book 6 Book 5 Book 5 Book 14 Book 23 Book 9 Book 22 Book 3 Book 2 Book 17 Book 3 Book 6 Book 7 Book 6 Book 2 Book 7 Book 27 Book 14 Book 15 Book 2 Book 6 Book 4 Book 6 Book 23 Book 13 Book 66 Book 9 Book 34 Book 32 Book 17 Book 18

L'alternative serait de mettre en évidence toutes les valeurs qui apparaissent au less 5 fois.

Pour une formule:

 =IFERROR(INDEX($A$1:$E$11,AGGREGATE(14,6,ROW($A$1:$E$11)/((COUNTIF($A$1:$E$11,$A$1:$E$11)=COLUMNS($A$1:$E$11))*(COLUMN($A$1:$E$11)=1)),ROW(1:1)),1),"")

entrez la description de l'image ici

Une mise en garde. Les titres doivent être uniques par colonne.

Preuve de concept

Sélectionnez la gamme de vos données et assurez-vous que le coin supérieur gauche de la sélection est votre cellule active. Ensuite, pour le formatting conditionnel, select l'option "Utiliser une formule …" et entrez la formule suivante dans la barre de formule:

= COUNTIF ($ A $ 1: $ E $ 11, A1)> = 5

Mise en forme conditionnelle

Sélectionnez le button de format et select diverses options de formatting que vous souhaitez appliquer lorsque votre état est vrai. Dans ce cas, la condition est lorsque l'input d'une cellule se produit 5 fois ou plus dans la plage sélectionnée.

Aucune cellule auxiliaire n'a été utilisée dans cette méthode.

Maintenant, ce qui suit n'est pas ce que vous avez demandé, mais je pensais que je l'inclurai au cas où cela aurait aidé ou aidé quelqu'un nous sur les étapes de votre solution.

entrez la description de l'image ici

Pour générer la list des noms uniques, j'ai utilisé la formule de tableau suivante dans une seule cellule dans G2:

 {=INDIRECT(TEXT(MIN(IF(($A$1:$E$11<>"")*(COUNTIF($G$1:G1,$A$1:$E$11)=0),ROW($1:$11)*100+COLUMN($A:$E),7^8)),"R0C00"),)&""}

Pour générer {} qui est l'indication de la formule du tableau, maintenez CTRL et SHIFT lorsque vous appuyez sur ENTER. C'est pourquoi les formules de tableau sont également connues sous le nom de formules CSE. J'ai ensuite copié (ne faites pas glisser) la cellule au besoin.

Dans la colonne adjacente H, le nombre de fois que la colonne de nom F est apparue dans la plage A1: E11 a été compté. En H2, la formule suivante a été utilisée et traînée vers le bas:

 =COUNTIF($A$1:$E$11,G2)

La touche finale a été d'append un formatting conditionnel pour changer la couleur des cellules. En utilisant la même technique que ci-dessus. Avec la gamme G2: H20 sélectionnée et G2 comme cellule active, j'ai utilisé la formule suivante dans la window de mise en forme conditionnelle:

 =$H2>=5

Idée rapide: 5 colonnes auxiliaires avec la formule: = =if(countif($A:$E;A1)>=5;countif($A:$E;A1);"") Format conditionnel dans a: e range avec = f1 … j11 <> "" puis mettez en surbrillance en vert.

entrez la description de l'image ici