Microsoft 365 Si tous les légumes d'une chaîne se trouvent dans une autre chaîne alors on pourra supprimer cette chaîne

carlos

XLDnaute Impliqué
Supporter XLD
Bonjour,
Je cherche à nettoyer une colonne d'excel. Voir PJ.
par exemple si j'ai dans la même colonne :

Ligne 2 --> Pois/Carotte
Ligne 3 --> Pois/Carotte/Tomate
Ligne 4 --> Pois/Carotte/Asperge/Céleri

Ligne 23 --> Pois/Carotte/Tomate/Poireau

Principe : Si tous les légumes d'une chaîne se trouvent dans une autre chaîne alors on pourra supprimer cette chaîne
Donc dans mon exemple on pourra supprimer la ligne 2 et la ligne 3
J’espère avoir été assez clair.
Bonne soirée
 

Pièces jointes

  • Nettoyer les plus petites chaines.xlsx
    138.9 KB · Affichages: 21

job75

XLDnaute Barbatruc
Bonsoir carlos, chris,

Voyez le fichier joint et ces macros dans le code de la feuille "Résultat" :
VB:
Private Sub Worksheet_Activate()
Dim d As Object, tablo, resu$(), i&, s, x$, n&
Set d = CreateObject("Scripting.Dictionary")
d.CompareMode = vbTextCompare 'la casse est ignorée
tablo = Sheets("tout").UsedRange.Columns(1).Resize(, 2) 'matrice, plus rapide, au moins 2 éléments
ReDim resu(1 To Rows.Count, 1 To 1)
For i = 2 To UBound(tablo)
    s = Split(tablo(i, 1), "/")
    If UBound(s) > 0 Then
        tri s, 0, UBound(s) 'classement alphabétique sur chaque ligne
        tablo(i, 1) = Join(s, "/")
    End If
    x = tablo(i, 1)
    If x <> "" Then
        If Not d.exists(x) Then
            d(x) = ""
            n = n + 1
            resu(n, 1) = x
        End If
    End If
Next
'---restitution---
If FilterMode Then ShowAllData: DrawingObjects(1).Text = "Filtrer" 'si la feuille est filtrée
With [A2] '1ère cellule de restitution, à adapter
    If n Then
        .Resize(n) = resu
        .Offset(, 1).Resize(n) = "=COUNTIF(C1,SUBSTITUTE(RC[-1],""/"",""*""))"
    End If
    .Offset(n).Resize(Rows.Count - n - .Row + 1, 2).ClearContents 'RAZ en dessous
End With
Application.Goto [A1], True 'cadrage
End Sub

Sub Filtrer_RAZ()
With DrawingObjects(1)
    If .Text = "Filtrer" Then UsedRange.Resize(, 2).AutoFilter 2, 1 Else If FilterMode Then ShowAllData
    .Text = IIf(.Text = "RAZ", "Filtrer", "RAZ")
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
La macro principale se déclenche quand on active la feuille.

Les lignes de fréquence > 1 ne sont pas supprimées mais masquées via le bouton.

Bonne nuit.
 

Pièces jointes

  • Nettoyer les plus petites chaines(1).xlsm
    106.5 KB · Affichages: 6

chris

XLDnaute Barbatruc
RE à tous

J'ai comparé nos résultats :
  • je trouve 7 faux doubles et 8 faux simples dans les résultats de job75
  • j'avais des problèmes avec Courge et Courgette que j'ai corrigé
Pour voir les requêtes PowerQuery : Données, Obtenir des données, Lancer PowerQuery
Il y a 3 requêtes :
  • Uniques qui dédoublonne les 5588 lignes en 213 (comme le fait le code de job75
  • Doublons qui repère les combinatoires existant dans les uniques (25)
  • Final qui retire les doublons de Uniques
Pour chaque requêtes on peut voir les actions/étapes dans le volet de droite et les rouages en bout de ligne précise le détail de l'action

Pour les actions, dans 95% des cas on passe par les Onglets ou le clic droit, on ne code pas.
Si tu as des questions sur certaines actions, j'y répondrai demain en fin d'après-midi...
 

Pièces jointes

  • Nettoyer les plus petites chaines_PQ3.xlsx
    160.6 KB · Affichages: 10
Dernière édition:

carlos

XLDnaute Impliqué
Supporter XLD
Job75,Chris,
Wahou, je suis impressionné par la qualité et la rapidité de vos réponses.
@chris Je vais approfondir le PowerQuery.
@job75 Bonne idée de trier les lignes et d'utiliser le dictionnary.
Super et merci à tous les 2.
Je risque de vous solliciter à nouveau car pour arriver à ces 5588 lignes j'ai créé un code qui marche mais qui met 1 minute à tourner. Je vais créer un autre post.
Encore merci à tous les 2
 

job75

XLDnaute Barbatruc
Bonjour carlos, chris, mapomme, le forum,

Ah oui je n'avais pas vu le problème Courge et Courgette.

Il suffit d'ajouter des espaces pour encadrer, voyez ce fichier (2) :
VB:
Private Sub Worksheet_Activate()
Dim d As Object, tablo, i&, s, ub%, x$
Set d = CreateObject("Scripting.Dictionary")
d.CompareMode = vbTextCompare 'la casse est ignorée
tablo = Sheets("tout").UsedRange.Columns(1).Resize(, 2) 'matrice, plus rapide, au moins 2 éléments
ReDim resu(1 To Rows.Count, 1 To 1)
For i = 2 To UBound(tablo)
    x = tablo(i, 1)
    s = Split(x, "/")
    ub = UBound(s)
    If ub > 0 Then
        tri s, 0, ub 'classement alphabétique sur chaque ligne
        x = Join(s, " / ") 'encadrement par des espaces
    End If
    If x <> "" Then d(x) = ""
Next
'---restitution---
If FilterMode Then ShowAllData: DrawingObjects(1).Text = "Filtrer" 'si la feuille est filtrée
With [A2] '1ère cellule de restitution, à adapter
    If d.Count Then
        .Resize(d.Count) = Application.Transpose(d.keys) 'Transpose est limitée à 65536 lignes
        .Resize(d.Count).Sort .Cells, xlAscending, Header:=xlNo 'tri sur la 1ère colonne
        .Offset(, 1).Resize(d.Count) = "=COUNTIF(C[-1],SUBSTITUTE(RC[-1],""/"",""*""))"
    End If
    .Offset(d.Count).Resize(Rows.Count - d.Count - .Row + 1, 2).ClearContents 'RAZ en dessous
    .EntireColumn.AutoFit 'ajustement largeur
End With
Application.Goto [A1], True 'cadrage
End Sub
J'ai aussi ajouté un tri sur la 1ère colonne, c'est plus simple pour vérifier.

Bonne journée.
 

Pièces jointes

  • Nettoyer les plus petites chaines(2).xlsm
    106.4 KB · Affichages: 7

job75

XLDnaute Barbatruc
Bon le fichier (2) ne va pas, il y a encore 8 lignes de trop comme l'a dit chris.

Il faut mettre des espaces aussi sur les bords, fichier (3) :
VB:
Private Sub Worksheet_Activate()
Dim d As Object, tablo, i&, s, ub%, x$
Set d = CreateObject("Scripting.Dictionary")
d.CompareMode = vbTextCompare 'la casse est ignorée
tablo = Sheets("tout").UsedRange.Columns(1).Resize(, 2) 'matrice, plus rapide, au moins 2 éléments
ReDim resu(1 To Rows.Count, 1 To 1)
For i = 2 To UBound(tablo)
    x = tablo(i, 1)
    s = Split(x, "/")
    ub = UBound(s)
    If ub > 0 Then
        tri s, 0, ub 'classement alphabétique sur chaque ligne
        x = "/ " & Join(s, " / ") & " /" 'encadrement par des espaces
    End If
    If x <> "" Then d(x) = ""
Next
'---restitution---
Application.ScreenUpdating = False
If FilterMode Then ShowAllData: DrawingObjects(1).Text = "Filtrer" 'si la feuille est filtrée
With [A2] '1ère cellule de restitution, à adapter
    If d.Count Then
        With .Resize(d.Count)
            .Value = Application.Transpose(d.keys) 'Transpose est limitée à 65536 lignes
            .Offset(, 1) = "=COUNTIF(C[-1],SUBSTITUTE(RC[-1],""/"",""*""))"
            .Offset(, 1) = .Offset(, 1).Value 'supprime les formules
            .Replace " / ", "/", xlPart 'supprime les espaces au milieu
            .Replace "/ ", "" 'supprime l'espace et le slash à gauche
            .Replace " /", "" 'supprime l'espace et le slash à droite
            .Resize(, 2).Sort .Cells, xlAscending, Header:=xlNo 'tri sur la 1ère colonne
        End With
    End If
    .Offset(d.Count).Resize(Rows.Count - d.Count - .Row + 1, 2).ClearContents 'RAZ en dessous
    .EntireColumn.AutoFit 'ajustement largeur
End With
Application.Goto [A1], True 'cadrage
End Sub
Maintenant après filtrage il y a bien 188 lignes comme sur le fichier de chris.
 

Pièces jointes

  • Nettoyer les plus petites chaines(3).xlsm
    105.5 KB · Affichages: 7

Discussions similaires

Statistiques des forums

Discussions
311 733
Messages
2 082 019
Membres
101 872
dernier inscrit
Colin T