XL 2016 VBA - Traitement de données en fonction de plusieurs critères avec Dictionary

[Mak_tarmak]

Bonjour,
Je sollicite votre aide pour utiliser la fonction Dictionary dans une macro ou une autre méthode plus judicieuse mais toujours en macro car j'ai beaucoup de données à traiter qui évoluent sans cesse.
Je manipule très mal les imbrications de données qui dépendent de plusieurs critères dans des onglets différents mais je souhaite apprendre.

Voici ma problématique :
Dans l'onglet ELT_SESSIONS, en colonne B j'ai mes codes sessions.
Dans l'onglet liste_sessions, ceux-ci se trouvent en colonne A.

Pour chaque code session dans B, je recherche l'équivalence dans A.
Si elle est trouvée, je regarde dans la colonne G de liste_sessions.
Je prends chaque valeur unique de cette colonne pour le code session concerné et pour chaque valeur unique je soustrais les date/heure de J et I pour obtenir une durée en heures avec une décimale. Ensuite, je fais la somme des durées de chaque segment unique et je la reporte dans la colonne L de ELT_SESSIONS pour chaque session.

Pour exemple, je prends le code session 6086.
Il se trouve dans la colonne B de ELT_SESSIONS mais aussi dans la colonne A de liste_sessions.
Pour ce code session, je regarde dans G et je vois qu'il y a 15 valeurs mais seulement 3 segments uniques : le 1, 2 et 2851 (il peut en avoir 10 ou plus différents)

Pour la première valeur unique 1, je soustrais 08/03/2023 17:30 Europe/Paris en J à 08/03/2023 14:00 Europe/Paris en I, cela donne 3,5 heures (les dates peuvent être converties au besoin mais je dois obtenir le résultat en heures)
Pour la deuxième valeur unique 2, je soustrais 09/03/2023 12:30 Europe/Paris en J à 09/03/2023 09:00 Europe/Paris en I, cela donne 3,5 heures
Pour la dernière valeur unique 2851, je soustrais 09/03/2023 18:00 Europe/Paris en J à 09/03/2023 13:30 Europe/Paris en I, cela donne 4,5 heures

Comme il y a 3 valeurs différentes de segments, je fais la somme des trois, soit 11,5 heures et je reportes unique 11,5 dans la colonne L de ELT_SESSIONS pour le code session concerné.

Je vous joints un fichier modèle qui sera plus parlant.

Merci d'avance,
Kad

 

[Mak_tarmak]

Re-,
Bien sûr...
La V2

Re,
ça à l'air nickel ! Bravo !
Petite question, étant novice dans Power Query, je récupère comment la requête pour l'intégrer dans mon fichier d'origine et aussi pour comprendre comment vous avez fait car j'ai envie de progresser ?
En tout cas, un grand merci.

 

[Lolote83]

Re bonjour

Lolote,
Comme nous obtenons les mêmes résultats, je pense qu'on n'est pas loin de la vérité

Merci @Cousinhub , mais c'était aussi pour être sur que les solutions proposées soient en adéquation avec la demande initiale. J'avais effectivement remarqué la correspondance de nos deux versions.
@+ Lolote83

 

[Cousinhub]

Re-,
Dans ton fichier d'origine, il faut que tu nommes les deux Tableaux Structurés :
- T_Elt pour le tableau de l'onglet "ELT_Sessions"
- T_List pour le tableau de l'onglet "liste_sessions"

Dans mon fichier :

Dans le ruban "Données", tu cliques sur "Requêtes et connexion", une fenêtre va s'ouvrir sur la droite

Tu cliques sur le 1er (T_Elt), puis en maintenant la touche Contrôle appuyée, tu cliques sur le 2ème (T_List)
Clic droit, "Copier"

Tu viens dans ton fichier, "Données/Requêtes et connexions", clic droit dans la zone grise à droite (et normalement vide), "Coller"
Voilà, tu as les requêtes...
Tu cliques sur "Données/Actualiser tout"
Si la requête "T_Elt" ne se charge pas, clic droit dessus, "Charger dans", et tu sélectionnes la colonne (S dans mon fichier)
Bon courage

 

[Mak_tarmak]

Re bonjour à tous.
@Mak_tarmak , merci pour ton retour. Je comprends tout à fait la problématique, mais à titre d'information, peux tu nous dire si les solutions proposées donnaient des réponses justes ?
Merci de ton retour
@+ Lolote83

Re,
Désolé, j'ai omis de te faire le retour.
Tes formules retournent les bonnes réponses. J'ai juste besoin d'avoir 11,5 au lieu de 11:30:00 pour les heures.
Tes formules sont très intéressantes, je les garde sous la main pour autre chose.
Comme j'ai supprimé une quinzaine de colonnes dans liste_sessions, je suppose qu'il faudra que je crée tes colonnes un peu plus loin
Par contre qu'as tu fait aux colonnes vertes pour qu'elles n'affichent qu'une valeur différente de chaque segment ? ça me turlupine !

 

[Mak_tarmak]

Re-,
Dans ton fichier d'origine, il faut que tu nommes les deux Tableaux Structurés :
- T_Elt pour le tableau de l'onglet "ELT_Sessions"
- T_List pour le tableau de l'onglet "liste_sessions"

Dans mon fichier :

Dans le ruban "Données", tu cliques sur "Requêtes et connexion", une fenêtre va s'ouvrir sur la droite
Regarde la pièce jointe 1173299
Tu cliques sur le 1er (T_Elt), puis en maintenant la touche Contrôle appuyée, tu cliques sur le 2ème (T_List)
Clic droit, "Copier"

Tu viens dans ton fichier, "Données/Requêtes et connexions", clic droit dans la zone grise à droite (et normalement vide), "Coller"
Voilà, tu as les requêtes...
Tu cliques sur "Données/Actualiser tout"
Si la requête "T_Elt" ne se charge pas, clic droit dessus, "Charger dans", et tu sélectionnes la colonne (S dans mon fichier)
Bon courage

Merci beaucoup Cousinhub, c'est très clair.
Par contre, une question me vient en tête. L'onglet ELT_SESSIONS est fabriqué à partir de 4 rapports de plus de 50000 lignes que j'insère régulièrement dans mon fichier original pour le constituer et fournir des chiffres.
Après avoir formaté mes rapports comme je le souhaite, j'ai différents launcher de macros qui me constituent plusieurs onglets avec des données différentes. Généralement, je lance mon launcher principal qui lancent les autres et je vais me prendre un café.
Comment peut on inclure ton travail dans cette automatisation ? Enfin si c'est possible, je pose juste la question

 

[Lolote83]

Re bonjour,

Par contre qu'as tu fait aux colonnes vertes pour qu'elles n'affichent qu'une valeur différente de chaque segment ? ça me turlupine !

Tu parles des colonnes A, G, I et J de l'onglet Liste_sessions ?
Je n'ai rien fait, je les ai simplement mises en évidence (verte) pour me repérer selon ta demande
Ce sont les colonnes K à O qui ont été rajoutées
La colonne K = colonne I au format Date et Heure
La colonne L = colonne J au format Date et Heure
La colonne M = Calcul de la durée entre L et K
La colonne N = Concaténation de la colonne A (identifiant) et de la colonne G (n° segment)
La colonne O = Calcul des doublons basé sur la concaténation (Chaque FAUX correspond a une nouvelle donnée).
Donc dans ma formule (onglet ELT_SESSIONS), je ne prends :
- Les identifiants (colonne B)
- Les doublons = FAUX
- La durée
@+ Lolote83

 

[Lolote83]

Re re bonjour

Après avoir formaté mes rapports comme je le souhaite, j'ai différents launcher de macros qui me constituent plusieurs onglets avec des données différentes. Généralement, je lance mon launcher principal qui lancent les autres et je vais me prendre un café.

Peut être même qu'un seul traitement via PowerQuery pourrait faire tout le job. Mais on a pas le détail des différents onglets comme tu sembles le préciser.
A voir
@+ Lolote83

 

[Mak_tarmak]

Re bonjour,

Tu parles des colonnes A, G, I et J de l'onglet Liste_sessions ?
Je n'ai rien fait, je les ai simplement mises en évidence (verte) pour me repérer selon ta demande
Ce sont les colonnes K à O qui ont été rajoutées
La colonne K = colonne I au format Date et Heure
La colonne L = colonne J au format Date et Heure
La colonne M = Calcul de la durée entre L et K
La colonne N = Concaténation de la colonne A (identifiant) et de la colonne G (n° segment)
La colonne O = Calcul des doublons basé sur la concaténation (Chaque FAUX correspond a une nouvelle donnée).
Donc dans ma formule (onglet ELT_SESSIONS), je ne prends :
- Les identifiants (colonne B)
- Les doublons = FAUX
- La durée
@+ Lolote83

Oui, c'est tout à fait ça ! Un grand merci, c'est très astucieux.
Merci de m'avoir éclairé.
J'en apprends tous les jours

 

[Cousinhub]

Re-,
Si les TS des onglets portent bien les noms cités supra, il n'y a rien à faire de plus...
Juste s'assurer que les colonnes portent bien les noms de colonne que tu as donnés dans ton fichier exemple...
Surtout les colonnes du TS "T_Elt" qui se nomment ("code élément " ***avec un espace à la fin*** et "session"
et pour le TS "T_List", les noms de colonnes "Identifiant de la session", "N° seg", "Date/heure de début", "Date/heure de fin"
Si ces noms de colonne sont identiques, clic droit dans la requête, actualiser, et c'est tout...

 

[Mak_tarmak]

Re re bonjour

Peut être même qu'un seul traitement via PowerQuery pourrait faire tout le job. Mais on a pas le détail des différents onglets comme tu sembles le préciser.
A voir
@+ Lolote83

C'est bien possible, même sûrement, mais malheureusement le fichier est bien trop volumineux pour passer !

 

[Cousinhub]

Re-,
Et je plussoie aux dires de Lolote :

Re re bonjour

Peut être même qu'un seul traitement via PowerQuery pourrait faire tout le job. Mais on a pas le détail des différents onglets comme tu sembles le préciser.
A voir
@+ Lolote83

 

[Mak_tarmak]

Re-,
Si les TS des onglets portent bien les noms cités supra, il n'y a rien à faire de plus...
Juste s'assurer que les colonnes portent bien les noms de colonne que tu as donnés dans ton fichier exemple...
Surtout les colonnes du TS "T_Elt" qui se nomment ("code élément " ***avec un espace à la fin*** et "session"
et pour le TS "T_List", les noms de colonnes "Identifiant de la session", "N° seg", "Date/heure de début", "Date/heure de fin"
Si ces noms de colonne sont identiques, clic droit dans la requête, actualiser, et c'est tout...

Re,
C'est génial, merci beaucoup !
Oui tous les noms sont les originaux dans mon fichier. Y'a plus qu'à !
J'ai appris beaucoup de choses, je dormirais moins "idiot" ce soir et une porte Power Query s'ouvre peut-être pour moi.
Passes une excellente après-midi.

 

[Mak_tarmak]

Re re bonjour

Peut être même qu'un seul traitement via PowerQuery pourrait faire tout le job. Mais on a pas le détail des différents onglets comme tu sembles le préciser.
A voir
@+ Lolote83

Un grand merci pour ton aide Lolote83.
Passes une très bonne après-midi.

 

[Cousinhub]

Re-,

je dormirais moins "idiot" ce soir

Comme tout le monde, du moins, je l'espère...

 

[Mak_tarmak]

Compris, mais dans la feuille "ELT_SESSIONS" il n'y a aucune référence au N° de segment, alors comment fait on ?
On devrait trouver dans cette feuille 5452 Segment 1 et sur la ligne du dessous 5452 segment 2 puis 5452 segment 3 ?

Un grand merci pour votre aide sylvanu.
Je reste toujours à l'écoute d'une solution macro pour apprendre à manipuler aussi avec.
Très bon après-midi.