Extraire des données uniques issues de plusieurs feuilles de calcul

Bonjour,

Voici un premier essai avec un dictionnaire :

Sub RefUniques()

Dim dico As Object
Dim ws As Worksheet
Dim dl&, i&, k As Byte

Set dico = CreateObject("Scripting.Dictionary")

For Each ws In Worksheets
    If ws.Name Like "Donn*" Then
        With ws
            dl = .Cells(.Rows.Count, 2).End(xlUp).Row
            For i = 1 To dl
                For k = 2 To 5 Step 3
                    If .Cells(i, k).Value <> "" And .Cells(i, k).Value <> "Référence" Then
                        dico(.Cells(i, k).Value) = dico(.Cells(i, k).Value) + 1
                    End If
                Next k
            Next i
        End With
    End If
Next ws

With Sheets("Récap")
    .Range("A2").Resize(dico.Count, 1) = Application.Transpose(dico.keys)
    .Range("B2").Resize(dico.Count, 1) = Application.Transpose(dico.items)
End With

End Sub

Cdlt,

Merci beaucoup 3GB pour votre réponse si rapide.

Je trouve l'idée séduisante et ça pourrait marcher.

Toutefois, j'aurais besoin d'une mise à jour dynamique, c'est-à-dire que l'utilisatrice (ma femme en l'occurrence, mais sans doute pas que) ne devrait pas avoir à lancer une macro à chaque fois qu'une donnée est modifiée.

Cela dit, il me reste la possibilité de lancer la macro automatiquement à l'activation de l'onglet. Qu'en pensez-vous ? Pas trop long ? Je vais tester et je reviens vers vous.

Merci encore !

Pour l'instant, en l'état, lancer la macro à l'activation de la feuille est une très bonne idée et je ne pense pas que ça prenne du temps.

L'avantage, c'est qu'il suffit de l'appeler dans la macro évènementielle.

Il faudra éventuellement prévoir un clearcontents en début de macro pour nettoyer les anciennes données.

Cdlt,

Encore un merci pour ton aide, 3GB.

Bon, un petit retour après quelques jours de fonctionnement.

Globalement, tout va bien, ça donne satisfaction, mais :
- la mise à jour à l'ouverture de l'onglet est assez longue (3 s chez moi, 10-12 s sur l'ordinateur professionnel de ma femme).
- il reste le problème de la mise à jour de la feuille quand les données sont mises à jour alors que cette dernière est ouverte. Je précise que les données sont issues d'un autre classeur.

Je ne vois pas comment faire pour résoudre ce problème. OK, ça n'est pas souvent, mais le risque existe bel et bien.

Salut Benj,

Alors, je ne suis pas un expert en optimisation du temps d'exécution malheureusement, donc je serais peut-être limité dans les réponses. Mais si les données sont issues d'un autre classeur, ça change un peu la macro. Est-ce que je pourrais voir le code ?

Pour la mise à jour à l'ouverture du fichier, il faut appeler la macro dans la procédure workbook_open :

'MODULE THISWORKBOOK
private sub workbook_open()
Call RefUniques
end sub

Cdlt,

Bonjour à tous

Il y a combien de feuilles dans le classeur ?

Peux-tu préciser :

BBBenj a écrit :

Je précise que les données sont issues d'un autre classeur.

Une proposition PowerQuery (intégré à Excel 2016 et+) : la requête se réactualise à l'activation de la feuille Recap

3GB,

En fait, les données ne sont pas issues directement d'un autre classeur. Elles sont déjà rapatriées dans ce classeur, réparties dans autant de feuilles que d'années (2018 à 2021), puis (mais ça n'a pas d'intérêt ici) classées par mois au sein de chaque feuille-année.

Et c'est dans ce dernier classeur que je fais mes extractions. Ça me limite donc le nombre de données à balayer de 240 ou 480 lignes selon les classeurs considérés (j'ai deux classeurs où les données sont éclatées selon le type d'emballage), à 1.099 lignes pour le classeur principal. Donc ça divise par 2 ou 4 le nombre de lignes à balayer, ce qui je pense est plus rapide.

Par contre, les données rapatriées dans les feuilles des années sont bien mises à jour en temps réel par un calcul matriciel :

={SIERREUR(PETITE.VALEUR(SI('[PLANNING EMPOTAGE.xlsm]Planning'!$E$4:$E$1099<>0;SI('[PLANNING EMPOTAGE.xlsm]Planning'!$BW$4:$BW$1099>=B6;SI('[PLANNING EMPOTAGE.xlsm]Planning'!$BW$4:$BW$1099<=FIN.MOIS(B6;0);'[PLANNING EMPOTAGE.xlsm]Planning'!$K$4:$K$1099;"");"");"");LIGNE(INDIRECT("1:" & LIGNES('[PLANNING EMPOTAGE.xlsm]Planning'!$K$4:$K$1099))));"")}

Cette formule est "appliquée" 120 fois pour l'un des classeurs et 360 fois pour le second. Si tu vois une amélioration, je suis preneur ! Sinon, pas grave, je continue.

Re Benj,

Franchement, je ne saurais pas vraiment te répondre comme je t'ai dit sur mon précédent message d'autant plus que je n'aurais jamais connaissance de l'utilisation effective de ton fichier.

Tout ce que je peux te dire, c'est que la macro n'a d'incidence sur le fichier que quand elle est exécutée, c'est-à-dire à chaque activation de la feuille. Il est toujours possible de l'exécuter par un bouton, juste à l'ouverture ou à la fermeture... ou d'accepter de patienter quelques secondes si la mise à jour est occasionnelle.

Mais sinon, tu peux examiner la proposition de 78Chris (que je salue au passage !). Je ne connais pas PowerQuery mais j'ai cru comprendre que les traitements étaient rapides... Ca ne coûte rien.

Cdlt,

@3GB : merci pour ton aide, c'était très sympa, très compétent et en plus super rapide !

@78Chris : je suis désolé, je n'avais pas vu ton message (pourtant, je suis très bien pourvu côté lunettes 😎 !)
Je vais essayer de répondre à ta question : "il y a combien de feuilles dans le classeur ?"
En fait, l'essentiel des données se trouve dans un premier classeur, avec une feuille de données.
Ensuite, j'ai un second classeur où j'éclate mes données sur autant de feuilles que d'années (de 2018 à 2021 pour le moment, mais en 2022, devine !) et où je viens renseigner des informations complémentaires sur d'autres colonnes. Donc j'importe des références dans deux colonnes (H et J) et je renseigne en plus la colonne B et la P.
Puis, sur une autre feuille de ce second classeur, je dois extraire sur une colonne la liste sans doublon des références issues des colonne B, H, J et P pour ensuite faire des manipulations de données (quantité commandées, consommées et perdues) à partir des références des produits.

Je ne peux malheureusement pas envoyer mes fichiers car ils sont confidentiels et les anonymiser ne ferait que supprimer l'ensemble des données, donc je ne suis pas sûr que ça soit utile.

Pour le classeur que tu m'as envoyé, je dois avouer que je ne connais strictement rien à Power Query, mais j'ai un message d'erreur à l'activation de "Récap" : "[Expression.error] La valeur d'argument n'est pas valide.", ce qui ne va pas m'aider, j'en suis désolé.

[Edit] en cherchant un peu (si, si, j'ai commencé !), j'ai vu que Power Query est un complément à télécharger. Si je peux tout à fait le faire sur mon PC, celui de ma femme est totalement verrouillé par une stratégie d'entreprise et elle ne peut rien installer sur le sien, pas plus que sur celui des autres utilisateurs. Du coup, je crains que cette technique ne tombe à l'eau. Est-ce que je me trompe ?

Bonjour

BBBenj a écrit :

@78Chris : je suis désolé, je n'avais pas vu ton message (pourtant, je suis très bien pourvu côté lunettes 😎 !)
Je vais essayer de répondre à ta question : "il y a combien de feuilles dans le classeur ?"
En fait, l'essentiel des données se trouve dans un premier classeur, avec une feuille de données.
Ensuite, j'ai un second classeur où j'éclate mes données sur autant de feuilles que d'années (de 2018 à 2021 pour le moment, mais en 2022, devine !) et où je viens renseigner des informations complémentaires sur d'autres colonnes. Donc j'importe des références dans deux colonnes (H et J) et je renseigne en plus la colonne B et la P.

Puis, sur une autre feuille de ce second classeur, je dois extraire sur une colonne la liste sans doublon des références issues des colonne B, H, J et P pour ensuite faire des manipulations de données (quantité commandées, consommées et perdues) à partir des références des produits.

alors dans ce cas il serait plus simple de partir directement du 1er classeur pour lister.

BBBenj a écrit :

Pour le classeur que tu m'as envoyé, je dois avouer que je ne connais strictement rien à Power Query, mais j'ai un message d'erreur à l'activation de "Récap" : "[Expression.error] La valeur d'argument n'est pas valide.", ce qui ne va pas m'aider, j'en suis désolé.

[Edit] en cherchant un peu (si, si, j'ai commencé !), j'ai vu que Power Query est un complément à télécharger. Si je peux tout à fait le faire sur mon PC, celui de ma femme est totalement verrouillé par une stratégie d'entreprise et elle ne peut rien installer sur le sien, pas plus que sur celui des autres utilisateurs. Du coup, je crains que cette technique ne tombe à l'eau. Est-ce que je me trompe ?

Le classeur testé sur 2 PC, l'un avec Excel 2010, l'autre Excel 365 ne renvoie pas d'erreur donc c'est bizarre

Tu as mal regardé : PowerQuery est totalement intégré à EXcel depuis la version 2016 comme je l'avais écrit dans mon post, donc sauf si ta femme a une version antérieure, rien à installer.

Tu peux voir la requête et l'exécuter depuis PowerQuery pour voir quelle étape pose problème : Données, Requêtes et connexions qui liste les requêtes à droite

Clic droit sur Synthèse, Modifier. Là s'il y a une erreurs, tu vois à partir de quelle étape à droite

Voici ce que j'ai en affichant les requêtes... J'ai vraiment dû rater quelque chose !

RE

Clique sur Fermer: c'est un message non bloquant, juste un avertissement

OK.

Il n'en reste pas moins que j'ai la même erreur à chaque fois que j'ouvre la feuille "Récap"... Je ne comprends pas. Je n'ai encore rien fait sur le classeur que tu m'envoies.

78chris a écrit :

Tu peux voir la requête et l'exécuter depuis PowerQuery pour voir quelle étape pose problème : Données, Requêtes et connexions qui liste les requêtes à droite

Clic droit sur Synthèse, Modifier. Là s'il y a une erreurs, tu vois à partir de quelle étape à droite

sur 2016 c'est Données, Afficher les requêtes

Tant que tu ne regardes pas l'étape concernée... on n'avancera pas

Ta version 2016 est à jour ?

Comme dit j'ai testé sur 2010 et 365...