XL 2019 Trouver la data correspondant selon un tableau ; deux colonnes et une ligne.

[Loïc DUBOIS]

Bonjour à tous,

J'espère que vous allez tous bien ?

Mon problème est que je souhaite réaliser une collecte de données via une base de données. Pour se faire j’ai mon fichier de base qui contient toutes mes données.

Ensuite j’ai mon fichier voulu (feuil2) qui contient toutes mes entreprises (liste) et je souhaite mettre toutes mes données dans mon fichier voulu (feuil2) sous forme d’une liste (colonne 1 : entreprise; colonne 2 : variable 1 ; colonne 3 : variable 3 etc).

Pour se faire dans ma liste des entreprises j’ai comme infos le numéro de tableau ou se trouve l’entreprise (fichier de base (feuil1) avec le nom de l’entreprise et la date souhaité. En sachant que la date souhaité (se trouve sur la première ligne de chaque tableau) est différente selon chaque tableau (j’ai 49 tableaux différents sur une seule feuille de calcul avec 14 variables différentes(fichier de base).

Je voudrais donc savoir s’il existe une formule excel me permettant de retrouver ma donnée selon le numéro de tableau (colonne A feuil2), le nom de la variable (M1, N1 et O1 (feuil2), le nom de l’entreprise (colonne B feuil2) (critère colonne) et selon la ligne (date) (il y a une ligne de date par tableau).

Ou un code VBA (en m’expliquant quelle critère je dois changer pour adapter à un fichier de plus de 400000 lignes et plus de 14 variables).



Merci d’avance



Je vous met un fichier joint en exemple.
PS : Je préfererais une formule excel si possible car je ne suis pas très à l'aide avec le code VBA (modifier le code pour mon cas général...)

 

[vgendron]

en PJ une proposition par macro

plusieurs choses:
1) cliquer UNE fois sur le bouton "Créer tables"
cette macro transforme ta feuille 1 en plusieurs tables dont chacune s'appelle du nom de la première colonne
==> tu devrais donc avoir 48 tables sur ton fichier final

2) ensuite cliquer sur le bouton "remplir Feuil2"
cett macro parcourt remplit les lignes une à une

NOTE: il va falloir que tu revois ta méthode pour identifier les dates de la colonne L
il y a des dates vides et surtout.. plus genant. des dates qui n'en sont pas.. exemple.. le 29/02/2018

 

[Loïc DUBOIS]

Je vois que ma demande est pas très clair je vais essayer de ré expliquer :

Je voudrais une formule ou un vba capable de :
- A partir de la colonne A (feuil2) trouver le numéro de tableau : En effet il s'avère que j'ai 49 tableaux différents avec des code d'entreprises différents
- Puis une fois que le tableau est trouvé il faut trouver l'intersection entre 2 colonnes (feuil1) et 1 ligne (feuil1) : les deux variables colonnes (B et C feuil1) sont le code entreprise (colonne B feuil2) et variable (cellule M1 ou N1 ou O1) ; enfin la variable ligne sont les dates (il y a une ligne de dates par tableaux (feuil1 : je ne peux pas unifier tous mes tableaux pour avoir une seule ligne de date...) les dates à trouver sont dans la colonne L (feuil2).

De base, j'avais trouvé une formule qui peut trouver le croisement entre deux colonnes et une ligne, la voici :
=INDEX(Feuil1!$C$2:$ZZ$100000;EQUIV(M$1 & Feuil2!$A1287;Feuil1!$A$2:$A$100000&Feuil1!$B$2:$B$100000;0); EQUIV(Feuil2!$L1287*1;Feuil1!$C$1:$ZZ$1;0))
Cette formule cherche le code entreprise et la variable dans les deux colonnes et également les dates (ligne).

Je ne peux malheureusement pas récupérer mes données grâce à la formule dans le cas présent car il y une nouvelle condition : le numéro de tableau ainsi que la présence d'une ligne de date par tableau.

D'où ma requête.

Pour résumer :

La formule ou code VBA doit trouver le bon tableau (critère se trouve feuil2 dans la colonne A) puis ensuite trouver l'intersection entre code entreprise, variable (M1 ; N1 et O1) avec les dates (qui se trouvent en ligne sur chaque tableau dans la feuil1).

Voila,

J'espère avoir été plus clair et m'excuse si ce n'est pas le cas...

Merci beaucoup,

Loïc

 

[vgendron]

il me semble que c'est ce que fait le code que je t'ai proposé... ??

 

[Loïc DUBOIS]

il me semble que c'est ce que fait le code que je t'ai proposé... ??

Ah excuse moi je n'avais pas vu ton message (j'ai du écrire le miens plus ou moins en même temps), je vais tester cela, merci beaucoup en tout cas

 

[Loïc DUBOIS]

en PJ une proposition par macro

plusieurs choses:
1) cliquer UNE fois sur le bouton "Créer tables"
cette macro transforme ta feuille 1 en plusieurs tables dont chacune s'appelle du nom de la première colonne
==> tu devrais donc avoir 48 tables sur ton fichier final

2) ensuite cliquer sur le bouton "remplir Feuil2"
cett macro parcourt remplit les lignes une à une

NOTE: il va falloir que tu revois ta méthode pour identifier les dates de la colonne L
il y a des dates vides et surtout.. plus genant. des dates qui n'en sont pas.. exemple.. le 29/02/2018

Merci pour ta proposition. J'ai juste un petit problème : lorsque je clique sur remplir feuil2, j'ai un message d'erreur (voir screen)

Merci d'avance,

Loïc

 

[vgendron]

correction avec cette version pour la création des tables de la feuille 1
le bouton est sur la feuill2 et le code disait with activesheet au lieu de with sheets("Feuil1")

maintenant, cette macro de création de table suppose que les plages 43 44 45.. sont séparées de 2 ou 3 lignes vides comme dans l'exemple fourni

 

[Loïc DUBOIS]

correction avec cette version pour la création des tables de la feuille 1
le bouton est sur la feuill2 et le code disait with activesheet au lieu de with sheets("Feuil1")

maintenant, cette macro de création de table suppose que les plages 43 44 45.. sont séparées de 2 ou 3 lignes vides comme dans l'exemple fourni

Merci beaucoup cela à l'air de très bien fonctionné.
Il faut que je set up mon fichier complet pour pouvoir tester sur l'ensemble de mes données.
Donc si je comprend bien le code, j'ai juste à ajouter des blocs de cette formes :
If .ListColumns(2).DataBodyRange(j) = "Total Debt" Then
TotDebt = .DataBodyRange(j, col)
En ajoutant mes différents noms de variables ?

Tout le reste du code reste inchangé même si ma feuil1 fait 700k lignes ?

Merci beaucoup !

 

[vgendron]

effectivement, si tu as plus de "colonnes " remplir, il faut un bloc par colonne..
mais bon. si tu as 20 colonnes.. c'est peut etre plus simple de rajouter une boucle

par contre;. avec 700k lignes.. je pense qu'il va falloir etre patient.. très patient...
il faudrait sans doute passer par des tableaux VBA (encore une truc différent des tables strucuturées
ces tables strucuturés (Listobject) seraient chargées dans un tablo vba (dim TabData() as variant

et les recherches se font directement dans ces tablos (plus rapide) avant de coller le résultat sur la feuille

je regarderai ce que je peux te proposer

 

[vgendron]

pour les tests,
il faudrait que tu postes un fichier avec au moins 2 tableaux (43 44) avec tous les critères (TotRevenu, totDebt, totAsset......) qui sont utilisés pour générer les colonnes à remplir

est ce que ces critères sont listés quelques part? est ce qu'ils sont communs à TOUS les tableaux (43 44...)
est ce que leur liste est prédéfinies et fixe ou peut elle évoluer selon tes importations (génération de la feuille 1) ??

 

[Loïc DUBOIS]

pour les tests,
il faudrait que tu postes un fichier avec au moins 2 tableaux (43 44) avec tous les critères (TotRevenu, totDebt, totAsset......) qui sont utilisés pour générer les colonnes à remplir

est ce que ces critères sont listés quelques part? est ce qu'ils sont communs à TOUS les tableaux (43 44...)
est ce que leur liste est prédéfinies et fixe ou peut elle évoluer selon tes importations (génération de la feuille 1) ??

Voici le fichier.
Oui les 14 variables sont communs à tous les tableaux.
Cette liste est effectivement fixe.

Merci beaucoup

 

[vgendron]

Alors voici une version légèrement différente et que j'ai commentée pour expliquer le code

note: à l'origine dans ton fichier, la colonne 3 (qui contient l'entreprise) de la table 43, les nombres sont stockés sous forme de texte==> il faut modifier pour en faire des nombres.. sinon, la macro de remplissage ne trouve pas les entreprises.. et donc ne remplit rien
**************
tout en écrivant, je viens de faire une modif du code qui devrait permettre de contourner le pb.. et donc pas besoin de traiter la feuille 1 mais j'ai pas essayé==> tu me diras
************

note2: dans la feuille 2, les colonnes à remplir sont dans l'ordre alpha: indispensable pour que les infos soient remplies à la bonne colonne

note3: le code est fait de telle sorte qu'il est admis que dans la feuille 1 TOUTES les entreprises ont les 14 lignes
un tri alpha est effectué


Je te laisse regarder le code et les commentaires

dernière remarque.. ca va beaucoup plus vite que je ne pensais. (moins d'une seconde)==> à voir maintenant avec ton fichier complet

 

[Loïc DUBOIS]

Alors voici une version légèrement différente et que j'ai commentée pour expliquer le code

note: à l'origine dans ton fichier, la colonne 3 (qui contient l'entreprise) de la table 43, les nombres sont stockés sous forme de texte==> il faut modifier pour en faire des nombres.. sinon, la macro de remplissage ne trouve pas les entreprises.. et donc ne remplit rien
**************
tout en écrivant, je viens de faire une modif du code qui devrait permettre de contourner le pb.. et donc pas besoin de traiter la feuille 1 mais j'ai pas essayé==> tu me diras
************

note2: dans la feuille 2, les colonnes à remplir sont dans l'ordre alpha: indispensable pour que les infos soient remplies à la bonne colonne

note3: le code est fait de telle sorte qu'il est admis que dans la feuille 1 TOUTES les entreprises ont les 14 lignes
un tri alpha est effectué


Je te laisse regarder le code et les commentaires

dernière remarque.. ca va beaucoup plus vite que je ne pensais. (moins d'une seconde)==> à voir maintenant avec ton fichier complet

Merci beaucoup pour ton investissement,

J'ai juste une dernière question, est ce que dans ma feuil2 je suis obligé de laisser une ligne entre chaque tableau (comme fait dans mon exemple) ou alors si je met tout à la chaine il n'y a pas de soucis ?

Ps : je fini de set up mes données et j'essaie la macro dès demain,

Je te tiens au courant,

Loïc

 

[vgendron]

non pour la feuille 2, pas de souci, tu peux tout mettre à la suite

 

[Loïc DUBOIS]

non pour la feuille 2, pas de souci, tu peux tout mettre à la suite

Parfait je vais essayer cela.

merci beaucoup

 

[Loïc DUBOIS]

Alors voici une version légèrement différente et que j'ai commentée pour expliquer le code

note: à l'origine dans ton fichier, la colonne 3 (qui contient l'entreprise) de la table 43, les nombres sont stockés sous forme de texte==> il faut modifier pour en faire des nombres.. sinon, la macro de remplissage ne trouve pas les entreprises.. et donc ne remplit rien
**************
tout en écrivant, je viens de faire une modif du code qui devrait permettre de contourner le pb.. et donc pas besoin de traiter la feuille 1 mais j'ai pas essayé==> tu me diras
************

note2: dans la feuille 2, les colonnes à remplir sont dans l'ordre alpha: indispensable pour que les infos soient remplies à la bonne colonne

note3: le code est fait de telle sorte qu'il est admis que dans la feuille 1 TOUTES les entreprises ont les 14 lignes
un tri alpha est effectué


Je te laisse regarder le code et les commentaires

dernière remarque.. ca va beaucoup plus vite que je ne pensais. (moins d'une seconde)==> à voir maintenant avec ton fichier complet

J'ai appliqué le code. Au final, j'ai eu quelques bugs mais dans l'ensemble la solution fonctionne.
Mes lignes correspondants au premier tableaux ne se sont pas remplies (pas grave j'ai fais une formule plus simple pour combler ce bug). Le second bug intervient au niveau de l'ordre des variables. Par exemple, les datas sont mélangés (Total revenue devait être dans la colonne M (première variable) mais au final les résultats se sont affichés dans la colonne Z, etc). Donc une fois que l'on a compris l'ordre, ce n'est pas un problème.

Merci beaucoup,

Je vais retravailler dessus demain voir si il n'y a pas d'autres problèmes en creusant mais j'ai l'impression que la solution a fonctionné.

Bonne soirée Vgendron,

Ps : Je te tiens au courant de l'avancé.
Loïc