Microsoft 365 Transformer un export de données

[Ines99]

Bonsoir le Forum,

Je vous sollicite pour transformer un export d’une balance afin de me faciliter son exploitation.

Il s’agit de reprendre le classement par tranche de solde échu des données par compte et fournisseurs.

J’aimerais pouvoir copier et coller les exports et que les données se mettent à jour sur la feuille nouvel export.

Dites-moi s’il vous plait

Ci-joint un fichier exemple

Ines

 

[job75]

J'ai bien compris et fichier fonctionne bien.

J'en suis heureux mais je dois faire une remarque.

Dans ma solution aucune donnée située sous la 1ère ligne "Total" n'est copiée.

Si vous voulez cependant les copier il faudrait qu'elles soient cohérentes donc standardisées de manière qu'on puisse les reconnaître.

 

[Ines99]

Bonsoir,
Merci pour cette remarque pertinente.
Je me suis focalisé sur cette option de transformer les données en me disant je verrai par la suite ce qu' il en dessous de total.
Mais si vous pouvez aussi integrer ces données ou globalement les totaux par colonne en haut enfin si vous pouvez ça serait super
Ines

 

[job75]

Comme je l'ai dit c'est à vous de faire en sorte que les lignes situées sous le 1er "Total" soient repérables.

Revenez avec des données cohérentes.

Est-ce que les numéros de compte commenceront toujours par "K" ?

 

[Ines99]

Comme je l'ai dit c'est à vous de faire en sorte que les lignes situées sous le 1er "Total" soient repérables.

Revenez avec des données cohérentes.

Est-ce que les numéros de compte commenceront toujours par "K" ?

Entendu.
Les numéros de comptes commenceront toujours par une lettre, par forcément "K".
Je reviens vers vous.

Merci beaucoup

 

[Ines99]

Bonsoir Job75
Je vous prie de trouver ma database.
Merci pour votre aide si pouvez l'améliorer.
Ines

 

[Cousinhub]

Comme je l'ai dit c'est à vous de faire en sorte que les lignes situées sous le 1er "Total" soient repérables.

Hello Job,
Ces données proviennent d'extractions...
Et une "extraction" comptable peut contenir moult "comptes"... (avec plus ou moins de cellules contenant le mot "total")
Le demandeur ne peut donc être tenu d'une quelconque "responsabilité", non?

 

[Ines99]

Hello Job,
Ces données proviennent d'extractions...
Et une "extraction" comptable peut contenir moult "comptes"... (avec plus ou moins de cellules contenant le mot "total")
Le demandeur ne peut donc être tenu d'une quelconque "responsabilité", non?

Bonsoir Cousinhub,
Ces données alors là datent de plus de 5 ans et anonymisées.
Je les ai modifiées et tous les noms sont fictifs.
Bien à vous
Ines

 

[Cousinhub]

Bonsoir Cousinhub,
Ces données alors là datent de plus de 5 ans et anonymisées.
Je les ai modifiées et tous les noms sont fictifs.
Bien à vous
Ines

Hi,
Attention, mon intervention ne présage en rien, d'une quelconque "réponse" de ma part....
Juste pour dire que votre question n'est certainement pas aussi simple....
Et que le VBA, c'est bien, mais a aussi ses limites...
Je vous laisse avec Job75

 

[Ines99]

Hi,
Attention, mon intervention ne présage en rien, d'une quelconque "réponse" de ma part....
Juste pour dire que votre question n'est certainement pas aussi simple....
Et que le VBA, c'est bien, mais a aussi ses limites...
Je vous laisse avec Job75

Bonsoir,
Oui je comprends.
La data proposée est anonymisée Cousinhub.
Certainement le jeux de données proposée est complexe.

Job, ta proposition est déjà super bien et me permettra de gagner du temps, et je te remercie.
A bientôt à vous tous.
Ines

 

[job75]

Bon je pense avoir trouvé le bon critère pour récupérer les numéros de comptes.

En colonne A de la feuille source leurs caractères sont non gras alors que sur les autres lignes ils sont gras.

Dans Module1 j'ai donc créé cette fonction VBA :

Function Gras_Vide(c As Range) As Variant
If c.Font.Bold Or IsEmpty(c) Then Gras_Vide = "" 'texte vide
End Function

Et dans la macro j'insère une colonne auxiliaire qui sera ensuite supprimée :

Private Sub Worksheet_Activate()
Dim F As Worksheet, colsource, celdeb As Range, celfin As Range, dest As Range, titres, h&, col%
'---données sources---
Set F = Sheets("ExportCEGID")
Set celdeb = F.[A15]
Set celfin = F.Columns(1).Find("*", , xlValues, , , xlPrevious) 'dernière cellule non vide
colsource = Array(1, 4, 7, 9, 12, 14, 16, 19) 'colonnes à copier
F.Columns(1).Insert 'ajoute une colonne auxiliaire
F.Range(celdeb(1, 0), celfin(1, 0)) = "=Gras_Vide(RC[1])" 'fonction dans Module1
'---données destination---
Set dest = [B8]
titres = Array("Numéro de compte", "Fournisseur", "Solde du compte", "Solde non échu", "De 1 à 30 Jrs", "31-45 Jrs", "46-60 Jrs", "+61 Jrs", _
    "Total échu", "%", "Total non échu", "%", "Contrôle solde du compte")
'---filtrage et copier-coller des valeurs---
Application.ScreenUpdating = False
dest.CurrentRegion.ClearContents 'RAZ
dest.Resize(, UBound(titres) + 1) = titres
On Error Resume Next 'si aucune SpecialCel
With F.Range(celdeb(1, 0), celfin(1, 0)).SpecialCells(xlCellTypeFormulas, 1) 'valeurs numériques (0)
    h = .Count
    For col = 0 To UBound(colsource)
        .Offset(, colsource(col)).Copy
        dest(2, col + 1).PasteSpecial xlPasteValues 'Collage spécial-Valeurs
    Next col
End With
F.Columns(1).Delete 'supprime la colonne auxiliaire
'---formules---
dest(2, UBound(colsource) + 2).Resize(h) = "=SUM(RC[-4]:RC[-1])"
dest(2, UBound(colsource) + 3).Resize(h) = "=IFERROR(RC[-1]/RC[-7],"""")"
dest(2, UBound(colsource) + 4).Resize(h) = "=RC[-7]"
dest(2, UBound(colsource) + 5).Resize(h) = "=IFERROR(RC[-1]/RC[-9],"""")"
dest(2, UBound(colsource) + 6).Resize(h) = "=RC[-4]+RC[-2]"
Application.Goto [A1], True 'cadrage
End Sub

Dans la feuille source voyez le numéro de compte en A313.

Bonne nuit.

 

[Ines99]

Bon je pense avoir trouvé le bon critère pour récupérer les numéros de comptes.

En colonne A de la feuille source leurs caractères sont non gras alors que sur les autres lignes ils sont gras.

Dans Module1 j'ai donc créé cette fonction VBA :

Function Gras_Vide(c As Range) As Variant
If c.Font.Bold Or IsEmpty(c) Then Gras_Vide = "" 'texte vide
End Function

Et dans la macro j'insère une colonne auxiliaire qui sera ensuite supprimée :

Private Sub Worksheet_Activate()
Dim F As Worksheet, colsource, celdeb As Range, celfin As Range, dest As Range, titres, h&, col%
'---données sources---
Set F = Sheets("ExportCEGID")
Set celdeb = F.[A15]
Set celfin = F.Columns(1).Find("*", , xlValues, , , xlPrevious) 'dernière cellule non vide
colsource = Array(1, 4, 7, 9, 12, 14, 16, 19) 'colonnes à copier
F.Columns(1).Insert 'ajoute une colonne auxiliaire
F.Range(celdeb(1, 0), celfin(1, 0)) = "=Gras_Vide(RC[1])" 'fonction dans Module1
'---données destination---
Set dest = [B8]
titres = Array("Numéro de compte", "Fournisseur", "Solde du compte", "Solde non échu", "De 1 à 30 Jrs", "31-45 Jrs", "46-60 Jrs", "+61 Jrs", _
    "Total échu", "%", "Total non échu", "%", "Contrôle solde du compte")
'---filtrage et copier-coller des valeurs---
Application.ScreenUpdating = False
dest.CurrentRegion.ClearContents 'RAZ
dest.Resize(, UBound(titres) + 1) = titres
On Error Resume Next 'si aucune SpecialCel
With F.Range(celdeb(1, 0), celfin(1, 0)).SpecialCells(xlCellTypeFormulas, 1) 'valeurs numériques (0)
    h = .Count
    For col = 0 To UBound(colsource)
        .Offset(, colsource(col)).Copy
        dest(2, col + 1).PasteSpecial xlPasteValues 'Collage spécial-Valeurs
    Next col
End With
F.Columns(1).Delete 'supprime la colonne auxiliaire
'---formules---
dest(2, UBound(colsource) + 2).Resize(h) = "=SUM(RC[-4]:RC[-1])"
dest(2, UBound(colsource) + 3).Resize(h) = "=IFERROR(RC[-1]/RC[-7],"""")"
dest(2, UBound(colsource) + 4).Resize(h) = "=RC[-7]"
dest(2, UBound(colsource) + 5).Resize(h) = "=IFERROR(RC[-1]/RC[-9],"""")"
dest(2, UBound(colsource) + 6).Resize(h) = "=RC[-4]+RC[-2]"
Application.Goto [A1], True 'cadrage
End Sub

Dans la feuille source voyez le numéro de compte en A313.

Bonne nuit.

Merci Job75,
Je vous fais un retour demain, super sympa
Bien à vous

 

[job75]

Bonjour Ines99, le forum,

Vous avez parlé d'un classement des soldes pour cela il suffit de trier le tableau des résultats sur la 3ème colonne :

dest.Resize(h + 1, UBound(colsource) + 1).Sort dest(1, 3), xlDescending, Header:=xlYes 'tri décroissant sur les soldes

A+