Supprimer les cellules en double dans les différentes colonnes d'une ligne

Bonjour,

HELP : j'ai tout essayé, j'ai cherché sur internet, rien trouvé !

J'ai un tableau avec 150 000 lignes et 37 colonnes.

Pour une ligne donnée, j'ai 37 colonnes avec pour chaque cellule une valeur texte. Chaque ligne représente un nom latin d'une plante.

Chaque colonne contient les synonymes en anglais de son nom latin. Je peux avoir jusqu'à 40 synonymes (avec doublons).

Par exemple : Pour la ligne 2, les colonne 3 et colonne 16 contiennent toutes les deux "Narrowleaf".

Pour la 3ème ligne, les colonnes 15, 23 et 26 contiennent toutes les 3 la valeur "Leak" + les colonnes 8 et 35 contiennent toutes les 2 la valeurs "Water speed".

Je souhaite supprimer les valeurs en double et conserver 1 valeur unique. Je ne souhaite pas perdre mon synonyme, juste supprimer mon doublon.

Le problème c'est qu'avec excel : supprimer les doublons : ca s'applique aux valeurs de chaque ligne dans une colonne donnée. (et je ne peux pas transposer parce qu'il y a beaucoup trop de lignes > nombre de colonnes possible dans excel). L'autre soucis, c'est que même si je fais 16 000 lignes par 16 000 lignes (j'ai tenté). Mais ce n'est pas possible car je ne peux pas dire à excel de regarder 1 colonne par 1 colonne tout seul. Soit je sélectionne la colonne sur laquelle s'applique la suppression de doublons. Il faudrait que je sélectionne à la main, supprimer les doublons, que je choisisse une seule colonne, que j'applique, et que je fasse cela 16 000 fois, pour chaque colonne, multiplié par le nombre de fichier que j'aurais pour atteindre mes 150 000 lignes de départ (que j'aurais fractionné). IMPOSSIBLE ! . Soit si je sélectionne plusieurs colonnes, ca va supprimer les lignes doublons qui ont en commun les 3 valeurs des colonnes sélectionnées. Ce n'est pas non plus ce que je veux. J'ai testé de créer un autre onglet et d'appliquer sur ma feuille précédente un : Si(nb.si(A1:AK1);cellule à recherchée en doublons dans la plage)>1;"";valeur cellule"). Sauf qu'il supprime les 2 valeurs en doublons, donc je perds une info. J'ai essayé de mettre en surbrillance les valeurs en double puis de les supprimer, mais ca ne marche pas non plus.

Le plus simple pour moi, serait de conserver mes valeurs en ligne et de trouver un moyen de supprimer les valeurs en double en conservant 1 des valeurs pour ne pas perdre l'info.

Une idée ?

Peut-être une macro mais je n'y connais rien en macro.
Merci :)

Bonjour et bienvenue,

Merci de joindre un fichier à ta demande avec des exemples de résultats.

Cdlt.

J'ai extrait les colonnes de mes noms en anglais de mon tableau pour que ce soit moins lourd. J'ai essayé de joindre 2 fois mais ca n'a pas fonctionné. J'ai extrait que les 2500 premières lignes là. En vrai ca fait 150 000. Il ne faut pas supprimer de lignes (mêmes vides) pour que ca reste au même niveau dans mon tableau plus global (donc aucune des lignes n'est vide).

Il y a beaucoup de cellules vides car pour certaines plantes, je n'ai pas trouvés de synonymes ou elles n'en ont pas.

Le but à la fin c'est de concaténer dans une cellule, pour chaque ligne, dans une nouvelle colonne, les synonymes anglais (sans les doublons).

Merci beaucoup.

un exemple : ligne 2425 il y a des doublons "European Silver-fir" Colonne C et N.

Salut Thomas,
Salut Jean-Eric,

si je comprends bien, il faut supprimer les doublons puis concaténer dans une nouvelle colonne toutes les valeurs uniques.
Si je suis bien, toutes les colonnes, ou presque, sont complètes, en fonction du nombre de synonymes.
Pour faciliter le travail et ta consultation, ne serait-il pas plus facile d'avoir cette concaténation en colonne [B:B] ?

Je dis ça, je ne dis rien : encore un fichier-exemple extraordinaire !

Version valable UNIQUEMENT en ayant inséré une colonne vide en [B:B] pour les concaténations (sur ce fichier-ci, c'est fait !).
Un double-clic sur la feuille démarre la macro avec concaténation en colonne [B:B]
Attention : Common silver Fir,European silver Fir,European Silver-fir,Silver Fir : le tiret ne sera pas pris en compte !

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'
Dim tTab
'
Cancel = True
tTab = [A1].Resize(UsedRange.Rows.Count, UsedRange.Columns.Count + 1).Value
'
For x = 2 To UBound(tTab, 1)
    For y = 3 To UBound(tTab, 2) - 1
        For Z = y + 1 To UBound(tTab, 2)
            If tTab(x, Z) = tTab(x, y) Then
                tTab(x, Z) = ""
                For w = Z To UBound(tTab, 2) - 1
                    tTab(x, w) = tTab(x, w + 1)
                Next
            End If
        Next
    Next
    tTab(x, 2) = ""
    For y = 3 To UBound(tTab, 2)
        If tTab(x, y) <> "" Then tTab(x, 2) = tTab(x, 2) & IIf(tTab(x, 2) = "", tTab(x, y), "," & tTab(x, y))
    Next
Next
[A1].Resize(UBound(tTab, 1), UBound(tTab, 2)).Value = tTab
Columns.AutoFit
'
End Sub

Á tester, à tester, à tester...

Joyeux Noël !

A+

Re-bonjour/Bonsoir,

Super merci beaucoup ! Tu as tout compris à ma demande.

Ca marche du tonnerre ! Ca me fait plaisir. Quand je vois que vous arrivez en quelques minutes à résoudre un problème sur lequel j'ai réfléchi et tester des heures, ca fait rêver.

Le fichier que j'ai envoyé n'est qu'une étape de travail. Mon fichier de base fait 60 colonnes sur 200 000 lignes donc ce que j'ai envoyé est juste un extrait brouillon. Le but était évidemment de récupérer les infos concaténées pour n'avoir plus qu'une colonne au lieu de plusieurs colonnes (où j'avais fait une étape préalable de conversion par virgule et supprimé les espaces). Donc peu importe l'emplacement de la concaténation, B:B m'allait très bien.

Juste un point supplémentaire : Possible de rajouter quelques lignes de code pour concaténer en conservant les couleurs du texte ?

En effet, je souhaite faire la même chose pour les synonymes en français. Sauf que pour certains synonymes j'ai l'info s'il est très utilisé, moyennement utilisé, peu utilisé ou aucune info. Je souhaiterais concaténer de la même façon en ayant en premier les plus utilisés d'une couleur, les moyennement utilisés d'une autre couleur ensuite ...

J'ai préparé le fichier en mettant en couleur ce que je souhaite, dans l'ordre d'importance (du + utilisé au - utilisé, puis les autres synonymes).
J'ai vérifié ta macro : conserve bien la première valeur qui apparait et supprime les suivantes (donc ca conserve bien celui qui est en couleur).

Cependant, la concaténation fait perdre la couleur.
Je n'avais pas pensé à ajouter cette info initialement parce que j'essayais de résoudre le problème pour les synonymes anglais qui n'avaient pas ces infos / couleurs.

Merci beaucoup !!!

Je vous souhaite un bon réveillon. Je vais aller profiter un peu en famille.

Belle soirée !

Je rebondis sur le fichier avec les noms anglais. Finalement, j'ai repéré quelques bugs :

- il faut que je rajoute une colonne supplémentaire vide à la fin (sinon la dernière colonne remplie n'est pas prise en compte).
- Quand le fichier est important, il y a des erreurs. Exemple :

Dans mon énorme fichier dont j'ai extrait que le colonnes des noms EN et pour lequel j'ai rajouté 2 colonnes au début, j'ai lancé la macro. Cependant, pour la ligne 88944 (/193 000) qui a sa dernière colonne remplie : il y a une seule fois "spurge" en AL et une fois "Marvailla" en AM. Une fois la macro lancée, je me retrouve en B (concaténée) avec "Painted leaf, Fire on the mountains, Mexican fire plant, Red milkweed, Milk weed, Milk grass, Milky-milky, Wild poinsettia, Spurge, Spurge, Spurge, Spurge, Spurge". Donc 5 fois la valeur "Spruge" et 0 fois "Maravilla". Pourtant "Spurge" n'apparaissait pas en doublon dans toutes mes colonnes. Je peux alors relancer la macro plusieurs fois et il va supprimer au fur et à mesure 1 ou 2 valeurs de "Spurge" à cahque exécution.

Par contre si je supprime toutes les lignes précédentes et suivantes et que je lance la macro, il n'y a pas ce bug. C'est étrange, non ?

- Dernier point, mon fichier est tellement lourd que pour les noms anglais ca fonctionne, mais quand je le fais sur les homonymes, qui a pourtant beaucoup moins de valeurs (10 fois moins de valeurs non vides que le fichier des noms anglais mais plus de colonnes et autant de lignes), impossible. Ca tourne au début puis excel "ne répond pas" et même en laissant tourner toute la nuit, il ne s'est rien passé. J'ai essayé sur un extrait du fichier de 300 lignes ca a marché en ramant.

Pourquoi ? Un problème dans mon fichier ? Un problème de performance de pC ? Où y-a-il moyen de faire une macro pour faire des sous-macros de ce que tu m'as donné en faisant 100 lignes puis 100 lignes puis 100 lignes ... ?

Si besoin de fichier excel exemple, je peux fournir :)

Merci beaucoup.

Salut Thomas,

même le meilleur des garagistes de génie ne pourra réparer une voiture qu'il n'a pas devant lui !

Alors, oui, des fichiers-exemples REPRÉSENTATIFS de la structure exacte des fichiers réels est indispensable pour comprendre le pourquoi du comment.
Donc, fournis-nous des exemples de fichiers réels avec quelques dizaines de lignes (pleines, hein, du moins normales en fonction de ton travail !) concentrant toutes les situations possibles de tes données, histoire de prévoir des cas spéciaux ainsi que les résultats espérés sur une autre feuille.

Pour garder tes couleurs, je sens que ça va te paraître tout de suite beaucoup moins magique et là aussi, fichier-exemple et résultats espérés.

Plus tu nous donnes d'infos sur tes fichiers et ce que tu espères, moins on tournera autour de la solution !

Joyeux Noël !

A+

Bonjour,

Je vais essayer d'expliquer clairement, même si ce n'est pas facile de faire concis. Surtout qu'on ne peut pas vraiment mettre des gros fichiers excel ici.

J'ai un fichier de base (voir extrait excel en PJ) qui est une compilation de BDD publiques. Chaque ligne correspondant à un nom de plante (qui peut être son nom officiel ou un synonyme ou un homonyme).

1) Noms anglais (voir fichier "noms EN macro")

De base, quand j'ai posé ma question ici je commençais par cette étape.

J'ai 3 BDD différentes qui renseignaient des noms anglais (EN) j'avais donc fait des rechercheV pour récupérer les infos en fonction du numéro botanique (chaque BDD ayant sa propre notation). Au final je me suis retrouvé avec 3 colonnes de noms EN (chacune issue d'une BDD). Mais dans chaque colonne les noms EN étaient séparés par des virgules. Mon but était de rassembler en une seule colonne toutes les noms EN séparés par des virgules. Donc j'ai pris ces 3 colonnes, que j'ai collées dans un nouveau classeur excel (parce que mon fichier de base est énorme et rame beaucoup). J'ai converti en séparant par virgule. Je me suis rendu compte qu'il y avait beaucoup de doublons (au sein de la ligne). Je ne voulais pas supprimer les lignes vides pour pouvoir, une fois les doublons supprimés avoir :

- un fichier excel dédiés aux noms EN (avec les colonnes de début : numéros d'identification du nom + nom latin + une colonne concaténés des noms EN sans doublons + une colonne pour chaque nom EN (sans doublons) ;

- mon fichier de base simplifié en ayant une seule colonne qui contient tous les noms EN concaténés sans doublons.

Sur cette partie c'est bon grâce au code que tu m'as fourni.

Par contre je me suis rendu compte qu'il fallait que je rajoute une colonne vide à la fin car sinon la dernière colonne (qui contenait un nom pour certaines lignes) n'étaient pas prises en compte. (exemple ligne 6 du fichier "noms EN macro" : tu peux voir onglet 2, colonne B (concaténation) après avoir lancé la macro 1 fois que "Maravilla" n'est pas indiquée, pourtant il l'était dans la colonne AM.

J'ai dû relancer 4 fois la macro car comme dit dans mon message précédent, sur certaines lignes, des noms étaient multipliés et concaténés avec multiplication comme la ligne 88944 de mon fichier de base. Le problème c'est que je voudrais bien t'envoyer mais je ne peux pas. Et si je supprime les lignes d'avant et d'après pour t'envoyer que cette ligne dans un fichier excel, quand on lance la macro, ca ne le fait plus, le problème est résolu. (exemple ligne 6 du fichier "noms EN macro" : tu peux voir onglet 2, colonne B (concaténation) après avoir lancé la macro 1 fois que "Spurge" apparait 5 fois et il apparait aussi plusieurs fois, dans les colonnes O, P, Q, R, S alors qu'il n'était que dans la colonne AL dans l'onglet 1 avant macro. Tu peux voir le résultat après avoir exécuté la macro 2, 3 et 4 fois. Ou tout devient bon.

2 ) Noms FR

Maintenant que la solution fonctionnait je me suis dit que j'allais pouvoir faire la même chose avec ta macro. Ce qui pourrait être le cas.

Cependant, j'avais zappé, une des BDD m'a donné une information supplémentaire qui est intéressante pour moi à exploiter : le niveau de priorité d'utilisation. Les autres BDD ne donnent pas d'informations supplémentaires, juste des noms français, sans spécifications.

Donc je me suis dit qu'au lieu d'avoir 6 colonnes de noms FR (1 pour les noms FR très utilisés = "recommandés", 1 pour les noms FR moyennement utilisés = "secondaires", 1 pour les noms FR peu utilisés = "peu usité", 3 autres issues d'autres BDD qui mettaient les noms FR dans une même cellule mais séparés par des virgules). J'ai voulu faire 1 seule colonne dans mon fichier de base. Et créer de la même façon que pour les noms EN, un fichier dédiés aux noms FR. Donc mêmes étapes qu'avant : conversion, suppression doublons, concaténation.

Là où j'avais besoin d'une modification du code ce serait pour conserver l'information du niveau de priorité d'utilisation. Je souhaiterais pour cela mettre des couleurs sur les textes en fonction du niveau comme dans le fichier que je t'ai fourni (extraitFR). Ayant vu que les doublons supprimés sont ceux des colonnes excepté la première qui contient la valeur, j'ai mis dans l'ordre. Dans la concaténation, j'aimerais retrouver les couleurs, ainsi je sais lesquels ont un nveau de priorisation d'utilisation.

3) Noms homonymes

Dans mon fichier de base, j'ai l'information quand c'est un homonyme ou non dans la colonne H, qui contient un 1 quand c'est le cas. C'est ainsi un homonyme (nom latin aussi) du "nom retenu" = nom latin validé. Donc j'ai utilisé une fonction matricielle FILTRE (que j'ai transposée) pour aller chercher toutes les noms latins homonymes du même nom latin validé (à partir de la colonne B qui me dit quel est le numéro du nom latin validé : lui même n'étant pas considéré comme un homonyme des autres). Donc j'ai fait une colonne où j'ai mis pour chaque nom de plante, le nom latin validé associé. La fonction filtre transposée m'a permis d'obtenir tous les homonymes (parfois jusqu'à 40). J'ai copié et coller en valeurs dans toutes les colonnes associées. Donc j'ai plus de colonnes que dans le fichier Noms EN, autant de ligne, mais plus de cellules vides (d'apparence) donc au final moins de valeurs (comptés par excel en bas). Le problème c'est que ca beug. Quand je lance la macro. Ca tourne, excel ne répond pas pendant des heures mais il ne se passe rien. Je crois que c'est issue de ma formule avec la fonction filtre car je rentre du texte vide "" quand mon SI n'est pas respecté (en même temps c'est soit ca soit des 0, même si je ne rentre pas de valeur si FAUX ca remplace par des 0, ca ne laisse pas vide). J'ai essayé de faire remplacer vide par rien (avec CTRL+H : mais ca mouline des heures et ca fini par planter, j'ai fait ca cette nuit, je voyais les lignes défiler dans la soirée mais ce matin excel était fermé avec récupération proposé au même point qu'avant que je lance le remplacement). Quand ce n'est pas du texte vide, il dit qu'il ne trouve aucune valeur donc c'est bien ca qui le gêne car ca lui fait énormément de valeurs en plus à traiter pendant la macro. Je le vois aussi quand j'utilise la fonction filtre même après avoir copié et collé en valeurs car les données ne sont pas bien repérées par excel. Quand je me mets dans une colonne des homonymes, que j'appuie sur CTLR+fleche du bas, ca ne m'envoie pas sur la première cellule non vide, ca me renvoie soit sur une cellule vide (d'apparence), soit sur aucune valeur et ca descend à la fin de mon "tableau" à la ligne 193 000, soit tout en bas de la feuille de calcul à la ligne ... en fonction de la colonne homonyme dans laquelle je me place. Donc je ne sais pas trop comment faire.

Au final, dès que j'applique une formule à l'intégralité de ma feuille, tout rame, ca ferme excel tout seul, je recommence 20 fois la même action avant que ca fonctionne sans que je sois obligé der fermer excel qui ne répond plus. (Ca le fait un peu pour les noms anglais, mais ca fonctionne quand même bien).

Mais la macro en elle même correspond à mon besoin. Je dois juste rajouter une colonne vide à la fin.

J'aimerais juste être sûr que j'ai besoin de la lancer 1 seule fois, et non 4 fois pour ne plus avoir de doublons comme c'est le cas dans le fichier EN, sinon je ne pourrais jamais y arriver. (En espérant que j'arrive au moins à la faire aboutir 1 fois vu comme ca plante). J'ai un lenovo 8GO de mémoire vive, ca ne doit pas être suffisant. D'ailleurs depuis 2 jours, à force de lui faire des "calculs" aussi gros, quand je reste appuyé sur les flèches pour me déplacer dans mes fichiers, l'ordi fait un bruit strident (qui n'est pas lié au son qui est coupé), quand je relâche la touche, le bruit s'arrête. Quand je lance la macro aussi ca me fait ca par moment, il tourne, et d'un coup il me fait un petit bruit strident qui "s'essoufle" au bout de quelques minutes.

4) Ensuite je vais devoir faire pareil avec les synonymes qui sont encore plus nombreux

La macro fonctionne pour faire le boulot.

Le but final final, c'est que j'ai un autre fichier excel, lorsque je rentre un nom de plante que je trouve en lien avec le sujet que je traite actuellement, ca me donne toutes les informations dont j'ai besoin : synonymes, homonymes, noms FR, noms EN + localisation + réglementation ... (sachant que la localisation et la réglementation, je vais chercher les informations dans d'autres fichiers excel, qui utilise la majorité du temps le nom latin validé, mais pas toujours, des fois ils n'ont pas encore mis à jour c'est pourquoi j'ai besoin des homonymes, synonymes dans des fichiers à part (sinon ca va trop buguer) (avec 1 colonne pour chacun sans doublons) pour faire des recherches dans ces autres fichiers excel à partir de ces autres noms ...

J'espère que l'objectif est plus clair pour toi, pour comprendre le but et l'intérêt.

Merci beaucoup.

Le texte et les infos sont un peu longues mais j'ai essayé de décrire au mieux mes soucis ^^

J'ai réussi à le lancer pour les homonymes. J'ai supprimer les "" avec Power Query. J'ia pu lancer. C'est long, ca prend la nuit mais ca fonctionne. Toujours avec les beug : rajout d'une colonne et relancer plusieurs fois car il y a des multiplications de mots.

Une idée pour concaténer en conservant les couleurs de mots ?

Merci infiniment !