Transformer un tableau à nb de colonnes variables en une base propre

Bonjour, je dispose d'un tableau excel qui liste 200 familles. Les intitulés de colonne sont NOM puis PRENOM1, PRENOM2... jusque PRENOM7 (pour les familles très nombreuses avec 2 parents et 5 enfants !). Si la famille est composée de 4 membres, alors les colonnes PRENOM5, PRENOM6 et PRENOM7 restent vides. Comment puis-je transformer automatiquement ce tableau en une base propre qui liste chaque personne avec son NOM et son PRENOM ? En pièce jointe mon exemple. Merci beaucoup pour votre aide, Alexis.

Bonjour,

quelle est ta version d'excel ? tu peux éventuellement utiliser PowerQuery pour cette transformation

... sinon, voici une macro assez simple

Sub fam()
Set f1 = Sheets("Sheet1")
Set f2 = Sheets("Sheet2")
f2.Select

k = 2
With f1
    For i = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
        For j = 2 To .Cells(i, Columns.Count).End(xlToLeft).Column
            Cells(k, 1) = .Cells(i, 1)
            Cells(k, 2) = .Cells(i, j)
            k = k + 1
        Next
    Next
End With

End Sub

bonjour

un essai par formules dans plages nommées en dynamique

cordialement

Merci à vous deux pour vos réponses.

@steelson : j'utilise excel 2010 en version anglaise.

Je vous joins une version un peu plus compliquée où il y a des colonnes qui ne me sont pas utiles.

Savez-vous m'aider à obtenir le même résultat ?

Si utile, je vous joins le fichier réel avec les colonnes à extraire en couleur jaune. Merci à nouveau pour votre aide précieuse !

Ce qui est curieux, c'est qu'il n'y a pas de logique dans cette suite !

Et bien j'ai réalisé que vos deux réponses fonctionnent bien dans mon exemple "familles.xlsx". Mais dans mon vrai fichier de travail les colonnes PRENOM1 à PRENOM7 ne sont pas adjacentes, elles sont séparées par d'autres champs (comme ADRESSE, VILLE, TELEPHONE, etc...) c'est pour cela que j'essaie de mieux comprendre comment fonctionnent les deux solutions que vous m'avez proposées. C'est aussi pour cela que j'ai posté une 2e réponse avec le vrai fichier de travail ! Merci, Alexis

Tu trouveras ici les triplés gagnants (les colonnes où se trouvent nom, prénom, date de naissance) et le test est fait sur l'existence du prénom.

donnees = Array("14,15,22", "14,39,42", "14,45,47", "14,48,50", "14,52,53", "14,54,55", "14,56,57")

Sub fam()
Set f1 = Sheets("raw_NSC")
Set f2 = Sheets("Feuil1")

donnees = Array("14,15,22", "14,39,42", "14,45,47", "14,48,50", "14,52,53", "14,54,55", "14,56,57")

    f2.Select
    If Not f2.ListObjects(1).DataBodyRange Is Nothing Then f2.ListObjects(1).DataBodyRange.Delete

    k = 2
    With f1.ListObjects(1)
        For i = 1 To .ListRows.Count
            With .DataBodyRange
                For Each donnee In donnees
                tbl = Split(donnee, ",")
                    If .Cells(i, tbl(1) * 1) <> "" Then
                        Cells(k, 1) = .Cells(i, tbl(0) * 1)
                        Cells(k, 2) = .Cells(i, tbl(1) * 1)
                        Cells(k, 3) = .Cells(i, tbl(2) * 1)
                        k = k + 1
                    End If
                Next
            End With
        Next
    End With

End Sub

Merci BEAUCOUP !

Steelson a écrit :

Tu trouveras ici les triplés gagnants (les colonnes où se trouvent nom, prénom, date de naissance) et le test est fait sur l'existence du prénom.

donnees = Array("14,15,22", "14,39,42", "14,45,47", "14,48,50", "14,52,53", "14,54,55", "14,56,57")

Sub fam()
Set f1 = Sheets("raw_NSC")
Set f2 = Sheets("Feuil1")

donnees = Array("14,15,22", "14,39,42", "14,45,47", "14,48,50", "14,52,53", "14,54,55", "14,56,57")

    f2.Select
    If Not f2.ListObjects(1).DataBodyRange Is Nothing Then f2.ListObjects(1).DataBodyRange.Delete

    k = 2
    With f1.ListObjects(1)
        For i = 1 To .ListRows.Count
            With .DataBodyRange
                For Each donnee In donnees
                tbl = Split(donnee, ",")
                    If .Cells(i, tbl(1) * 1) <> "" Then
                        Cells(k, 1) = .Cells(i, tbl(0) * 1)
                        Cells(k, 2) = .Cells(i, tbl(1) * 1)
                        Cells(k, 3) = .Cells(i, tbl(2) * 1)
                        k = k + 1
                    End If
                Next
            End With
        Next
    End With

End Sub

11familles-v3.xlsm (24.44 Ko)

ha oui avec array putter

@Steelson,

Encore une petite question SVP : quand on fait tourner votre macro, le résultat est un tableau positionné sur la cellule A1. Si j'insère une colonne vide à gauche ou bien une ligne vide en haut, la macro continue à "produire" un tableau en A1. Comment faire pour décaler le tableau, par exemple en cellule B2 ?

Merci, Alexis

Dans ce cas, il faut en effet adopter un code plus structuré comme ceci (tu peux positionner ton tableau où tu veux sur la feuille).

Sub fam()

Set f1 = Sheets("raw_NSC")
Set tbl1 = f1.ListObjects(1)
Set f2 = Sheets("Feuil1")
Set tbl2 = f2.ListObjects(1)

donnees = Array("14,15,22", "14,39,42", "14,45,47", "14,48,50", "14,52,53", "14,54,55", "14,56,57")

    f2.Select
    If Not tbl2.DataBodyRange Is Nothing Then tbl2.DataBodyRange.Delete

    With tbl1
        For i = 1 To .ListRows.Count
            With .DataBodyRange
                For Each donnee In donnees
                tbl = Split(donnee, ",")
                    If .Cells(i, tbl(1) * 1) <> "" Then
                        tbl2.ListRows.Add
                        k = tbl2.ListRows.Count
                        tbl2.DataBodyRange.Cells(k, 1) = .Cells(i, tbl(0) * 1)
                        tbl2.DataBodyRange.Cells(k, 2) = .Cells(i, tbl(1) * 1)
                        tbl2.DataBodyRange.Cells(k, 3) = .Cells(i, tbl(2) * 1)
                    End If
                Next
            End With
        Next
    End With

End Sub

Encore merci à vous deux, super utile, Alexis