formule doublon avec condition

[david84]

Bonjour,
J'ai un tableau avec 2 colonnes.
je cherche une formule me permettant de récupérer sur 2 autres colonnes les valeurs des 2 premières colonnes sans les doublons de la colonne 2.
ex :
cyclisme paris cyclisme paris
cyclisme caen natation cannes
cyclisme paris
natation cannes
natation strasbourg
natation cannes

Ci-joint fichier.
Merci.

 

[david84]

Re : formule doublon avec condition

Bon, j'ai compris : fallait pas vous chercher !
Félicitation à tous et chapeau bas messieurs.
Bcharef, super travail (rien qu'en lisant ta formule, j'ai mal au crâne...)
Cisco, merci pour ton explication.
A bientôt sur le forum

 

[lulu6424]

Re : formule doublon avec condition

Messieurs
besoin d'aide également...mais sur 7 colonnes au total, avec l'extration de doublons sur une autre feuille excel...
Merci d'avance si c'est réalisable...

 

[Guillaumega]

Re : formule doublon avec condition

lulu,

tu as déjà fait un post. Merci de par avance de le continuer

Bien à toi,
Guillaume

 

[ROGER2327]

Re : formule doublon avec condition

Bonjour lulu6424

Messieurs
besoin d'aide également...mais sur 7 colonnes au total, avec l'extration de doublons sur une autre feuille excel...
Merci d'avance si c'est réalisable...

D'après les messages qui précèdent, il semble que oui...
Voyez l'exemple joint.
​

ROGER2327
#2427

 

[CISCO]

Re : formule doublon avec condition

Bonsoir à toutes et à tous

Une variante en pièce jointe répondant à la demande de David84.

En fait, on utilise toujours, ou presque, la même méthode, proposée initialement sur ce forum, et à ma connaissance, par Monique :

On recherche (avec SI(NB.SI()=0;LIGNE()) ) le numéro des lignes contenant, dans une colonne source (ex A), une valeur (texte ou num) pas encore écrite dans une colonne récapitulative (ex D), au dessus de la cellule où viendra se placer le résultat. Puis on garde (avec MIN ou PETITE.VALEUR) une valeur particulière de cette liste.

C'est donc fait avec une formule du style
MIN(SI(NB.SI(D$1: D1;A$2:A$19)=0;LIGNE(A$2:A$19)))
ou
PETITE.VALEUR(SI(NB.SI(D$1: D1;A$2:A$19)=0;LIGNE(A$2:A$19)));LIGNES(D$1: D...))

Le calcul ne fonctionne que grace à l'utilisation d'une fonction (MIN ou PETITE.VALEUR) travaillant forcément par rapport à une liste, et sous forme matricielle (ctrl+maj+entrée).

Puis on affiche le contenu de la cellule de la colonne source et de la ligne choisie, avec INDEX.

Ici, dans le cas présent, il y a non pas une colonne source, mais 2 (A et B), et, non pas une colonne récapitulative, mais 2 (D et E).

On pouvait espérer que cela fonctionne avec quelque chose du style
MIN(SI(NB.SI(D$1: D1&E$1: E1;A$2:A$19&B$2:B$19)=0;LIGNE(A$2:A$19)))
Malheureusement, ce n'est pas le cas : la fonction NB.SI n'accepte pas la concaténation des plages D$1: D1&E$1: E1 (ou plus exactement de D$1: D2&E$1: E2 dans la cellule suivante, puis de D$1: D3&E$1: E3 dans...).

Par chance, on peut presque faire la même chose avec la fonction EQUIV. La formule utilisée est alors ici :

MIN(SI(ESTNA(EQUIV(A$2:A$19&B$2:B$19;D$1:D2&E$1:E2;0));LIGNE(A$2:A$19)))

EQUIV renvoie, pour toutes les valeurs existantes dans A$2:A$19&B$2:B$19 et aussi dans D$1: D2&E$1:E2 une valeur numérique. Pour les autres, c'est à dire celles qui n'ont pas encore été recopiées dans la liste récapitulative, elle renvoie #NA. Pour mettre ces dernières en évidence, on utilise ESTNA, on garde en mémoire leur n° de ligne avec LIGNE, et on garde le numéro de ligne le plus petit avec MIN.

Bon apparemment, ça fonctionne. Il y a bien sûr des variantes, en utilisant des noms définis pour l'occasion, en supprimant la première ligne....

@ plus

PS1 : le &"" à la fin de la formule permet d'éviter l'écriture d'un test du style SI(ESTTEXTE(X);X;"") servant à éliminer les affichages gênants (des 0 ici), écriture lourde lorsque X est en réalité une longue formule.


PS2 : Et le fichier pour Lulu6424

 

[ROGER2327]

Re : formule doublon avec condition

Re...

Merci pour cette synthèse. Et merci à Monique pour l'idée de base. (Je suis trop jeune sur ce forum pour savoir d'où viennent les bonnes idées qu'on y trouve.)​

ROGER2327
#2428

 

[ROGER2327]

Re : formule doublon avec condition

Suite...

Je viens d'étudier le classeur joint, et il me semble qu'il faudrait écrire

[B][COLOR="DarkSlateGray"]=INDEX(Feuil1!A[COLOR="Red"]$[/COLOR]1:A[COLOR="Red"]$[/COLOR]202;MIN(SI(ESTNA(EQUIV(DAT1&DAT2&DAT3&DAT4&DAT5&DAT6&DAT7;B$1:B2&C$1:C2&D$1:D2&E$1:E2&F$1:F2&G$1:G2&H$1:H2;0));LIGNE(DAT1);NBVAL(DAT1)+2)))&""[/COLOR][/B]

au lieu de

[B][COLOR="DarkSlateGray"]=INDEX(Feuil1!A1:A202;[I]etc.[/I][/COLOR][/B]

en B3. (Correction similaire dans les colonnes suivantes.)

En utilisant les plages nommées de la feuille "Feuil1", on peut d'ailleurs écrire une formule unique en B3, à tirer ensuite à droite et vers le bas :

[COLOR="DarkSlateGray"][B]=INDEX([COLOR="Blue"]DECALER(DAT1;0;COLONNE()-COLONNE($B$3))[/COLOR];MIN(SI(ESTNA(EQUIV([COLOR="Blue"]DAT[/COLOR];[COLOR="Blue"]$[/COLOR]B$1:[COLOR="Blue"]$[/COLOR]B2&[COLOR="Blue"]$[/COLOR]C$1:[COLOR="Blue"]$[/COLOR]C2&[COLOR="Blue"]$[/COLOR]D$1:[COLOR="Blue"]$[/COLOR]D2&[COLOR="Blue"]$[/COLOR]E$1:[COLOR="Blue"]$[/COLOR]E2&[COLOR="Blue"]$[/COLOR]F$1:[COLOR="Blue"]$[/COLOR]F2&[COLOR="Blue"]$[/COLOR]G$1:[COLOR="Blue"]$[/COLOR]G2&[COLOR="Blue"]$[/COLOR]H$1:[COLOR="Blue"]$[/COLOR]H2;0));LIGNE(DAT1);NBVAL(DAT1)+2)))&""[/B][/COLOR]

Il y a quelques curiosités que je ne m'explique pas :

• mon précédent classeur pèse 71 ko alors que celui que je joins dépasse 200 ko ;
• l'ancien classeur est bien plus rapide que le nouveau.

(Les données sont les mêmes, et j'ai recréé deux classeurs neufs afin d'éliminer les "résidus" de manipulation.)
​

ROGER2327
#2429

 

[CISCO]

Re : formule doublon avec condition

Bonjour à tous

Roger2327, tu as raison pour les $. Dans la précipitation, j'avais oublié de faire cette modif dans le fichier destiné à Lulu6424...

Au passage, j'en profite pour faire quelques simplifications (on n'a plus besoin de prendre une liste trop longue, ni du NBVAL()+2) dans mes précédents fichiers.

La formule de base devient donc
{=INDEX(A$1:A$19;MIN(SI(ESTNA
(EQUIV(plagesource1&plagesource2;hautplagerécap1&hautplagerécup2;0));LIGNE(plagesource1))))&""}


@ plus

 

[CISCO]

Re : formule doublon avec condition

Rebonjour à tous

Remarque au passage, on peut utiliser cette formule lorsqu'on veut, non pas éliminer les doublons, mais au contraire, les garder, en remplaçant le ESTNA par un ESTNUM.

Cf un exemple en pièce jointe pour 8 conditions, avec une formule qui pourrait remplacer parfois un SI(X=Y;SI(... avec 8 SI imbriqués au total (mais uniquement avec des =, et pas avec des < ou des > !).

@ plus

 

[CISCO]

Re : formule doublon avec condition

Bonjour à tous

Quelques $ en moins, histoire de demander moins de calculs à excel.

@ plus

 

[david84]

Re : formule doublon avec condition

Bravo Cisco,
Ce que j'apprécie avec toi c'est qu'en plus d'avoir la volonté de trouver une solution, tu prenne le temps de l'expliquer pour qu'elle soit compréhensible.
Cela me permets en l'occurrence de la comprendre, et de comprendre pourquoi le fait de se servir d' equiv simplifie les choses.

 

[CISCO]

Re : formule doublon avec condition

Bonjour à tous

Ci-joint, deux variantes :
* la première, demandant encore moins, je l'espère, de calculs à excel.
Le principe est le suivant :
Dans les fichiers précédents, on "compare" avec EQUIV toutes les valeurs des colonnes A et B (fichier formule doublon 3) ou presque toutes ces valeurs (fichier formule doublon 5), aux valeurs des premières cellules des colonnes D et E. C'est long
Pour comprendre la nouvelle formule, plaçons nous dans une cellule particulière, par exemple en D8. La dernière nouvelle valeur est en D7, AERONAUTIQUE (CAEN), et se trouve aussi quelque part, sur une ligne particulière, la ligne 10, dans la colonne A. En pratique, ce n'est donc pas nécessaire de comparer toutes les valeurs, de A2:A10&B2:B10, aux valeurs contenues dans le haut des colonnes D et E, puisqu'on sait que le haut des colonnes D et E est un "résumé" des valeurs A2:A10&B2:B10. Les nouvelles valeurs A&B à écrire en D et E ne peuvent être qu'en dessous de la ligne 10, dans les colonnes A et B.
On recherche donc le premier AERONAUTIQUE CAEN (D7&E7) dans A et B, on trouve la ligne 10 et on fait la comparaison de A11:A19&B11:B19 aux valeurs des premières cellules des colonnes D&E. On trouve que A11&B11 est une nouvelle valeur, et on l'écrit en D8.
Et ainsi de suite

* Dans le second fichier, on classe toutes les disciplines et les villes par ordre alphabétique. La formule est lourde, mais bon, ça fonctionne. On ne peut malheureusement pas utiliser la technique précédente pour diminuer le temps de calcul, car, ce coup ci, les valeurs ne sont pas "extraites" dans l'ordre, en allant vers le bas du fichier. On peut très bien extraire en premier le contenu de la ligne 15, puis ensuite celui de la ligne 5, puis après celui de la ligne 26...

@ plus et joyeux Noël