Transfert et tri de données entre 2 feuilles Excel

Le 02/08/2012 à 12:03

Bonjour,

j'aimerai transférer et reformater/trier des données entre 2 feuilles Excel de manière automatique.

Actuellement je part d'une feuille de "données brutes" organisée en 4 colonnes (marker/sample/value1/value2), que je voudrais déplacer sur une autre feuille "bilan" de manière plus visuelle en vrai tableau. J'effectue cette tache en triant puis en copiant/collant dans la feuille de destination mais ça peut être très long.

Voyez vous un moyen plus automatique de faire ceci ?

ci-joint un fichier d'exemple

En vous remerciant

253exemple01.zip (36.60 Ko)

Le 02/08/2012 à 13:17

Bonjour,

Vois le fichier et clique sur le bouton.

Si ok, merci de cloturer le fil en cliquant sur le V vert à coté du bouton EDITER

Amicalement

651exemple02.zip (45.39 Ko)

Le 02/08/2012 à 14:23

C'est exactement ça!

En mieux que ce que j'imaginais!

Mais pourrais tu m'expliquer comment tu arrive à ce résultat, car j'aimerai pouvoir le reproduire par la suite...

J'ajoute que si tu as une solution pour transformer par la même les valeurs "Bleu 142" en 142 par exemple tu deviendrai un dieu vivant du vba aux yeux de la modeste quiche que je suis

Encore merci.

Le 02/08/2012 à 15:53

re,

Remplace le code SUB TEST par celui ci-dessous puis clique sur bouton

Sub test()
'Macro Dan
Dim dlg As Integer, i As Integer, lig As Integer
Dim col As Byte, c As Byte
With ActiveSheet
dlg = .Range("A65536").End(xlUp).Row
For i = 2 To dlg
On Error Resume Next
col = WorksheetFunction.Match(.Range("A" & i), Sheets("Bilan").Range("A1:W1"), 0)
lig = WorksheetFunction.Match(.Range("B" & i), Sheets("Bilan").Columns("A"), 0)
    If col > 0 And lig > 0 Then

        Sheets("Bilan").Cells(lig, col + 1) = .Range("D" & i)
            Select Case UCase(Left(.Range("C" & i), InStr(Left(.Range("C" & i), 10), " ") - 1))
                Case "BLEU": c = 5
                Case "VERT": c = 4
                Case "ROUGE": c = 3
                Case Else: c = -4142
            End Select
            If c > 0 Then
                Sheets("Bilan").Cells(lig, col) = Mid(.Range("C" & i), InStrRev(.Range("C" & i), " ") + 1, 99)
            Else: Sheets("Bilan").Cells(lig, col) = .Range("C" & i)
            End If
            Sheets("Bilan").Cells(lig, col).Font.ColorIndex = c
            c = 0
            Select Case UCase(Left(.Range("D" & i), InStr(Left(.Range("D" & i), 10), " ") - 1))
                Case "BLEU": c = 5
                Case "VERT": c = 4
                Case "ROUGE": c = 3
                Case Else: c = -4142
            End Select
            If c > 0 Then
                Sheets("Bilan").Cells(lig, col + 1) = Mid(.Range("D" & i), InStrRev(.Range("D" & i), " ") + 1, 99)
            Else: Sheets("Bilan").Cells(lig, col + 1) = .Range("D" & i)
            End If
            Sheets("Bilan").Cells(lig, col + 1).Font.ColorIndex = c

    End If
    c = 0
Next
End With
End Sub

Oublie pas de cloturer le fil lors de ta réponse... (si ok bien sûr)

Amicalement

Le 02/08/2012 à 16:10

C'est énorme!!

Je clôture

Le 03/08/2012 à 13:57

Re-bonjour,

J'étudie avec soin le code vba que Dan m'as fait pour mieux comprendre tout ça.

A l'aide des cours du site et de mon ami google, j'ai à peu près tout saisi, par contre je ne comprends pas la valeur d'itération dans la boucle

For i = 2 To dlg

Je me suis dis que le 2 était lié au fait que j'avais 2 valeurs différentes (allele1 et allele1) pour un même sample, mais si je remplace le 2 par 1 je n'observe aucune différence dans mon résultat final...

Quelqu'un pourrait il m'éclairer?

merci.

Edit: je crois que j'ai trouvé tout seul: 2 n'est pas un nombre d'itération mais le n° de la ligne de départ à partir de laquelle la boucle s'opère, si je mets 1 la boucle commence sur le titre--->erreur---> resume next---> 2 et ça marche....

Le 22/08/2012 à 11:05

Je suis de retour vers vous...

J'ai commencé à travailler avec cette macro et je me suis rendu compte d'un bug/problème: si il manque un sample name sur la feuille cible "bilan", les valeurs remplacent celles du "sample name" précédent.

En clair, si j'oublie de mettre le sample 102 du tableau cible, les valeurs du sample 101 sont correctement transmisent puis remplacées par les valeurs du 102. Les autres Samples ne sont pas impactées.

La macro en détail:

Sub Formatage_données_Genemapper2()

'Macro Dan

If MsgBox("Voulez vous importer vos données de génotypage ?", vbYesNo, "Confirmation") = vbNo Then Exit Sub

Dim dlg As Integer, i As Integer, lig As Integer

Dim col As Byte, c As Byte

With Sheets("Données brutes")

dlg = .Range("A65536").End(xlUp).Row

For i = 2 To dlg

On Error Resume Next

col = WorksheetFunction.Match(.Range("A" & i), Sheets("Bilan").Range("A1:BA1"), 0)

lig = WorksheetFunction.Match(.Range("B" & i), Sheets("Bilan").Columns("A"), 0)

If col > 0 And lig > 0 Then

Sheets("Bilan").Cells(lig, col + 1) = .Range("D" & i)

Select Case UCase(Left(.Range("C" & i), InStr(Left(.Range("C" & i), 10), " ") - 1))

Case "BLEU": c = 5

Case "VERT": c = 4

Case "ROUGE": c = 3

Case Else: c = -4142

End Select

If c > 0 Then

Sheets("Bilan").Cells(lig, col) = Mid(.Range("C" & i), InStrRev(.Range("C" & i), " ") + 1, 99)

Else: Sheets("Bilan").Cells(lig, col) = .Range("C" & i)

End If

Sheets("Bilan").Cells(lig, col).Font.ColorIndex = c

c = 0

Select Case UCase(Left(.Range("D" & i), InStr(Left(.Range("D" & i), 10), " ") - 1))

Case "BLEU": c = 5

Case "VERT": c = 4

Case "ROUGE": c = 3

Case Else: c = -4142

End Select

If c > 0 Then

Sheets("Bilan").Cells(lig, col + 1) = Mid(.Range("D" & i), InStrRev(.Range("D" & i), " ") + 1, 99)

Else: Sheets("Bilan").Cells(lig, col + 1) = .Range("D" & i)

End If

Sheets("Bilan").Cells(lig, col + 1).Font.ColorIndex = c

End If

c = 0

End With

End Sub

Comment faire pour intégrer une ligne d'erreur pour qu'en cas de sample name ou de Marker non trouvé un message d'erreur apparaisse ? (j'ai essayé moi même mais je tourne en rond sur diverses erreur).

Est il possible de faire en sorte que le formatage du tableau de la feuille bilan se fasse automatiquement en fonction des éléments de la feuille donnée brute?

Ci-joint un fichier test, le bouton lançant la macro est la flèche rouge en haut à gauche de la feuille bilan

Un grand merci par avance

79fichier-test.zip (10.88 Ko)

Le 22/08/2012 à 12:51

Re,

En clair, si j'oublie de mettre le sample 102 du tableau cible, les valeurs du sample 101 sont correctement transmisent puis remplacées par les valeurs du 102. Les autres Samples ne sont pas impactées.

Corrige le code en ajoutant simplement ces deux instructions en fin de macro juste après "c=0"

lig = 0
col = 0

Est il possible de faire en sorte que le formatage du tableau de la feuille bilan se fasse automatiquement en fonction des éléments de la feuille donnée brute?

Une manière de faire est de jouer sur l'activation de la feuille Cible

clique droite sur l'onglet CIBLE
Choisis "Visualiser le code"
Colle le code ci-après dans la fenêtre

Private Sub Worksheet_Activate()
Call Formatage_données_Genemapper2
End Sub

Si ok, oublie pas de cloturer le fil...

Amicalement

Le 23/08/2012 à 09:44

Merci, la première correction et parfaitement efficace.

Par contre je vois pas comment le 2e code va me créer mon tableau, d'ailleurs ça fonctionne pas (j'ai afficher le code de la feuille bilan et j'ai collé la private sub...c'est bien ça?).

En fait j'imaginais plus un code du genre:

regarde le nom dans la colonne sample name et va le mettre dans la colonne sample name de la feuille bilan
ne pas tenir compte des répétitions (pour ne l'avoir qu'une fois)

La mise en place des lignes marker/alelle1/alelle2 est un détail par contre le transfert des samples names pourrait valoir le coup pour éviter des erreurs.

Je creuse un peux la question et je reviens ....

Le 23/08/2012 à 11:18

Bon j'ai revu mes objectifs...un peu à la hausse.

Je voudrais éviter de rentrer manuellement les informations "sample file" et "sample name" dans la feuille "bilan" et les récupérer automatiquement dans la feuille "données brutes"

Je voudrais en fait récupérer l'information "sample file" de la feuille "données brutes" et la transférer une seule fois dans la feuille "bilan" en faisant suivre le "sample name" qui est associé.

Une fois les transferts des "sample" effectués le reste de la macro pourra s'effectuer.

Un fichier exemple en pièce jointe, le bouton de macro est toujours la flèche rouge en A1

Mon problème est en fait de transférer une seule fois dans la colonne cible l'information "Sample file/name" qui est répétée plusieurs fois dans la colonne source (1 fois pour chaque "marker" en fait...).

mes piètres tentatives me font fait remplir toutes la colonne avec les sample file successivement jusqu'au dernier qui a fini par se dupliquer jusqu'en bas de feuille bref je mouline...

merci.

84fichier-test2.zip (11.72 Ko)

Le 23/08/2012 à 12:53

Re,

Par contre je vois pas comment le 2e code va me créer mon tableau, d'ailleurs ça fonctionne pas (j'ai afficher le code de la feuille bilan et j'ai collé la private sub...c'est bien ça?).

Une fois le code placé dans la feuille Bilan, clique sur la feuille Données puis clique sur la feuille Bilan

A te relire

edit : ton dernier fichier n'est identique au précédent.

Merci de confirmer quel sera le bon fichier

Le 23/08/2012 à 13:55

En fait la private sub, une fois mise dans la feuille bilan, lance la macro quand je passe de la feuille donnée à la feuille bilan, mais elle ne permet pas de créer automatiquement le tableau de la feuille bilan.

Ce que je voudrais c'est que la macro récupère elle même les samples file/name du tableau bilan. depuis la feuille données brutes.

Mon fichier "Test2", n'est pas tout à fait identique, la colonne "Sample file" a remplacé la "Sample name" pour que la fonction match travaille dessus.

La colonne Sample name devient une données secondaire qui doit être transféré mais qui ne sert pas à proprement dit

Sur ce fichier la macro est fonctionnelle si je copie/colle les "samples files" manuellement, mais je voudrais l'automatiser.

Il n'y a pas besoin de réécrire toute la macro, juste rajouter une étape préalable qui transfère les colonnes "B et E données brutes" vers les colonnes "A et B bilan".

C'est le juste qui coince...

EDIT: je progresse, j'ai rajouté ceci avant le "on error resume next"

A = .Range("B65536").End(xlUp).Row

With .Range("b1:b" & A)

.AdvancedFilter Action:=xlFilterCopy, _

CopyToRange:=Sheets("Bilan").Range("A2"), Unique:=True

End With

Du coup mes "sample files" sont extraits et copiés dans la feuille bilan et la macro peut s'appliquer.

Mais je ne sais pas si c'est la bonne méthode car j'ai l'impression que ça travaille beaucoup...

Edit2: ça travaille beaucoup trop, j'ai pas du le placer au bon endroit

Il faut aussi que je fasse suivre les samples names...

24fichier-test2.zip (11.72 Ko)

Le 23/08/2012 à 16:07

re,

Essaie ce fichier.

Pour l'exécution du code, si la feuille Données Brutes n'est pas active, sélectionne la puis clique sur l'onglet BILAN.

Si tu apportes un changement sur la feuille Données brutes, le fait de cliquer sur la feuille BILAN remettra tout à jour.

Amicalement

59fichier-test3.zip (16.77 Ko)

Le 23/08/2012 à 16:21

Merci, c'est nettement plus efficace que la misère que j'ai pondu...

Il y a juste un petit "hic": d'un point de vue pratique je suis amener à naviguer d'une feuille à l'autre pour divers controles mais je ne veux pas que la macro se relance à chaque fois.

D'où la question ultime: est il possible de combiner ce 2e code avec le premier?

Je vais creuser la question, si c'est trop compliqué, je ferai un 2e bouton avec ce code, de telle sorte, un clic pour importer les sample et un clic pour importer et formater les données....

Dans tous les cas un grand merci à ta patience et à ton dévouement

Le 23/08/2012 à 16:43

Conclusion: j'ai crée une SUB globale qui "Call" les 2 autres et ça marche au poil

Rechercher des sujets similaires à "transfert tri donnees entre feuilles"