Mise en relation de colonnes

Version imprimable

13/09/2016, 10h08
maximeozenne

1 pièce(s) jointe(s)

Mise en relation de colonnes

Bonjour à tous !

Je dispose d'un classeur "Suivi" qui va chercher des infos dans deux autres classeurs "Devis" et "Chantier"

Pour simplifier, disons que le classeur Devis contient les colonnes "Numéro de devis", "Prix", "Chantier" et que le classeur Chantier contient les colonnes "Numéro de Chantier", "Date", "Type"

Les informations ne sont pas vraiment importantes. Ce qui nous intéresse ici sont les colonnes "Chantier" des devis et "Numéro de Chantier" des chantiers que je voudrais mettre en relation. Je m'explique :

En VBA je récupère toutes les infos des Devis dans mon classeur Suivi. Je fais une boucle sur les colonnes des devis pour les coller dans le suivi. Là où ça devient plus compliqué, c'est que lorsque la colonne "Chantier" des devis est renseignée, je dois ajouter les informations des chantiers correspondantes au devis dans mon classeur "Suivi" comme illustré ci-dessous :

Pièce jointe 220188

Je précise qu'à l'ouverture du classeur Suivi, les informations sont mises à jours (suppression totale des données pour aller à nouveau les chercher dans les devis/chantiers)

Je pensais faire une itération sur la colonne Chantier des Devis et lorsque qu'elle n'est pas vide, faire une itération sur la colonne Numéro de Chantier des Chantiers pour aller ensuite copier coller les informations. Mais sachant que l'on brasse des milliers de devis avec ce fichier, la manip' risque d'être extrêmement longue surtout si elle se déroule à chaque ouverture de fichier. C'est pourquoi je me tourne vers vous, je cherche une solution différente de l'itération qui pourrait optimisation la vitesse d'ouverture.

Si possible j'aimerais éviter de rajouter une formule dans les cases du type "RECHERCHEV" ou encore "SI" (en gros si la colonne chantier n'est pas vide, afficher la valeur des chantiers) tout d'abord car les informations viennent de classeurs différents donc je devrais d'abord copier TOUTES les informations des devis ET des chantiers dans un seul classeur pour pouvoir utiliser ce genre de formule, et j'aimerais aussi que la donnée soit écrite en brute dans la cellule plutôt que ça soit le résultat d'une formule.

Merci de votre attention !
13/09/2016, 10h20
Invité

1 pièce(s) jointe(s)

bonjour,
si tu plaçais ton tableaux comme ça:

A A A A A A

A A A A A A

A A A A A A

A A A A A A

A A A A A A

A A A A A A

A A A A A A

A A A A A A

A A A A A A

A A A A A A

A A A A A A

on aurait pas besoin de licence Photoshop!
13/09/2016, 10h33
maximeozenne

Merci du conseil, vous conviendrez tout de même qu'un simple screenshot est plus rapide que la mise en place du tableau via l'outil HTML du site...

C'était surtout un schéma pour la compréhension, afin que les connaisseurs du problème puissent comprendre facilement afin de me faire part de leur expérience. Je ne pensais pas que vous vous appuieriez dessus pour faire des tests, je m'en excuse.
13/09/2016, 10h38
Invité

tu conviendra qu'il est plus facile de t'aider en faisant un copier coller quand réécrivant toutes tes information!

vue qu'une requête sql via ado fait tout le travail en une seule fois et que je t'imagine incapable (mais je me trompe peut-être) de la rédiger!
13/09/2016, 10h49
maximeozenne

Soit, encore une fois je ne pensais pas que l'on se servirait de mon exemple pour faire des tests. Voici donc les différents tableaux :

DEVIS

Numéro de devis Prix Chantier

1 10

2 10 3

3 50

4 100 5

CHANTIER

Numéro de chantier Date Type

1 01/01/2016 A

2 13/03/2016 A

3 14/05/2016 A

4 28/08/2016 B

5 13/09/2016 B

SUIVI

Numéro de devis Prix Chantier Date Type

1 10

2 10 3 14/05/2016 B

3 50

4 100 5 13/09/2016 B

Encore merci de votre attention.
13/09/2016, 11h04
halaster08

Bonjour,

Citation:

Envoyé par maximeozenne

Si possible j'aimerais éviter de rajouter une formule dans les cases du type "RECHERCHEV" ou encore "SI" (en gros si la colonne chantier n'est pas vide, afficher la valeur des chantiers) tout d'abord car les informations viennent de classeurs différents donc je devrais d'abord copier TOUTES les informations des devis ET des chantiers dans un seul classeur pour pouvoir utiliser ce genre de formule,

Pourquoi la rechercheV neccésiterait que toutes les données soit dans un seul classeur?
De ce que je vois de ton exemple tu peux copier les infos du devis, puis faire une rechercheV sur le numéro de chantier dans le fichier chantier pour obtenir les deux infos qu'il te manque.

Citation:

et j'aimerais aussi que la donnée soit écrite en brute dans la cellule plutôt que ça soit le résultat d'une formule.

Une fois la recherche effectuée, tu peux copier coller le résultat en valeur pour faire disparaitre la formule.
13/09/2016, 11h14
maximeozenne

Merci de ta réponse !

C'est vrai que vu que je copie les données depuis le classeur chantier, il est déjà ouvert, du coup je pourrais faire un RECHERCHEV... Après je trouvais ça un peu "sale" de faire une formule pour ensuite copier coller le résultat mais bon. Ça doit être la solution la plus simple et plutôt rapide. Je te remercie :)

1 pièce(s) jointe(s)

Perso je t'avais prévenu que tu ne pourrais pas mettre ma solution seul!

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
Option Explicit
Sub Compil()
Dim F As String, vchrono As Date, Rep As String, Rs As Object, Sql As String
vchrono = Now()
 
Rep = ThisWorkbook.Path & "\"
ThisWorkbook.Worksheets("Feuil1").Range("A2:E" & Cells.Rows.Count).EntireRow.Delete
With CreateObject("adodb.connection")
            '.Provider = "Microsoft.Jet.OLEDB.4.0"
            .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" _
                & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0;HDR=YES;IMEX=1"""
            .Open
          If Dir(Rep & "DEVIS.xlsx") <> "" Then
             If Dir(Rep & "CHANTIER.xlsx") <> "" Then
                    Sql = "select distinct  [Numéro de devis],[Prix],[Chantier],[Date],[Type] from(select * from [DEVIS$] in '" & Rep & "DEVIS.xlsx' 'Excel 12.0;HDR=YES;IMEX=1;Mode=Read;') as frm1 "
                    Sql = Sql & "left join (select * from [CHANTIER$] in '" & Rep & "CHANTIER.xlsx' 'Excel 12.0;HDR=YES;IMEX=1;Mode=Read;' )as frm2 "
                    Sql = Sql & "On frm1.[Chantier] =frm2.[Numéro de chantier] "
                    Set Rs = .Execute(Sql)
 
              ThisWorkbook.Worksheets("Feuil1").Range("A2").CopyFromRecordset Rs
              Rs.Close: Set Rs = Nothing
            End If
          End If
            .Close
End With
 MsgBox "compil réalisé en " & (Now - vchrono) / 1000 & " secondes !"
End Sub

13/09/2016, 13h03
RyuAutodidacte

Bonjour,
je pense que tu aurais aussi pu utiliser un INDEX EQUIV qui marche en fichier fermé
et faire concorder les données

Code:

=SIERREUR(INDEX([Equiv2.xls]Feuil1!$A$2:$C$6;EQUIV(C2;[Equiv2.xls]Feuil1!$A$2:$A$6;0);2);"")

Code:

=SIERREUR(INDEX([Equiv2.xls]Feuil1!$A$2:$C$6;EQUIV(C2;[Equiv2.xls]Feuil1!$A$2:$A$6;0);3);"")

J'ai tout fait à partir de la colonne A
Ton 3è tableau est le tableau 1 ou tu as rajouté la colonne Date et la colonne type

A	A	A	A	A	A
A	A	A	A	A	A
A	A	A	A	A	A
A	A	A	A	A	A
A	A	A	A	A	A
A	A	A	A	A	A
A	A	A	A	A	A
A	A	A	A	A	A
A	A	A	A	A	A
A	A	A	A	A	A
A	A	A	A	A	A

Numéro de chantier	Date	Type
1	01/01/2016	A
2	13/03/2016	A
3	14/05/2016	A
4	28/08/2016	B
5	13/09/2016	B

Numéro de devis	Prix	Chantier	Date	Type
1	10
2	10	3	14/05/2016	B
3	50
4	100	5	13/09/2016	B

A	A	A	A	A	A
A	A	A	A	A	A
A	A	A	A	A	A
A	A	A	A	A	A
A	A	A	A	A	A
A	A	A	A	A	A
A	A	A	A	A	A
A	A	A	A	A	A
A	A	A	A	A	A
A	A	A	A	A	A
A	A	A	A	A	A

A	A	A	A	A	A
A	A	A	A	A	A
A	A	A	A	A	A
A	A	A	A	A	A
A	A	A	A	A	A
A	A	A	A	A	A
A	A	A	A	A	A
A	A	A	A	A	A
A	A	A	A	A	A
A	A	A	A	A	A
A	A	A	A	A	A