Correspondance de colonne

[jhlamoustache]

Bonjour à tous

Issu d'une conversation:

Bonjour Dranreb,

je dois développer une procédure qui mettra à jour une feuille (Destination) mensuellement environ à partir de données extraites sur une autre feuille (Source). L'utilisateur ordonnera ses colonnes comme il le souhaite. Pour développer mon code, j'ai l'habitude d'utiliser des tableaux. Je me dois donc de trouver une procédure qui permette de de faire correspondre le champs de la feuille Source avec ceux de la feuilles destination. De plus je ne suis pas sûr qu'au fil du temps l'ordonnancement des champs de la feuille Source soit toujours le même. J'ai lu qu'il fallait utiliser un dictionnaire, et faire du "mapping", mais je ne sais pas bien comment m'y prendre. Pourriez-vous m'aider svp ?

Ce à quoi Dranreb a répondu.

Si la source est sous forme de tableau les ListColumn du ListObject qui le représente on pour identifications les titres des colonnes, et pas besoin de mapping.
Sinon :
Code (Visual Basic):
Dim DicTit As New Dictionary, TTit(), C As Long
TTit = ActiveSheet.[A1:Z1].Value
For C = 1 To 26
DicTit(TTit(1, C)) = C
Next C

J'ai donc adapté le code, mais je ne vois pas comment je vais modifier les valeurs (jaunes sur Excel) au bon endroit dans le tableau TabD avant de le recoller sur la feuille "Destination"
NB : En principe je devrai passer par un tableau transposé pour ajouter les nouvelles lignes.
Merci de votre aide

 

[Dranreb]

Bonjour.
Comme ça par exemple :

Sub MAJ()
   Dim TabD(), CD As Long, TabS(), CS As Long, L As Long, DicTit As New Dictionary
   TabS = Feuil2.UsedRange.Value
   TabD = Feuil1.UsedRange.Resize(UBound(TabS, 1)).Value
   For CD = 1 To UBound(TabD, 2): DicTit(TabD(1, CD)) = CD: Next CD
   For CS = 1 To UBound(TabS, 2)
      If DicTit.Exists(TabS(1, CS)) Then
         CD = DicTit(TabS(1, CS))
         For L = 2 To UBound(TabS, 1): TabD(L, CD) = TabS(L, CS): Next L
         End If: Next CS
   Feuil1.[A1].Resize(UBound(TabD, 1), UBound(TabD, 2)) = TabD
   End Sub

 

[jhlamoustache]

Bonjour Dranreb,

Merci de votre aide.

Ce code implique que je devrai réaliser mes traitements à partir du moment où j'entrerai dans la boucle For L ... , n'est-ce pas ?

Je remarque que TabD est ajusté en fonction de TabS, mais si la taille de ce dernier est < TabD, je suppose que je perdrai des données sur la feuille destination ?

Le traitement ne consistera pas qu'à recopier la feuille source vers la detination. Si une valeur est présente dans Destination, je dois évaluer une éventuelle variation. Et si elle existe, récupérer l'ancienne valeur et procéder à des mises en forme. voir PJ

Au sujet des mises en forme, j'imagine deux options :
1 je crée des tableaux miroirs de TabD, un pour les polices, un pour les couleurs de fond... que je renseignerai au fil du traitement et que j'appliquerai sur la feuille Destination. Cela je saurai le faire seul.
2 Utiliser des modules de classes pour assigner une mise en forme "tout compris" sur les cellules impactées. Cela je ne saurai pas le faire seul.
Enfin je précise qu'entre deux traitements successifs s'il n'y a pas de variations, les couleurs alternées des lignes sont rétablies.
Quelle est l'option la plus pertinente selon vous pour exécuter la procédure le plus rapidement possible ? Voyez-vous vous même une autre solution ?

Encore merci de votre aide.

 

[Dranreb]

Bonjour.
Je ne sais pas, j'ai juste essayé de répondre à la question 'comment utiliser ensuite le dictionnaire'.

 

[jhlamoustache]

Bonjour,

D'accord. Merci

 

[jhlamoustache]

Bonjour,

je coince dans l'utilisation du Dico. J'ai modifié mon fichier pour tenter d'être plus clair. Je souhaite, pour chaque voyage de la feuille source, vérifier s'il est présent sur la feuille destination. S'il l'est alors je dois tester les valeurs immatriculation, marque, départ, et s'il y a eu variation noter les anciennes valeurs dans le colonnes bleues, et substituer les valeurs dans la colonne à gauche. Si le voyage n'est pas présent, alors on l'ajoute à la suite.
Vous avez le résultat souhaité sur la feuille "Destination après traitement".
J'ai ajouté un TabNew pour préparer des traitements ultérieurs sur les variants ou les nouveaux voyages.
Pouvez-vous m'aider svp ?

 

[Dranreb]

Bonjour.
Je crois qu'on a intérêt à récupérer la source dans un tableau de mouvements aux mêmes colonnes que la destination, sans cela on ne va pas s'en sortir.
Je joint un classeur destiné à s'enregistrer en .xlam, et muni d'une feuille d'aide. Une fois installé cochez GigIdx dans les références de votre classeur d'application.
Alors cette procédure semble faire ce que vous demandez :

Option Explicit
Sub MàJ()
Dim TSrc(), C As Long, DicTitSrc As New Dictionary, TMvt(), TRésu(), Voy As SsGr, L As Long, Détail, DétMvt(), DétDst(), Cas
TSrc = Feuil2.[A1:I1].Value
For C = 1 To 9: DicTitSrc(TSrc(1, C)) = C: Next C
TSrc = ColUti(Feuil2.[A2:M2]).Value
TTit = Feuil1.[A1:M1].Value
ReDim TMvt(1 To UBound(TSrc, 1), 1 To 13)
For C = 1 To 13
   If DicTitSrc.Exists(TTit(1, C)) Then GarnirColonne TMvt, C, TSrc, DicTitSrc(TTit(1, C))
   Next C
ReDim TRésu(1 To 5000, 1 To 13)
For Each Voy In Gigogne(TableUnique(Feuil1.[A2:M2], TMvt), 1)
   L = L + 1: Cas = 0
   For Each Détail In Voy.Co
      If Détail(0) = 0 Then DétDst = Détail: Cas = 1 Else DétMvt = Détail: Cas = Cas Or 2
      Next Détail
   Select Case Cas
      Case 1: ' N'existe qu'en Dst
         For C = 1 To 13: TRésu(L, C) = DétDst(C): Next C
         TRésu(L, 6) = Empty: TRésu(L, 8) = Empty: TRésu(L, 11) = Empty: TRésu(L, 13) = Empty
      Case 2: ' N'existe qu'en Mvt
         For C = 1 To 13: TRésu(L, C) = DétMvt(C): Next C
      Case Else: 'Existe des deux cotés
         For C = 1 To 13: TRésu(L, C) = DétMvt(C): Next C
         If DétMvt(5) <> DétDst(5) Then TRésu(L, 6) = DétDst(5)
         If DétMvt(7) <> DétDst(7) Then TRésu(L, 8) = DétDst(7)
         If DétMvt(10) <> DétDst(10) Then TRésu(L, 11) = DétDst(10)
         If DétMvt(12) <> DétDst(12) Then TRésu(L, 13) = DétDst(12)
      End Select: Next Voy
Feuil3.[A10].Resize(5000, 13).Value = TRésu ' (emplacement provisoire, en définitive Feui1.[A2])
End Sub

 

[jhlamoustache]

Merci beaucoup, je vais étudier. Bon WE

 

[job75]

Bonsoir jhlamoustache, Bernard,

Pas bien compris le problème mais s'il s'agit uniquement de remplir le tableau de destination sur les bonnes colonnes :

Sub MAJ()
Application.ScreenUpdating = False
With Sheets("Destination").[A1].CurrentRegion
    .Offset(1).ClearContents
    .Rows(1).Insert
    .Cells(0, 1) = 1: .Cells(0, 1).Resize(, .Columns.Count).DataSeries
    .Rows(0).Resize(2).Sort .Rows(1), xlAscending, Orientation:=2 'tri horizontal
End With
With Sheets("Source").[A1].CurrentRegion
    .Rows(1).Insert
    .Cells(0, 1) = 1: .Cells(0, 1).Resize(, .Columns.Count).DataSeries
    Union(.Rows(0), .Cells).Sort .Rows(1), xlAscending, Orientation:=2 'tri horizontal
    .Copy Sheets("Destination").[A2]
    Union(.Rows(0), .Cells).Sort .Rows(0), xlAscending, Orientation:=2 'tri horizontal
    .Rows(0).Delete xlUp
End With
With Sheets("Destination").[A1].CurrentRegion
    .Sort .Rows(1), xlAscending, Orientation:=2 'tri horizontal
    .Rows(1).Delete xlUp
End With
End Sub

A+

 

[jhlamoustache]

Bonjour Bernard et Job75,

Merci de vous être penché sur mon sujet. C'est magnifique d'apprendre. L'installation de Gigogne s'est bien passée. Actuellement la feuille destination comporte 41 000 lignes et 70 colonnes; c'est pourquoi je pense rester en mode tableau pour accélérer l'exécution.

Dans cette partie du code,
Case Else: 'Existe des deux cotés, les n° de colonnes à évaluer sont fixes.
For C = 1 To 13: TRésu(L, C) = DétMvt(C): Next C
If DétMvt(5) <> DétDst(5) Then TRésu(L, 6) = DétDst(5)
If DétMvt(7) <> DétDst(7) Then TRésu(L, 8) = DétDst(7)
If DétMvt(10) <> DétDst(10) Then TRésu(L, 11) = DétDst(10)
If DétMvt(12) <> DétDst(12) Then TRésu(L, 13) = DétDst(12)

Je propose de nommer ces cellule en-tête, et de déclarer comme variable chaque n° de colonne, et de passer cette variable comme indice des tableaux DétMvt DétDst... et créer une boucle sur la condition
If DétMvt(Colonne) <> DétDst(Colonne) Then TRésu(L, Colonne +1) = DétDst(Colonne)
Sinon, je lis que Sub indexerParFusions pourrait peut-être le faire. Il s'agirait de rechercher les colonne commençant par "ancienne valeur" et de prendre la colonne-1. Je pense que cela laisserait à l'utilisateur la liberté de déplacer les colonnes comme il l'entend (cahier des charges, et sous réserve de déplacer 2 colonnes contiguës bien entendu), Et qu'en cas d'ajout d'une nouvelle colonne ancienne valeur, elle soit prise en compte au traitement suivant. Qu'en pensez vous ?

Pour les mises en forme, elles sont de 2 types :
Pour les voyages existants, l'esprit est de repérer le voyage modifié lui-même (jaune par exemple), ainsi que les éléments modifiés
Pour les voyages apparus, l'esprit est de les repérer eux-mêmes (vert par exemple), ainsi que de modifier les polices( taille, couleur) de certaines caractéristiques . Pensez-vous qu'ils soit possible d'alimenter un autre tableau lors du Select case
case 1 : invariant
case 2 : nouveau
case else : variant
De sorte que je puisse déclencher les traitements ad hoc ultérieurement. Est-ce pertinent ?

PS dans les prochains jours, je serai indisponible. Ne vous étonnez pas de mon silence.

Bien cordialement

 

[Dranreb]

Bonjour.
J'avais cru comprendre que seule les colonnes de la feuille source pouvaient varier. Celle de la feuille déstination aussi ???
Si la plage est sous forme de tableau vous pouvez récupérer les numéros de colonnes par LOt.ListColumns(LeTitre).Index, LOt étant déclaré As ListObject et initialisé par Set LOt = Feuil1.ListObjects(1)

 

[jhlamoustache]

Bonjour,
le principe est d'extraire d'un site web des données, et de les compiler dans la feuille destination.
Lors des montées de version du site web (une à deux fois par an), l'ordonnancement des colonnes de l'extraction peut varier. Des collègues se sont déjà fait "prendre". Et les traitements développés par des prestataires (partis depuis) sont devenus inopérants.
Quand à la feuille destination, selon les éléments observés, l'utilisateur demande à pouvoir déplacer les colonnes.
Je viens de tester votre code en déplaçant des colonnes, et effectivement le résultat n'est pas conforme.
Désolé de n'avoir pas été plus précis.

 

[Dranreb]

Oui mais le site Web c'est la feuille Source, pas la feuille Destination !
Remarque: personnellement je n'aurais pas créé de colonne supplémentaire pour les anciennes valeurs, juste une colonne Observation.
j'aurai carrément reproduit toute l'ancienne ligne avec pour observation "Ancien" avant de reproduire la nouvelle correspondante.
J'aurais fait la comparaison sur toute les colonnes autres que la 1ère qui est le critère de reconnaissance des élément correspondants entre Source et Destination. Comme ça plus de problème, la comparaison se fait carrément sur toutes les colonnes associées à cet identifiant.
Ça permettrait de mettre des couleurs de fond surlignant ce qui a changé grâce à des MeFC.

 

[jhlamoustache]

Si je comprends bien votre solution, j'obtiendrais des doublons de voyage dans la feuille destination ?

 

[Dranreb]

Oui mais on pourrait leur mettre une couleur sombre genre violet pour qu'on voie bien qu'ils ne font plus partie de l'état actuel.