Microsoft 365 formule spécifique Excel

[richardlion]

Bonjour a tous,

je recherche une formule qui me permettrait d'extraire a partir du fichier en PJ tous les articles qui ont uniquement plusieurs adresses contenant un z.

exemple: si vous faite un filtre sur l'articles 101913 il aura 3 fois BLZ.

a votre dispsosition pour échange

 

[sylvanu]

comment fonctionne la macro je n'ai pas bien compris

1- Il faut avoir activé les macros.
2- Il suffit de sélectionner la feuille Filtrage pour que la macro s'exécute automatiquement et remet les données à jour.

 

[richardlion]

Bonsoir Cousinhub,
Ce n'est pas ce que j'ai compris :

Regarde la pièce jointe 1218302
Donc 2140 doit ressortir car ill a un "Z" à la fin.

Bonsoir tout le monde,
Peut être une autre approche.
Avec de grandes bases de données cela fait beaucoup de formules.
On peut le faire avec une macro, celle ci ne s'exécute que quand on sélectionne la feuille Filtrage.
Donc le fichier est moins lourd et les calculs ne s'effectuent que quand c'est utile.

Bonjour Sylvanu, désolé mais Cousinhub a raison, je recherche des codes qui n'ont que des adresse en Z si j'ai un code avec BF et aussi BFZ c'est pas bon, merci a vous

 

[djidji59430]

Bonjour désolé je n'avais pas vu ta deuxième réponse mais c'est toujours pas bon, je retrouve des articles avec un Z et sans z,
les codes ont plusieurs adresses je veux extraire les codes qui n'ont que des adresses en Z et pas des codes avec et sans Z

lesquels ?

 

[sylvanu]

Re,
Un essai en PJ, mais c'est beaucoup plus long, environ 5s. mais ne se fait qu'à la sélection de la feuille Filtrage.

 

[Cousinhub]

Re,
Un essai en PJ, mais c'est beaucoup plus long, environ 5s. mais ne se fait qu'à la sélection de la feuille Filtrage.

Re-,
Mêmes résultats que j'avais obtenus
Par contre, juste 0.2 seconde chez moi
👍

 

[richardlion]

Merci a tous pour vos tests, merci a sylvanu pour votre macro qui réponds tres rapidement

 

[Cousinhub]

Merci a tous pour vos tests, merci a sylvanu pour votre macro qui réponds tres rapidement

Hi,
La prochaine fois, lis bien tous les posts...
J'ai l'impression que tu lis en full-speed, et ne regarde que le dernier.
Y'a pas que Mary Poppins dans la vie 🙂

 

[job75]

J'avais mal compris le problème, alors voyez le fichier joint qui récupère 164 articles sans doublon.

Formule en D2 :

=(NB.SI(B:B;B2)=NB.SI.ENS(B:B;B2;C:C;"*z*"))

Formule matricielle en F2 :

=SIERREUR(INDEX(B:B;PETITE.VALEUR(SI(NON(NB.SI(F$1:F1;B$2:B$10000))*D$2:D$10000;LIGNE($B$2:$B$10000));1));"")

Le recalcul des 164 formules se fait chez moi en 3,9 secondes.

Edit : chez moi la macro de sylvanu s'exécute en 3,5 secondes.

Bonne nuit.

 

[job75]

Bonjour le forum,

J'ai mis des plages illimitées pour la formule en colonne D, ça ne change pas la durée du calcul.

A+

 

[job75]

Maintenant voici une solution VBA avec cette macro qui utilise 3 Dictionary :

Private Sub Worksheet_Change(ByVal Target As Range)
Dim t, d1 As Object, d2 As Object, d3 As Object, tablo, resu(), i&, x$, y$, z$, n&
t = Timer
Set d1 = CreateObject("Scripting.Dictionary")
d1.CompareMode = vbTextCompare 'la casse est ignorée
Set d2 = CreateObject("Scripting.Dictionary")
d2.CompareMode = vbTextCompare 'la casse est ignorée
Set d3 = CreateObject("Scripting.Dictionary")
d3.CompareMode = vbTextCompare 'la casse est ignorée
tablo = [A1].CurrentRegion.Resize(, 3) 'matrice, plus rapide
ReDim resu(1 To UBound(tablo), 1 To 3)
For i = 2 To UBound(tablo)
    x = tablo(i, 2): y = tablo(i, 3)
    d1(x) = d1(x) + 1
    If LCase(y) Like "*z*" Then
        z = x & y
        d2(z) = d2(z) + 1
    End If
Next i
For i = 2 To UBound(tablo)
    x = tablo(i, 2): y = tablo(i, 3): z = x & y
    If d1(x) = d2(z) Then
        If Not d3.exists(x) Then
            n = n + 1
            d3(x) = n
            resu(n, 1) = x
            resu(n, 2) = d1(x)
            resu(n, 3) = y
        End If
    End If
Next i
'---restitution---
Application.EnableEvents = False 'désactive les évènements
With [E3] '1ère cellule de destination
    If n Then .Resize(n, 3) = resu
    .Offset(n).Resize(Rows.Count - n - .Row + 1, 3).ClearContents 'RAZ en dessous
End With
Application.EnableEvents = True 'réactive les évènements
With UsedRange: End With 'actualise la barre de défilement verticale
MsgBox n & " articles trouvés en " & Format(Timer - t, "0.00 \sec")
End Sub

Elle se déclenche quand on modifie ou valide une cellule quelconque.

Elle s'exécute chez moi en 0,06 seconde.

 

[sylvanu]

Bonjour Job,
Effectivement très rapide.
Cependant il me semble qu'on ne doit conserver uniquement les articles qui ont plusieurs adresses finissant par "z".

tous les articles qui ont uniquement plusieurs adresses contenant un z.

 

[djidji59430]

Bonjour à tous,

J'ai essayé avec power query .....
Les doublons d'articles dont les adresses ne sont pas identiques et qui doivent se terminer par "Z" sont supprimés (genre 2140) ainsi que ceux dont l'adresse ne se termine pas en "Z".
Je n'ai pas de réponses pour comparer.......

 

[job75]

Cependant il me semble qu'on ne doit conserver uniquement les articles qui ont plusieurs adresses finissant par "z".

Ce n'est pas logique d'éliminer les articles dont le nombre est 1 mais s'il le faut c'est facile.

Dans le fichier .xlsx entrez en D2 :

=SI(NB.SI(B:B;B2)=NB.SI.ENS(B:B;B2;C:C;"*z*");NB.SI(B:B;B2)>1)

Les 8 articles en colonne F sont recalculés en 0,05 seconde.

Dans le fichier .xlsm complétez la 2ème boucle :

For i = 2 To UBound(tablo)
    x = tablo(i, 2): y = tablo(i, 3): z = x & y
    If d1(x) = d2(z) Then
        If d1(x) > 1 Then
            If Not d3.exists(x) Then
                n = n + 1
                d3(x) = n
                resu(n, 1) = x
                resu(n, 2) = d1(x)
                resu(n, 3) = y
            End If
        End If
    End If
Next i

la durée reste la même à 0,06 seconde.

 

[job75]

Notez que mes solutions par formules ou VBA supposent que les articles en doublon ont la même adresse contenant "z".

C'est bien le cas dans le tableau source fourni.

Si ce n'est pas le cas les articles concernés seront éliminés avec la solution VBA mais pas avec la solution par formules.

 

[job75]

Bonjour le forum,

Voici une autre solution qui tient compte de la remarque précédente, pour l'article 32814 j'ai mis en C3053 l'adresse xxxBQZ.

Pour le fichier .xlsx j'ai mis en F2 la formule matricielle :

=SIERREUR(INDEX(B:B;PETITE.VALEUR(SI(NON(NB.SI.ENS($F$1:$F1;$B$2:$B$10000;$G$1:$G1;$C$2:$C$10000))*$D$2:$D$10000;LIGNE($B$2:$B$10000));1));"")

tirée ensuite en G2 puis vers le bas.
Les 9 lignes trouvées se recalculent en [Edit] 0,18 seconde.

Pour le fichier .xlsm j'ai remanié la macro :

Private Sub Worksheet_Change(ByVal Target As Range)
Dim t, d1 As Object, d2 As Object, d3 As Object, tablo, resu(), i&, x$, y$, z$, n&, nn&
t = Timer
Set d1 = CreateObject("Scripting.Dictionary")
d1.CompareMode = vbTextCompare 'la casse est ignorée
Set d2 = CreateObject("Scripting.Dictionary")
d2.CompareMode = vbTextCompare 'la casse est ignorée
Set d3 = CreateObject("Scripting.Dictionary")
d3.CompareMode = vbTextCompare 'la casse est ignorée
tablo = [A1].CurrentRegion.Resize(, 3) 'matrice, plus rapide
ReDim resu(1 To UBound(tablo), 1 To 3)
For i = 2 To UBound(tablo)
    x = tablo(i, 2): y = LCase(tablo(i, 3))
    If y Like "*z*" Then d1(x) = d1(x) + 1 Else d2(x) = d2(x) + 1
Next i
For i = 2 To UBound(tablo)
    x = tablo(i, 2)
    If d1(x) > 1 And d2(x) = 0 Then
        y = tablo(i, 3): z = x & y
        If Not d3.exists(z) Then
            n = n + 1
            d3(z) = n 'mémorise la ligne
            resu(n, 1) = x
            resu(n, 2) = y
        End If
        nn = d3(z)
        resu(nn, 3) = resu(nn, 3) + 1 'comptage
    End If
Next i
'---restitution---
Application.EnableEvents = False 'désactive les évènements
With [E3] '1ère cellule de destination
    If n Then .Resize(n, 3) = resu
    .Offset(n).Resize(Rows.Count - n - .Row + 1, 3).ClearContents 'RAZ en dessous
End With
Application.EnableEvents = True 'réactive les évènements
With UsedRange: End With 'actualise la barre de défilement verticale
MsgBox n & " articles trouvés en " & Format(Timer - t, "0.00 \sec")
End Sub

Elle s'exécute toujours en 0,06 seconde.

A+