Discussion :

[cafeine]

Avantages :

• caractères génériques supportés
• concaténation
• colonne récupérée n'appartenant pas au tableau "matrice" pouvant même être avant le tableau

Inconvénient :

• Plus lente que la fonction native RECHERCHEV / VLOOKUP

Exemple d'utilisation

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=ConcatVLookUp("Frédéri*";A1:A500;2;VRAI;" - ")

ramène la deuxième colonne pour les Frédéric et Frédérique en concaténant les valeurs avec " - " comme séparateur

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
Function ConcatVLookUp(ByVal ValRecherche, _
                       ByVal TabMatrice As Range, _
                       ByVal IndexCol, _
              Optional ByVal blnConcat As Boolean = False, _
              Optional ByVal Separateur = ";") As Variant
 
' Permet une recherchev sur des caractères génériques
'
Dim c As Range
 
application.Volatile
 
For Each c In TabMatrice.Cells
    If c.Value Like ValRecherche Then
        ConcatVLookUp = ConcatVLookUp & Separateur & c.Offset(0, IndexCol - 1).Value
        If Not blnConcat Then Exit For
    End If
Next c
ConcatVLookUp = Mid(ConcatVLookUp, Len(Separateur) + 1)
 
Set c = Nothing
End Function

[scuffle]

Super ! en effet c'est la meilleur solution pour afficher plusieurs resultats a une seule rechercheV

Par contre, je travail actuellement sur un fichier de plus de 14.000 entrees. Je ne sais pas si c'est l'ordi qui est trop vieux, mais excel est en train de patoger ! il met tres longtemps a faire parvenir le resultat pour toutes les lignes.


Donc fonction TRES puissante, mais demandant beaucoup de ressources pour gros fichier !

Dans tous les cas, c'est la meilleure que j'ai pu trouver.

Merci !!

[cafeine]

Bonjour,

merci pour ce bienveillant commentaire
il est vrai que cette fonction est notoirement sous-performante puisqu'elle parcourt la plage de recherche intégralement.
Une possible optimisation serait d'utiliser un recordset DAO.
Si j'ai le temps (humpf) je ferais bien un benchmark ...

[cafeine]

Alors en fait ma proposition est encore moins performante (35% plus lent) sur un fichier de 12500 lignes.

Je donne le code malgré tout dans une démarche pédagogique :

Code VB :

Sélectionner tout - Visualiser dans une fenêtre à part

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
28
29
30
31
32
33
34
35
36
37
38
39
40
Function ConcatVlookUp_RS(ByVal ValRecherche As Range, _
                          ByVal Table As Range, _
                          ByVal IndexColSearch As Integer, _
                          ByVal IndexColResult As Integer, _
                 Optional ByVal blnConcat As Boolean = False, _
                 Optional ByVal separateur = ";") As Variant
 
Dim t0 As Single
 
t0 = Timer
 
    Dim dbEng As Object
    Dim db As Object
    Dim rs As Object
 
    Dim sql As String
 
    Set dbEng = CreateObject("DAO.DBEngine.36")
 
    Set db = dbEng.Workspaces(0).OpenDatabase(ValRecherche.Parent.Parent.FullName, False, False, "Excel 8.0;HDR=NO;")
    sql = "SELECT [F" & IndexColResult & "] FROM [" & Table.Parent.Name & "$" & Table.Address(False, False, xlA1) & "] " & _
           "WHERE [F" & IndexColSearch & "] Like '" & ValRecherche.Value & "'"
    Set rs = db.openrecordset(sql, 4)
 
    rs.movefirst
    Do While Not (rs.EOF)
        ConcatVlookUp_RS = ConcatVlookUp_RS & separateur & rs.fields(0).Value
        rs.movenext
    Loop
    rs.Close
 
    ConcatVlookUp_RS = Mid(ConcatVlookUp_RS, Len(separateur) + 1)
 
    Set rs = Nothing
    Set db = Nothing
    Set dbEng = Nothing
 
Debug.Print "RS - " & Timer - t0; ""
 
End Function