Comparer toute les lignes d'un tableau excel

Question

Bonjour,


J'ai un ensemble de donnée que je cherche a comparer les unes avec les autres et je ne trouve aucun formule correspondante sous excel, on ma conseil de me rabattre sur les macro mais je n'ai aucune connaissance dans ce domaine donc j'en appel a vous.

Mes données sont des génotypes a comparer (biologie), se présentant dans un tableau excel fait par mes soins ^^

Le format du tableau et de ce type

Nom des individus                   en colonne A à partir de A2
Génotype premier gene          colonne B et C à partir de B2 et C2 

avec 6genes étudiés donc au totale 12colonnes a comparer entre chaque individus sur environ 200 individus.

Le but serait de comparer chaque individus a tous les autres et de me signaler en couleur par exemple les individus ayant les même valeur pour tous les génotypes en même temps ...

Comme je doute d'etre trés clair je vous donne un exemple concret :

Nom  géno1        Géno2        Géno3        Géno4      Geno5       Géno6
ind1  106 106     230 230     301 304     110 105    243 244      321 367
ind2  107 106     236 230     301 304     110 109    243 244      321 367
ind3  106 106     230 230     301 304     110 105    243 244      321 367


Les individus 1 et 3 sont identiques donc il faudrait me les mettre en couleur.


Voila j'espère que c'est assez clair comme cela.


Merci a vous.

lili1678 · Answer

il y a un article dans micro actuel de ce mois ci qui traite de ton problème, ils parlent de mise en forme conditionnelle

lili1678 · Answer

pour créer la condition, il faut passer par format-mise en forme conditionnelle

Sieg · Answer

La mise en forme et la coloration des données n'est que superflu, la base du soucis c'est de comparer chacune de mes lignes de données avec l'ensemble de mon tableau.
Et si ca répond à ma question est-ce que tu pourrais etre plus explicite parce que je ne vois pas en quoi une mise en forme auto maiderais a comparer des données...


Il faut en fait que je comparer la ligne 2 d'un tableau avec un ensemble de ligne (jusqu'a 151 pour l'instant mais on l'espère prochainement sur un millier voir plus ...)

lili1678 · Answer

http://www.cathyastuce.com/excel/condition.php

Fxbrg · Answer

Hello,

Tu peux mettre en ligne un exemple de ton fichier? (cijoint.fr)

Tu veux que cette identification par couleur se fasse à la volée? c-à-d quand tu saisis un gène? Ou bien tu veux saisir puis lancer la recherche de concordances?

Dans tous les cas, il va falloir passer par une macro, si tu mets ton fichier en ligne, je m'y colle :-)

Sieg · Answer

J'ai essayer avec la mise en forme, effectivement ca me signale les données identiques mais ca ne m'avance pas car ca ne compare qu'une cellule a une autre (a moins de ne pas avoir bien comprit).

Il me faut comparer les 12cellules a la fois et seulement si les 12cellules sorte de maniere identique dans ma base de donnée alors il faut qu'il me le signale. Ce n'est pas une comparaison case par case dont j'ai besoin mais comparer les 12cases de génotype de chaque individus avec les 12cases de génotype de chacun des autres individus.

La mise en forme conditionnelle m'aide pour la lecture manuelle mais j'aimerais trouver une méthode vraiment automatisé de comparaison de l'ensemble de mes donnée simultanéement, sachant que le nombre d'individus va augmenter ainsi que le nombre de génotype ...

 Merci de ton aide en tout cas :)

Sieg · Answer

Voila une partie de mes données en exemple avec la mise en forme qu'ils doivent avoir :

http://www.cijoint.fr/cjlink.php?file=cj200802/cij11118932045752.xls.

et je pencherais plutot pour une saisie totale de toute mes infos puis une recherche de concordance.

Merci de ton aide ^^

Fxbrg · Answer

Ca me met "fichier inaccessible" sur ton lien, tu veux bien vérifier?

Sieg · Answer

Je retente le lien n'ayant pas trouver l'icone d'édition ^^


http://www.cijoint.fr/cjlink.php?file=cj200802/cij11118932045752.xls

Fxbrg · Answer

Ca marche...

Question : cherches-tu un seul couple? peut-il y avoir plus de deux lignes concordantes? peut-il y avoir plusieurs groupes de lignes concordantes?

:-)

Fxbrg · Answer

Hello :-)

regarde ça : http://www.cijoint.fr/cjlink.php?file=cj200802/cij6851484335735.xls

et dis-moi ce que tu en penses :-)

Fxbrg · Answer

Oops, j'ai mis un mauvais lien, voici le bon : 

http://www.cijoint.fr/cjlink.php?file=cj200802/cij10953692345719.xls

:-)

Fxbrg · Answer

Pour info, ce petit bout de code ne fonctionne que si tu n'as que des paires... Si tu peux avoir trois lignes ou plus identiques, il faut le modifier...

:-)

Fxbrg · Answer

Voici la version qui accepte tous les cas de figure, plus un bouton de reset des couleurs pour le fun :-)

http://www.cijoint.fr/cjlink.php?file=cj200802/cij10953692345719.xls

Fxbrg · Answer

Arghh... impossible de mettre la version finale du cij... Si tu as un mail, je te l'envoie en direct :-) le fichier fait 32ko..

@+,

JF

Fxbrg · Answer

Le fichier est parti...

Pour voir le code : clic droit sur l'onglet de ta feuille, puis visualiser le code...

Il y a deux boutons, chacun a son code : le premier remet le fond en blanc...

Le second est plus malin :-)

On crée deux variables : conc1 et conc2.

Ensuite on part de la fin de la feuille (usedrange.rows.count) et on remonte (step -1) jusqu'à la ligne 2 (to 2)

A chaque ligne, on affecte conc1 comme étant une concaténation de toutes les cellules de la ligne (cells(1,2)&...cells(i,13)
Ensuite on balaie tout le tableau, en partant de la ligne juste au dessus jusqu'à la ligne 2, on affecte à la seconde variable conc2 la concaténation de la ligne observée, puis on compare. Si conc1=conc2, on donne aux deux lignes la mêmes couleur...

Et on recommence jusqu'à la fin... :-)

Ce code est indépendant du nombre de lignes de ton tableau. Tu peux avoir X occurences de lignes identiques, qui seront de la même couleur. Chaque nouvelle association prend une nouvelle couleur.

Dis moi si ça gaze :-)

Sieg · Answer

Visuellement ca marche et c'est tréssssssss exactement ce que je voulais, malheureusement quand je ccopie-colle l'ensemble de mes données j'ai un message d'erreur ... 

Erreur d'execution 1004

Impossible de définir la propriété ColorIndex de la classe Interior


Et en cliquant bien évidemment sur débogage j'ai le droit a une surlignement de cette ligne :

Range(Cells(i, 2), Cells(i, 13)).Interior.ColorIndex = i


Voila si tu as une idée je suis prenneur ^^

Fxbrg · Answer

Il est possible que selon le nombre de lignes, le i soit trop grand ou ne corresponde pas à une couleur... Combien as-tu de lignes dans ton tableau?

Sieg · Answer

Pour le moment 151 ligne différent (en partant de la 2eme ...) mais au final je risque de passer les 1000 voir plus avec un peu de chance.

Fxbrg · Answer

Copie ça en lieu et place du code du bouton 1 : 

Private Sub CommandButton1_Click()
Dim conc1 As Double, conc2 As Double
Dim n As Integer, i As Integer, j As Integer, k As Integer
j = 2
For i = UsedRange.Rows.Count To 2 Step -1
    conc1 = Cells(i, 2) & Cells(i, 3) & Cells(i, 4) & Cells(i, 5) & Cells(i, 6) & Cells(i, 7) & Cells(i, 8) & Cells(i, 9) & Cells(i, 10) & Cells(i, 11) & Cells(i, 12) & Cells(i, 13)
    k = 0
    If Cells(i, 2).Interior.ColorIndex = -4142 Then
    For n = 1 To i - 2
        conc2 = Cells(i - n, 2) & Cells(i - n, 3) & Cells(i - n, 4) & Cells(i - n, 5) & Cells(i - n, 6) & Cells(i - n, 7) & Cells(i - n, 8) & Cells(i - n, 9) & Cells(i - n, 10) & Cells(i - n, 11) & Cells(i - n, 12) & Cells(i - n, 13)
        If conc1 = conc2 Then
            Range(Cells(i, 2), Cells(i, 13)).Interior.ColorIndex = j
            Range(Cells(i - n, 2), Cells(i - n, 13)).Interior.ColorIndex = j
            k = 1
        End If
    Next
    End If
    If k = 1 Then j = j + 1
Next
End Sub

et dis-moi comment ça se passe...

Je dois partir, serai de retour plus tard ce soir...

@+,

JF

Fxbrg · Answer

Petit détail : mets j=3 à la ligne 4, pour éviter la couleur blanche...

Ca devrait fonctionner avec 150 lignes, mais pour mille ou plus, donc un grand nombre de correspondance, ça va coincer, vu que la palette de couleur n'est pas infinie...Il faudra peut-être utiliser un code couleur Et un code numérique (en colonne N par exemple, mettre un nombre...

C'est un premier pas, à améliorer :-)

Bon courage,

JF

Fxbrg · Answer

Je viens de regarder, il y a 58 couleurs dans la palette, mais certaines sont inutilisables (blanc, noir et autres très foncées), bref, ça laisse une petite cinquantaine de couleurs différentes, il ne faudrait donc pas qu'il y ait plus d'association que ça...sinon il faudra trouver une alternative (changer la police par exemple en plus de la couleur par exemple, ce qui multiplie le choix par trois)

@+,

JF

Raymond PENTIER · Answer

Tout ça est bien trop compliqué pour moi.
J'aurais fait, comme l'indique Vaucluse au post 5, une concaténation en colonne 14, puis un tri par rapport à cette dernière ; avec une petite formule en colonne 15 du style =SI(N3=N2;"Doublon";"Unique") recopiée jusqu'en bas.
Et si vraiment tu veux de la couleur, tu bases ta condition sur le mot affiché colonne 15. Une seule couleur suffit !
Ce n'est pas valable ?

Fxbrg · Answer

Salut Raymond et Vaucluse,

Les 58 couleurs (ou 50 utilisables) sont des couleurs "primaires", relativement facilement identifiables... Pour les voir, créer un bouton dans une feuille vierge et coller ce code derrière le bouton : 

Private Sub CommandButton2_Click()
    Dim n As Integer
    For n = 0 To 56
        ActiveCell.Offset(n, 0) = n
        ActiveCell.Offset(n, 0).Interior.ColorIndex = n
    Next
End Sub

Ceci dit, je suis assez d'accord avec vous, il faudra peut-être regrouper les groupes de lignes identiques sur une autre feuille, par ordre à définir... Mais ça c'est Sieg qui va le dire :-)

sieg · Answer

désolé de pas suivre plus que ca mais c'est qu'une partie de mon boulot ^^


Donc j'ai tester la macro d'hier, avec l'ensemble du fichier ca avait l'air de marcher et donc j'ai pas insister hier soir, par contre en re-regardant ce matin je me suis rendu compte que les comparaison sont pas génial, visiblement il ne compare pas toutes les colonnes de chaque ligne vu qu'il me trouve des doublons qui n'en sont pas (juste 1ou 2 génotype différent, soit 1 a 2cases différentes mais quand même ^^)

Par contre on ma aussi fournit une macro appeller GenAlex qui visiblement a été fait spécialement pour le génotype biologique ... bon ca a pas l'air évident et je pense bien trop pousser pour ce que je veux (ca semble combiner des étude statistiques et des ratio d'erreur pour chaque valeur ce qui me donnerais plus d'informtaion a traiter qu'au départ  ^^).


Je devais vraiment pas etre au mieux de mon attention hier car je viens seulement de comprendre l'idée de concatenée les valeurs et de trier, oui ca ne donne pas de couleur mais il y a peut-etre possibilité de regler la question.
Je vais m'essayer a ca tout de suite et je vous tient au courant.


Merci de votre aide encore une fois ^^

sieg · Answer

Ah désolé oublier de préciser que le nombre de couleur a pas d'influence, je ne suis pas du tout sencer en retrouver en fait, chaque doublon est une possible erreur de manip ou alors de lecture et d'interprétation de mes donées brutes (avant le fichier excel ^^)

Donc je l'espère même sur 1000individus je ne devrais pas avoir plus de ... 15-20doublons maxi ... enfin j'espère ^^

Raymond PENTIER · Answer

Suite à mon post 25, voici un fichier de démonstration : https://www.cjoint.com/?cCrIzwk82E

Fxbrg · Answer

Salut :-)

Tu peux me faire passer (ou mettre ici) un exemple de doublon trouvé qui n'en est pas? Il doit y avoir une erreur de frappe quelque part, sinon il n'y a pas de raison qu'il ne trouve pas les doublons...:-) Ou alors, comme dans ton fichier exemple, tu as des cases vides, il se peut que la concaténation donne une valeur identique alors que les cases sont différentes...

15/20 couleurs c cool... :-)

Fxbrg · Answer

Salut Raymond,

Ton fichier est cool et simple, mais comme dit Sieg plus haut, il se peut qu'il ait des triplons ou pire, et il se peut aussi que deux paires de doublons soient à la suite, ce qui, une fois triés, les mettraient tous à la suite, pas facile à lire quoi est égal à quoi... :-)

Et si les doublons trouvés qui n'en sont pas sont liés aux cases vides, le problème sera le même, macro ou directement dans la feuille. J'attends de voir la réponse de Sieg...

:-)

sieg · Answer

Bon alors visiblement la combinaison concaténasion + formule si comme nous la donner Raymond fonction pas trop mal, maintenant il me suffirait juste de réussir a renvoyer les noms des individus ayant le même génotype (le noms des doublons quoi) dans une autre feuille excel, ca m'éviterais d'avoir a faire défiler toute ma liste ...

Vous voyez une possibilitée ? en cherchant dans les formules excel je trouve rien qui me permette de lier une case en fonction d'une condition validé dans une cellule d'une colonne ...

Par exemple ici j'ai gentillement demander qu'il me note le nom des deux individus ... bon en fait voila la formule que j'ai utilisé :
=SI(T5=T4;SI(T5="";"";(CONCATENER(A4;" ";A5;" Doublon")));SI(T5="";"";"Unique")) 

T car j'ai compter en prennant 24génotype soit 12cases appeller locus qui contient chacun deux génotypes ^^, maintenant c'est beau mais ca m'oblige a défiler, il n'est pas possible de renvoyer sur la feuille2 les valeurs A4 A5 de mon exemple si la case ou est écrite la formule contient déja A4 A5 doublon ???



Je travail a lier le fichier pour te montrer les lieux d'erreur.

sieg · Answer

Vous etes trop réactif ^^


Oui effectivement je n'ai pas pensé a vérifier si en triant puis avec la formule SI il arriverais a me détecter des triplons voir + et même des doublons accolé ... je dois vérifier ca encore, je te lie le fichier dans ma prochaine réponse pour que tu puisse te faire une idée du probleme.

sieg · Answer

Voila les lignes déja colorier qui me pose probleme, je ne sais vraiment pas d'ou cela peut venir ...


vraiment navré de vous mettre autant a contribution mais la formation excel j'y ai pas eu le droit ^^ donc je bidouille avec les formules mais les macros c'est vraiment, mais vraiment pas pour moi ... 
http://www.cijoint.fr/cjlink.php?file=cj200802/cij8699252555842.xls

Fin de journée pour moi, je passerais peut-etre ce soir vous lire mais sinon bonne soirée a vous et a demain ^^

Fxbrg · Answer

Voila la bête : 

 - Les doublons qui n'en sont pas : corrigé, il s'agissait d'un mauvais format de variable (en gros, la variable valait du genre 1.00104.10 puissance 35, la comparaison ne se faisait que sur les premiers chiffres...)

 - le fichier copie maintenant, en les associant, les doublons ou triplons ou plus sur la feuille 2... Le bouton Reset remet les couleurs à 0 ET efface la feuille 2.

Voici le lien : http://www.cijoint.fr/cjlink.php?file=cj200802/cij9492483465819.xls

Dis-voir si ça roule :-)

Sieg · Answer

EXCELLENT  ^^


Désolé a Vaucluse et a Raymond qui se sont donné du mal mais je pense rester sur la macro de Fxbrg (pas fait attention si tu avais un petit nom désolé ^^)

Elle fait trés exactement ce que je veux et en plus de maniere vraiment visuelle, un vrai bonheur pour les yeux et en terme d'efficacite ...

Un grand merci a vous tous pour votre aide précieuse, pour l'instant je ne vois rien d'autre mais je pense que vous aurez un jour ou l'autre de mes nouvelles ^^, rapide et efficace ca me plait beaucoup ...


Bonne journée a vous et a bientot (enfin peut-etre pas trop tot ca voudrais dire que je suis encore embéter ^^)

Fxbrg · Answer

Cool :-)

Au moins avec toi on travaille pour la science...lol

JF

Sieg · Answer

Et oui, et même a la survie d'une espèce ..... de plante ^^.

J'ai essayer d'éviter les termes technique mais bon c'était pas gagner au départ ^^ ...

En plus pour ta satisfaction personnel tu peux savoir que ta macro devrait pas mal tourner dans le Labo ^^, et oui stoujours comme ca les besoins sont la mais peu cherche ..

Sieg · Answer

J'ai encore une petite question t'en qu'a faire ^^


J'aimerais savoir/comprendre la maniere dont la macro est construite parce que les 6locus actuels (ce que j'ai appeller locus dans la feuil excel) devrait doubler voir tripler ... Donc est-ce qu'elle prend en compte toute la ligne ou juste les colonnes jusqu'a M ?

Sieg · Answer

Oui en fait j'ai posé la question trop vite, car même sans rien connaitre a VB ca m'a pas été trop difficile d'avoir ma réponse et de savoir quoi changer et comment ...

tu prend simplement jusqu'a i, 13  suffit de pousser aussi loin qu'on veut ...

Bref derniere question est idiote désolé ^^

Fxbrg · Answer

Salut :-)

Pour l'instant, la macro ne prend en compte que les colonnes B à M. 

Voici la version qui prend en compte TOUTES les colonnes, quelles qu'elle soient, il faut donc impérativement que tu n'aies que des locus dans cette feuilles, rien d'autre, sinon les concaténations et comparaisons vont pas le faire :-)

http://www.cijoint.fr/cjlink.php?file=cj200802/cij671435795948.xls

La macro prend maintenant en charge toutes les lignes, quel que soit leur nombre, et toutes les colonnes, quel que soit leur nombre...

:-)

Sieg · Answer

Salut, 


Je dois avouer ne pas avoir tester la version "toutes colonnes" vu que j'ai toujours des annotations sur le ou les cotés, par contre et vous allez etre content j'aurais encore besoin d'un peu d'aide  ^^...

J'aimerais savoir ce qu'il faut que je change pour que ce qu'il me fait actuellement, a savoir comparer chacune des lignes avec toutes les autres, deviennent "comparer la premiere ligne avec toutes les autres mais seulement une colonne a la fois" ... Bref je suis pas trés clair donc exemple ^^

http://www.cijoint.fr/cjlink.php?file=cj200803/cij63819616210.xls

Dans le fichier les cases coloriées correspondent au fait que parmis les deux nombres present chez le descendant aucun de ceux ci ne se retrouve chez la Mère (ou plutot que ce qu'on trouve chez la Mère on le retrouve pas chez le descendant alors qu'on devrait retrouver au moins l'un des deux ...)

Donc ce que je voudrais c'est dans le même style que la macro précédente, a savoir comparer la mère a tous les descendant et si je ne retrouve pas au moins une des deux valeur pour chaque locus étudié alors me le signaler ^^


Dites voir messieurs les macrotistes c'est faisable ca ?

Fxbrg · Answer

Euh macrotiste, faudrait voir à pas faire de fote d'orthographe, que ça devienne pas maquereau triste... :-)

Sérieusement : la mère est toujours en première ligne? Qu'en est-il des cases vides? Doit-on en tenir compte?

Je m'y mets plus tard...

:-)

Fxbrg · Answer

et voila, plus simple que prévu :-)

http://www.cijoint.fr/cjlink.php?file=cj200803/cij2492645146205.xls

:-)

sieg · Answer

Salut,

Promis je vais essayer de pas faire de faute d'orthographe pour que vous finissiez pas en maquereau ... triste ou non ^^

Bon et bien visiblement ca répond a mes attentes, les cases vides ne devait pas forcément etre prises en comptent mais ca me permet de faire d'une pierre deux coups et de me montrer les individus ou il manque des informations donc c'est vraiment tout aussi bien ^^


Un grand merci encore une fois, je me plonge dans l'étude des codes pour les comprendre et si besoin de quelques chose d'approchant ne plus devoir faire appel totalement a vous ^^ ...

En attente de nouvelles difficultés ... Have fun ^^


Sieg

Fxbrg · Answer

Thanks, U2... :-)

Comparer toute les lignes d'un tableau excel

44 réponses

Discussions similaires

Newsletters