XL 2019 Cherche valeur dans un tableau

[sum01]

Bonsoir le forum,

Petit casse-tête en ce qui me concerne. Dans un tableau dont le nombre de colonnes et de lignes (Feuille 2) peut varier en fonction des besoins, j'aimerais trouver une formule qui me permette d'alimenter le tableau (Feuille 1). En effet, le tableau pays (Feuille 1) doit être alimenté sur la base du tableau des pays (Feuille 2) qui contiennent la valeur "merci".
Je joins le fichier Excel.

Merci beaucoup pour votre aide.
Bonne soirée

 

[sylvanu]

Bonjour Sum,
Un essai en PJ avec cette macro qui s'exécute automatiquement lorsqu'on sélectionne la Feuil1 :

Sub Worksheet_Activate()
    Dim T, L%, C%, N%, Pays$, Mot$
    Mot = "merci"   '<------------------------------ Mot à rechercher
    Mot = "*" & Mot & "*"
    Application.ScreenUpdating = False
    Range("B1:Z1000").ClearContents
    T = Sheets("Feuil2").[A1].CurrentRegion: L = 2  ' "T" tableau data, "L" ligne écriture
    While Cells(L, "A") <> ""
        C = 2: Pays = Cells(L, "A")                 ' "C" colonne à écrire, "Pays" à analyser
        For N = 1 To UBound(T, 2)
            If T(1, N) = Pays Then Exit For         ' N contient la colonne du pays
        Next N
        For P = 1 To UBound(T)
            If LCase(T(P, N)) Like Mot Then         ' Si ligne contient le mot
                Cells(L, C) = T(P, N): C = C + 1    ' Ecrire phrase, et incrémenter colonne
            End If
        Next P
        L = L + 1                                   ' Saut de ligne pour pays suivant
    Wend
    Columns("B:K").ColumnWidth = 200
    Columns.AutoFit: Rows.AutoFit                   ' Ajustement largeur colonnes et hauteur lignes
End Sub

 

[job75]

Bonsoir sumo1, sylvanu,

Formule matricielle en Feuil1!A2 :

=SIERREUR(INDEX(Feuil2!A$1:L$1;PETITE.VALEUR(SI(ESTNUM(CHERCHE("merci";Feuil2!A$2:L$1000));COLONNE(Feuil2!A$1:L$1));LIGNE()-1));"")

La limite 1000 est à adapter.

A+

 

[job75]

Notez que si dans une colonne en Feuil2 il y a 2 cellules avec "merci" le pays sera listé 2 fois.

 

[sum01]

Bonsoir sumo1, sylvanu,

Formule matricielle en Feuil1!A2 :

=SIERREUR(INDEX(Feuil2!A$1:L$1;PETITE.VALEUR(SI(ESTNUM(CHERCHE("merci";Feuil2!A$2:L$1000));COLONNE(Feuil2!A$1:L$1));LIGNE()-1));"")

La limite 1000 est à adapter.

A+

Bonjour Job75,
Merci beaucoup pour votre réponse. Cela fonctionne à merveille.
Bonne soirée

 

[sum01]

Bonjour Sum,
Un essai en PJ avec cette macro qui s'exécute automatiquement lorsqu'on sélectionne la Feuil1 :

Sub Worksheet_Activate()
    Dim T, L%, C%, N%, Pays$, Mot$
    Mot = "merci"   '<------------------------------ Mot à rechercher
    Mot = "*" & Mot & "*"
    Application.ScreenUpdating = False
    Range("B1:Z1000").ClearContents
    T = Sheets("Feuil2").[A1].CurrentRegion: L = 2  ' "T" tableau data, "L" ligne écriture
    While Cells(L, "A") <> ""
        C = 2: Pays = Cells(L, "A")                 ' "C" colonne à écrire, "Pays" à analyser
        For N = 1 To UBound(T, 2)
            If T(1, N) = Pays Then Exit For         ' N contient la colonne du pays
        Next N
        For P = 1 To UBound(T)
            If LCase(T(P, N)) Like Mot Then         ' Si ligne contient le mot
                Cells(L, C) = T(P, N): C = C + 1    ' Ecrire phrase, et incrémenter colonne
            End If
        Next P
        L = L + 1                                   ' Saut de ligne pour pays suivant
    Wend
    Columns("B:K").ColumnWidth = 200
    Columns.AutoFit: Rows.AutoFit                   ' Ajustement largeur colonnes et hauteur lignes
End Sub

Bonsoir Sylvanu,
Merci infiniment pour votre réponse et ce code qui exprime les résultats attendus. C'est excellent.
Bonne soirée à vous

 

[R@chid]

Bonjour @ tous,
une variante avec Power Query.

Cordialement

 

[job75]

Bonjour le forum,

Avec ma solution si l'on veut une liste sans doublon il faut une 2ème colonne.

Formule matricielle en Feuil1!B2 :

=SI(A2="";"";INDEX(A:A;PETITE.VALEUR(SI(NON(NB.SI(B$1:B1;A$2:A$1000));LIGNE(A$2:A$1000));1)))

Salut cher R@chid.

@sum01 ce n'est pas votre post #5 qu'il faut noter comme solution mais le post qui donne la solution que vous utiliserez.

A+

 

[job75]

Avec la même logique voyez cette solution VBA :

Private Sub Worksheet_Activate()
Dim critere$, P As Range, ncol%, col%, a$(), n%
critere = "*merci*"
Set P = Sheets("Feuil2").UsedRange
ncol = P.Columns.Count
For col = 1 To ncol
    If Application.CountIf(P.Columns(col), critere) Then
        ReDim Preserve a(n) 'base 0
        a(n) = P(1, col)
        n = n + 1
    End If
Next
If n Then tri a, 0, n - 1
'---restitution---
If FilterMode Then ShowAllData 'si la feuille est filtrée
With [A2] '1ère cellule de destination
    If n Then .Resize(n) = Application.Transpose(a) 'Transpose est limitée à 65536 lignes
    .Offset(n).Resize(Rows.Count - n - .Row + 1).ClearContents 'RAZ en dessous
End With
End Sub

Sub tri(a, gauc, droi) ' Quick sort
Dim ref, g, d, temp
ref = a((gauc + droi) \ 2)
g = gauc: d = droi
Do
    Do While a(g) < ref: g = g + 1: Loop
    Do While ref < a(d): d = d - 1: Loop
    If g <= d Then
      temp = a(g): a(g) = a(d): a(d) = temp
      g = g + 1: d = d - 1
    End If
Loop While g <= d
If g < droi Then Call tri(a, g, droi)
If gauc < d Then Call tri(a, gauc, d)
End Sub

Le code est dans la 1ère feuille et s'exécute quand la feuille est activée.

 

[sum01]

Bonjour le forum,

Avec ma solution si l'on veut une liste sans doublon il faut une 2ème colonne.

Formule matricielle en Feuil1!B2 :

=SI(A2="";"";INDEX(A:A;PETITE.VALEUR(SI(NON(NB.SI(B$1:B1;A$2:A$1000));LIGNE(A$2:A$1000));1)))

Salut cher R@chid.

@sum01 ce n'est pas votre post #5 qu'il faut noter comme solution mais le post qui donne la solution que vous utiliserez.

A+

Bonjour Job75,

C'est noté, je rectifie en suivant la bonne procédure concernant la clôture des solutions. Dans ce cas, je reprends votre solution proposée aujourd'hui. Encore mille merci pour votre aide.
Bonne soirée à vous

 

[sum01]

Bonjour @ tous,
une variante avec Power Query.

Cordialement

Bonjour R@chid,
Merci beaucoup pour cette autre solution qui marche parfaitement bien également !
Bonne soirée