Microsoft 365 Recherche V

[walter ebelle]

Bsr à tous, svp j'ai besoin de votre aide sur la rechercheV. Voilà ma préoccupation est de ressortir toutes les linges contenant la valeur X qui se retrouve dans le 3e colonne d'une feuille excell A, feuille ayant plus de 1000 lignes et regrouper le résultat dans une autre feuille de calcul Excel. Besoin de solution svp. Merci

 

[walter ebelle]

Re,
Si tu as _xlfn._xlws.FILTER, c'est que tu n'as pas la version Microsoft 365, ou 2021 comme tu l'indiques en titre de post. La fonction Filtre n'est pas disponible dans les versions antérieures. Quelle est-ta version exacte ?

 

[ALS35]

Re,
On aperçoit vaguement un Excel 2016 en bas. La version exacte est dans Fichier, Compte, A propos d'Excel.
Si 2016 oriente-toi vers la solution de job75, ou la solution PowerQuery de Jeannette.

 

[job75]

Je comprends que vous voulez choisir dans une liste de validation le critère du filtrage.

Voyez le fichier joint avec cette formule matricielle en A2 :

=PETITE.VALEUR(SI((Feuil1!A$19:A$1000="X")*NON(NB.SI(A$1:A1;Feuil1!D$19:D$1000));Feuil1!D$19:D$1000);1)

La colonne A peut être masquée.

Formule de validation en E1 =DECALER($A$1;1;;NB($A:$A))

 

[walter ebelle]

Bonjour le forum,

S'il s'agit de filtrer le code sur 1574 c'est simple, formule matricielle en B4 :

=SIERREUR(INDEX(Feuil1!C:C;PETITE.VALEUR(SI((Feuil1!$A$1:$A$37="X")*(Feuil1!$D$1:$D$37=$C$1);LIGNE(Feuil1!$A$1:$A$37));LIGNE()-3));"")

Nota : j'ai corrigé la formule de mon post #6.

A+

Bjr JOB,
ta formule donne le résultat mais je ne comprends pas. où as-tu mis le code 1574 ? Et si je veux filtrer un autre code par exemple le 3140 quelle sera la formule ? Est-il possible d'utiliser ta formule sans ajouter une nouvelle colonne comme tu as fait ? Autre chose est ce que avec ma version Excel 2016 je peux écrire cette formule matricielle ? En somme je ne parvient pas à exploiter cette formule qui donne pourtant le résultat que je veux.Merci de m'aider

 

[job75]

Bonjour walter ebelle, le forum,

Dans les fichiers que j'ai joints le critère est coloré en jaune, cellules C1 ou E1.

Dans le fichier du post #19, pour les formules matricielles en A2 ou C4 procédez comme suit :

- sélectionnez la cellule

- cliquez dans la barre de formule et collez-y la formule

- validez par les 3 touches Ctrl Maj Entrée enfoncées simultanément.

A+

 

[job75]

Maintenant si vous ne voulez pas des colonnes A auxiliaires en Feuil1 et Feuil2 vous pouvez utiliser VBA.

Voyez le fichier .xlsm joint avec ces codes dans ThisWorkbook et dans Feuil2 :

Private Sub Workbook_Open()
Application.ScreenUpdating = False
Sheets("Feuil1").Activate
Sheets("Feuil2").Activate 'lance la macro Worksheet_Activate
Me.Saved = True 'évite l'invite à la fermeture si aucune modification
End Sub

Private Sub Worksheet_Activate()
Dim d As Object, tablo, i&, v, a, b
Set d = CreateObject("Scripting.Dictionary")
With Sheets("Feuil1")
    With .[A1].CurrentRegion.EntireRow
        .Sort .Columns(3), xlAscending, Header:=xlYes 'tri sur la 3ème colonne
        tablo = .Resize(, 3) 'matrice, plus rapide
    End With
    For i = 2 To UBound(tablo)
        d(tablo(i, 3)) = 0
    Next i
    tablo = .[A18].CurrentRegion.Resize(, 3) 'à adapter éventuellement
    For i = 2 To UBound(tablo)
        v = tablo(i, 3)
        If d.exists(v) Then d(v) = v
    Next i
     a = d.keys: b = d.items
    For i = 0 To UBound(a)
        If b(i) = 0 Then d.Remove a(i)
    Next i  
    [C1].Validation.Delete 'RAZ
    [E1].Resize(, Columns.Count - 4).ClearContents
    If d.Count Then [E1].Resize(, d.Count) = d.items: [C1].Validation.Add xlValidateList, Formula1:="=" & [E1].Resize(, d.Count).Address
    Worksheet_Change [C1] 'lance la macro
End With
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$C$1" Then Exit Sub
Application.ScreenUpdating = False
[A3].CurrentRegion.Clear 'RAZ
With Sheets("Feuil1").[A18].CurrentRegion
    .AutoFilter 3, [C1] 'Filtre automatique
    .Copy [A3]
    .Parent.AutoFilterMode = False 'ôte le filtre
End With
Rows(3).Font.Bold = True
End Sub

La Workbook_Open s'exécute à l'ouverture du fichier.

La Worksheet_Activate s'exécute à l'activation de Feuil2, elle crée la liste de validation.

La Worksheet_Change s'exécute quand on modifie la cellule C1, elle crée le tableau des résultats.

 

[job75]

Si les tableaux sources sont structurés il faut faire quelques aménagements.

 

[walter ebelle]

Bonjour walter ebelle, le forum,

Dans les fichiers que j'ai joints le critère est coloré en jaune, cellules C1 ou E1.

Dans le fichier du post #19, pour les formules matricielles en A2 ou C4 procédez comme suit :

- sélectionnez la cellule

- cliquez dans la barre de formule et collez-y la formule

- validez par les 3 touches Ctrl Maj Entrée enfoncées simultanément.

A+

Bsr Job,
Dans le réel sur la feuil1 le tableau1 a 573 col y compris la col ajoutée et 106 Lignes. Le tableau2 sur la feuil1 commence à la ligne485 jusqu'à la ligne 22166 et a le même nombre de col que le tableau1. Tel que ton exemple, j'ai écrit à la ligne 486 du tableau2 la même formule que voici : =REPT(‘’X'';ESTNUM(EQUIV(D486 ;D$2 😀$106;0))) et à la feuil2 j'ai copié l'entête du tableau2 en supprimant la colX ajoutée sur la feuil1 en écrivant la formule comme telle : {=SIERREUR(INDEX(Feuil1!B:B;PETITE.VALEUR(SI((Feuil1!$A$1:$A$222166=’’X'')*(Feuil1!$D$1:$D$22166=$C$1);LIGNE(Feuil1!$A$1:$A$22166));LIGNE()-3));’’’’)}
A la combien ligne1 tjrs sur la feuil2, j'ai écrit FILTRER CODE. Ala colC ligne1 j'écris le code à filtrer. Mais je j'obtiens pas le résultat recherché. J'ai une seule ligne qui s'affiche et aucune autre information n'apparaît sur le reste de colonnes. Je ne comprends pas.

 

[job75]

Bonjour walter ebelle,

Il faut que vous adaptiez les formules de mon post #19 à votre fichier réel.

Ce n'est pas très compliqué et je ne peux pas le faire à votre place.

A+

 

[walter ebelle]

Bonjour walter ebelle,

Il faut que vous adaptiez les formules de mon post #19 à votre fichier réel.

Ce n'est pas très compliqué et je ne peux pas le faire à votre place.

A+

BJR Job c'est ce que j'ai fait point par mais le résultat n'est pas le même.
B:B et Ligne()-3 C'est pourquoi stp ?

 

[job75]

B:B et Ligne()-3 C'est pourquoi stp ?

On utilise B:B en Feuil2!C4 parce qu'on recherche les valeurs en colonne B de Feuil1, en D4 c'est C:C.

-3 parce que les titres du tableau sont en ligne 3, en ligne 4 LIGNE()-3 vaut 1.

 

[job75]

Avec 573 colonnes et 22000 lignes le calcul des formules devrait prendre pas mal de temps 🙄

Il faudra utiliser les solutions VBA des posts #22 ou #23 ou bien passer par PowerQuery.

 

[job75]

J'ai créé un fichier de 573 colonnes, 113 lignes pour TABLEAU1, 22 000 lignes pour TABLEAU2.

Effectivement les formules prennent trop de temps.

Alors j'y ai mis les codes VBA de mon post #22.

Et là Excel me propose à l'ouverture de réparer le fichier, ceci à cause de la liste de validation.

C'est la création de la liste par Formula1:=Join(d.items, ",") qui pose problème et je ne comprends pas pourquoi.

J'ai donc modifié le code de la WorkSheet_Activate des posts #22 et €23 en utilisant une plage de cellules pour définir la liste de validation en C1.

 

[job75]

Bon j'ai compris, la méthode Formula1:=Join(d.items, ",") fonctionne sans problème à condition que le texte concaténé ne dépasse pas 255 caractères.

 

[job75]

Bonjour walter ebelle, le forum,

Le fichier que j'ai créé pèse 38 Mo, zippé il pèse encore 12,6 Mo, je l'ai mis sur cjoint.com :

https://www.cjoint.com/c/NJrelqUTCd7

A+