Microsoft 365 Excel VBA

mo_owen · 21 Septembre 2022

Bonjour à tous je suis novice en VBA et j'ai besoin de votre aide

Voici le fichier Excel ci joint

Il comporte trois feuilles excel. (ORIGINAL, RESULTAT ATTENDU, COPY_ORIGINAL_POUR_TEST).

Je voudrais si possible un code qui se rapproche de la suppression des doublons mais pas que. J'ai besoin d'un code qui garde le plus ancien et le plus récent dossier pour un numéro de dossier donné.

Cependant, Dans le fichier j'ai des doublons, un dossier unique, des triplons, quatriplons etc.....

Résultat attendu:
Besoin de 2 lignes (le plus ancien et la plus récent pour chaque numéro de dossier dossier en prenant en compte le code journal A) pour chaque dossier à savoir le premier dossier (le plus ancien dossier) et le dernier dossier (le plus récent) du même numéro de dossier.)

je récapitule : Si le code journal == A alors récupère le dossier le plus ancien et le plus récent; mais pour chaque numéro de dossier

Cordialement
Merci pour votre aide

chris · 25 Septembre 2022

RE

J'ai décomposé en plusieurs requête pour que tu comprennes la logique et puisse devenir autonome

mo_owen · 21 Septembre 2022

vgendron à dit:
La macro est dans le fichier...

J'ai bien vu la macro mais vu que je suis novice, j'ai oublié de signifier que je voudrais récupérer le dossier le plus ancien et le plus récent pour un meme numéro de dossier donnée. Désolé

mo_owen · 21 Septembre 2022

vgendron à dit:
et bah... c'est pas déjà le cas??
si tes données sont triées par Code puis par date...

non pour l'instant voici un fichier j'ai tester le code mais il ne récupère pas le dossier le plus ancien et le le plus récent

vgendron · 21 Septembre 2022

bon.. ce serait sympa d'arreter de dire "ca ne marche pas, je sais pas faire....."
ca donne quand meme l'impression que tu ne fais pas d'effort

1) je t'ai dit qu'il fallait trier tes données sur la colonne A, puis la colonne B==> la macro donnera donc le résultat attendu

2) dans ta feuille résultat attendu, je vois une ligne avec le dossier 4
il me semblait que tu avais dit que si on avait une seule ligne pour un dossier, alors, elle est supprimée...???

sylvanu · 21 Septembre 2022

Re,
En s'inspirant du code de Vgendron avec le tri N° puis date intégré.

chris · 21 Septembre 2022

Bonjour

Pas de MP non sollicité, merci.

C'est une solution Excel mais elle peut fonctionner dans Power BI puisque PowerQuery fait aussi partie de Power BI

Si cela peut aider voici le code de la requête

VB:

let
    Source = Excel.CurrentWorkbook(){[Name="Dossiers"]}[Content],
    Etat1 = Table.TransformColumnTypes(Source,{{"N°DOSSIER ", Int64.Type}, {"DATE", type date}, {"STATUT", type text}}),
    #"Lignes groupées" = Table.Group(Etat1, {"N°DOSSIER "}, {{"D1", each List.Min([DATE]), type nullable date}, {"D2", each List.Max([DATE]), type nullable date}}),
    #"Personnalisée ajoutée" = Table.AddColumn(#"Lignes groupées", "Filtre", each if [D1] = [D2] then null else 1),
    #"Lignes filtrées" = Table.SelectRows(#"Personnalisée ajoutée", each ([Filtre] = 1)),
    #"Supprimer le tableau croisé dynamique des autres colonnes" = Table.UnpivotOtherColumns(#"Lignes filtrées", {"N°DOSSIER "}, "Attribut", "DATE"),
    #"Colonnes supprimées" = Table.RemoveColumns(#"Supprimer le tableau croisé dynamique des autres colonnes",{"Attribut"}),
    #"Requêtes fusionnées" = Table.NestedJoin(#"Colonnes supprimées", {"N°DOSSIER ", "DATE"}, Etat1, {"N°DOSSIER ", "DATE"}, "Etat1", JoinKind.Inner),
    #"Etat1 développé" = Table.ExpandTableColumn(#"Requêtes fusionnées", "Etat1", {"STATUT"}, {"STATUT"}),
    #"Lignes triées" = Table.Sort(#"Etat1 développé",{{"N°DOSSIER ", Order.Ascending}, {"DATE", Order.Ascending}})
in
    #"Lignes triées"

Le tableau source a été mis sous forme de tableau structuré nommé Dossiers

mo_owen · 21 Septembre 2022

vgendron à dit:
bon.. ce serait sympa d'arreter de dire "ca ne marche pas, je sais pas faire....."
ca donne quand meme l'impression que tu ne fais pas d'effort

1) je t'ai dit qu'il fallait trier tes données sur la colonne A, puis la colonne B==> la macro donnera donc le résultat attendu

2) dans ta feuille résultat attendu, je vois une ligne avec le dossier 4
il me semblait que tu avais dit que si on avait une seule ligne pour un dossier, alors, elle est supprimée...???

Oui bien-sûr c'est exacte

mo_owen · 21 Septembre 2022

mo_owen à dit:
Oui bien-sûr c'est exacte

Oui bien sure que ça marche je l'ai dis et je vous ai remercié pour cela regardez en haut vous verrez

mo_owen · 21 Septembre 2022

sylvanu à dit:
Re,
En s'inspirant du code de Vgendron avec le tri N° puis date intégré.

Oui c'est exactement ce que j'ai fais merci beaucoup à vous

mo_owen · 21 Septembre 2022

sylvanu à dit:
Re,
En s'inspirant du code de Vgendron avec le tri N° puis date intégré.

Oui c'est exactement ce que j'ai fais merci beaucoup

mo_owen à dit:
Oui bien-sûr c'est exacte

Merci beaucoup pour ton code merci

mo_owen · 21 Septembre 2022

chris à dit:

Bonjour

Pas de MP non sollicité, merci.

C'est une solution Excel mais elle peut fonctionner dans Power BI puisque PowerQuery fait aussi partie de Power BI

Si cela peut aider voici le code de la requête

VB:

let
    Source = Excel.CurrentWorkbook(){[Name="Dossiers"]}[Content],
    Etat1 = Table.TransformColumnTypes(Source,{{"N°DOSSIER ", Int64.Type}, {"DATE", type date}, {"STATUT", type text}}),
    #"Lignes groupées" = Table.Group(Etat1, {"N°DOSSIER "}, {{"D1", each List.Min([DATE]), type nullable date}, {"D2", each List.Max([DATE]), type nullable date}}),
    #"Personnalisée ajoutée" = Table.AddColumn(#"Lignes groupées", "Filtre", each if [D1] = [D2] then null else 1),
    #"Lignes filtrées" = Table.SelectRows(#"Personnalisée ajoutée", each ([Filtre] = 1)),
    #"Supprimer le tableau croisé dynamique des autres colonnes" = Table.UnpivotOtherColumns(#"Lignes filtrées", {"N°DOSSIER "}, "Attribut", "DATE"),
    #"Colonnes supprimées" = Table.RemoveColumns(#"Supprimer le tableau croisé dynamique des autres colonnes",{"Attribut"}),
    #"Requêtes fusionnées" = Table.NestedJoin(#"Colonnes supprimées", {"N°DOSSIER ", "DATE"}, Etat1, {"N°DOSSIER ", "DATE"}, "Etat1", JoinKind.Inner),
    #"Etat1 développé" = Table.ExpandTableColumn(#"Requêtes fusionnées", "Etat1", {"STATUT"}, {"STATUT"}),
    #"Lignes triées" = Table.Sort(#"Etat1 développé",{{"N°DOSSIER ", Order.Ascending}, {"DATE", Order.Ascending}})
in
    #"Lignes triées"

Le tableau source a été mis sous forme de tableau structuré nommé Dossiers

Merci beaucoup pour ton code prenez tous soin de vous

mo_owen · 21 Septembre 2022

chris à dit:

Bonjour

Pas de MP non sollicité, merci.

C'est une solution Excel mais elle peut fonctionner dans Power BI puisque PowerQuery fait aussi partie de Power BI

Si cela peut aider voici le code de la requête

VB:

let
    Source = Excel.CurrentWorkbook(){[Name="Dossiers"]}[Content],
    Etat1 = Table.TransformColumnTypes(Source,{{"N°DOSSIER ", Int64.Type}, {"DATE", type date}, {"STATUT", type text}}),
    #"Lignes groupées" = Table.Group(Etat1, {"N°DOSSIER "}, {{"D1", each List.Min([DATE]), type nullable date}, {"D2", each List.Max([DATE]), type nullable date}}),
    #"Personnalisée ajoutée" = Table.AddColumn(#"Lignes groupées", "Filtre", each if [D1] = [D2] then null else 1),
    #"Lignes filtrées" = Table.SelectRows(#"Personnalisée ajoutée", each ([Filtre] = 1)),
    #"Supprimer le tableau croisé dynamique des autres colonnes" = Table.UnpivotOtherColumns(#"Lignes filtrées", {"N°DOSSIER "}, "Attribut", "DATE"),
    #"Colonnes supprimées" = Table.RemoveColumns(#"Supprimer le tableau croisé dynamique des autres colonnes",{"Attribut"}),
    #"Requêtes fusionnées" = Table.NestedJoin(#"Colonnes supprimées", {"N°DOSSIER ", "DATE"}, Etat1, {"N°DOSSIER ", "DATE"}, "Etat1", JoinKind.Inner),
    #"Etat1 développé" = Table.ExpandTableColumn(#"Requêtes fusionnées", "Etat1", {"STATUT"}, {"STATUT"}),
    #"Lignes triées" = Table.Sort(#"Etat1 développé",{{"N°DOSSIER ", Order.Ascending}, {"DATE", Order.Ascending}})
in
    #"Lignes triées"

Le tableau source a été mis sous forme de tableau structuré nommé Dossiers

Merci beaucoup pour ton code

chris · 21 Septembre 2022

RE

Finalement tu ne dis pas si tu as essayé le fichier Excel que j'avais joint et donne bien le résultat attendu sans VBA...

mo_owen · 21 Septembre 2022

chris à dit:

Bonjour

Pas de MP non sollicité, merci.

C'est une solution Excel mais elle peut fonctionner dans Power BI puisque PowerQuery fait aussi partie de Power BI

Si cela peut aider voici le code de la requête

VB:

let
    Source = Excel.CurrentWorkbook(){[Name="Dossiers"]}[Content],
    Etat1 = Table.TransformColumnTypes(Source,{{"N°DOSSIER ", Int64.Type}, {"DATE", type date}, {"STATUT", type text}}),
    #"Lignes groupées" = Table.Group(Etat1, {"N°DOSSIER "}, {{"D1", each List.Min([DATE]), type nullable date}, {"D2", each List.Max([DATE]), type nullable date}}),
    #"Personnalisée ajoutée" = Table.AddColumn(#"Lignes groupées", "Filtre", each if [D1] = [D2] then null else 1),
    #"Lignes filtrées" = Table.SelectRows(#"Personnalisée ajoutée", each ([Filtre] = 1)),
    #"Supprimer le tableau croisé dynamique des autres colonnes" = Table.UnpivotOtherColumns(#"Lignes filtrées", {"N°DOSSIER "}, "Attribut", "DATE"),
    #"Colonnes supprimées" = Table.RemoveColumns(#"Supprimer le tableau croisé dynamique des autres colonnes",{"Attribut"}),
    #"Requêtes fusionnées" = Table.NestedJoin(#"Colonnes supprimées", {"N°DOSSIER ", "DATE"}, Etat1, {"N°DOSSIER ", "DATE"}, "Etat1", JoinKind.Inner),
    #"Etat1 développé" = Table.ExpandTableColumn(#"Requêtes fusionnées", "Etat1", {"STATUT"}, {"STATUT"}),
    #"Lignes triées" = Table.Sort(#"Etat1 développé",{{"N°DOSSIER ", Order.Ascending}, {"DATE", Order.Ascending}})
in
    #"Lignes triées"

Le tableau source a été mis sous forme de tableau structuré nommé Dossiers

Merci beaucoup pour ton code

chris à dit:
RE

Finalement tu ne dis pas si tu as essayé le fichier Excel que j'avais joint et donne bien le résultat attendu sans VBA...

Désolé Chris je ne l'ai pas encore essayé je te fais le CR dans peu de temps.Merci

mo_owen · 22 Septembre 2022

Bonjour, Heureux de t'écris ta requ

chris à dit:

Bonjour

Pas de MP non sollicité, merci.

C'est une solution Excel mais elle peut fonctionner dans Power BI puisque PowerQuery fait aussi partie de Power BI

Si cela peut aider voici le code de la requête

VB:

let
    Source = Excel.CurrentWorkbook(){[Name="Dossiers"]}[Content],
    Etat1 = Table.TransformColumnTypes(Source,{{"N°DOSSIER ", Int64.Type}, {"DATE", type date}, {"STATUT", type text}}),
    #"Lignes groupées" = Table.Group(Etat1, {"N°DOSSIER "}, {{"D1", each List.Min([DATE]), type nullable date}, {"D2", each List.Max([DATE]), type nullable date}}),
    #"Personnalisée ajoutée" = Table.AddColumn(#"Lignes groupées", "Filtre", each if [D1] = [D2] then null else 1),
    #"Lignes filtrées" = Table.SelectRows(#"Personnalisée ajoutée", each ([Filtre] = 1)),
    #"Supprimer le tableau croisé dynamique des autres colonnes" = Table.UnpivotOtherColumns(#"Lignes filtrées", {"N°DOSSIER "}, "Attribut", "DATE"),
    #"Colonnes supprimées" = Table.RemoveColumns(#"Supprimer le tableau croisé dynamique des autres colonnes",{"Attribut"}),
    #"Requêtes fusionnées" = Table.NestedJoin(#"Colonnes supprimées", {"N°DOSSIER ", "DATE"}, Etat1, {"N°DOSSIER ", "DATE"}, "Etat1", JoinKind.Inner),
    #"Etat1 développé" = Table.ExpandTableColumn(#"Requêtes fusionnées", "Etat1", {"STATUT"}, {"STATUT"}),
    #"Lignes triées" = Table.Sort(#"Etat1 développé",{{"N°DOSSIER ", Order.Ascending}, {"DATE", Order.Ascending}})
in
    #"Lignes triées"

Le tableau source a été mis sous forme de tableau structuré nommé Dossiers

Bonjour Chris, Heureux de t'écrire ta requête marche super bien sur mon jeu de données test encore une fois merci,
cependant je suis entrain de le testé sur mon jeu de données original, il marche très bien sauf que je suis bloqué au niveau de cette partie et si possible j'aimerais plus d'éclaircissement merci.

#"Supprimer le tableau croisé dynamique des autres colonnes" = Table.UnpivotOtherColumns(#"Lignes filtrées", {"N°DOSSIER "}, "Attribut", "DATE"),
#"Colonnes supprimées" = Table.RemoveColumns(#"Supprimer le tableau croisé dynamique des autres colonnes",{"Attribut"}),
#"Requêtes fusionnées" = Table.NestedJoin(#"Colonnes supprimées", {"N°DOSSIER ", "DATE"}, Etat1, {"N°DOSSIER ", "DATE"}, "Etat1", JoinKind.Inner),
#"Etat1 développé" = Table.ExpandTableColumn(#"Requêtes fusionnées", "Etat1", {"STATUT"}, {"STATUT"}),
#"Lignes triées" = Table.Sort(#"Etat1 développé",{{"N°DOSSIER ", Order.Ascending}, {"DATE", Order.Ascending}})

chris · 22 Septembre 2022

Bonjour tous

Etapes depuis le début :
Tu mets ton vrai jeu de données sous forme de tableau structuré nommé Dossiers (on utilise les tableaux structurés depuis 19 ans dans Excel)

Tableaux structurés Excel

fauconnier.developpez.com

puis

depuis une cellule du tableau, Données, A partir d'un tableau : cela ouvre PowerQuery
il y a deux étapes listées à droite pour la requête qui s'est créée : renommer l'étape 2 de typage en Etat1
sélectionner la colonne N°DOSSIER clic droit, Regrouper par : en opérations utiliser le Min et le Max de DATE
Ajouter une colonne, Colonne Personnalisée, Nom Filtre, formule
=if [D1] = [D2] then null else 1
filtrer cette colonne pour ne garder que 1
sélectionner les colonnes N°DOSSIER et Filtre, clic droit Dépivoter les autres colonnes
Dans la barre de formule remplacer Valeur par DATE
supprimer les colonnes Filtre et Attribut
Accueil, Fusionner les requêtes : on ne peut choisir que la requête en cours.
Sélectionner dans les 2 tableaux, les deux colonnes, Type de jointure Interne.
Dans la barre de formule, remplacer le 2ème #"Colonnes supprimées" par Etat1 et
"Colonnes supprimées" par "Etat1"
Cliquer sur la double flèche près du titre Etat1 : décocher tout sauf STATUT
Trier par N°DOSSIER et DATE
sortir par Fermer et Charger dans, Tableau et choisir la destination

Microsoft 365 Excel VBA

mo_owen

XLDnaute Nouveau

Pièces jointes

chris

mo_owen

XLDnaute Nouveau

mo_owen

XLDnaute Nouveau

Pièces jointes

vgendron

XLDnaute Barbatruc

sylvanu

XLDnaute Barbatruc

Pièces jointes

chris

XLDnaute Barbatruc

mo_owen

XLDnaute Nouveau

mo_owen

XLDnaute Nouveau

mo_owen

XLDnaute Nouveau

mo_owen

XLDnaute Nouveau

mo_owen

XLDnaute Nouveau

mo_owen

XLDnaute Nouveau

chris

XLDnaute Barbatruc

mo_owen

XLDnaute Nouveau

mo_owen

XLDnaute Nouveau

chris

XLDnaute Barbatruc

Tableaux structurés Excel

Discussions similaires