XL 2010 Supprimer les lignes en doubles (multi critères)

[Tony.R]

Bonjour,

Je suis en train de créer un fichier automatisé de A à Z, je débute mais je ne m'en sort pas trop mal mais il y a un endroit où je bloque. J'ai fichier après traitement de la macro sauf que dans ce fichier il y a des lignes qui ne m'intéressent pas. C'est pourquoi je souhaite avoir une macro qui va permettre de faire une double recherche pour supprimer les lignes inutiles. Pour expliquer en détail pour un montant dans les colonnes intitulées D ou C il y a environ 50% de la TVA qui vient s'ajouter sauf que je n'en ai pas besoin. exemple :

Je parlais de double critère car en effet il faut un double doublon pour effectuer cette tâche de suppression : sur la colonne N et P de ce fait s'il y a un doublon sur la colonne N et P alors il faut supprimer la ligne la plus petite (qui équivaut à 50% de la TVA environ) donc dans cette exemple la ligne qui doit être supprimer est la ligne à 116,62€.

Normalement un double doublons doit avoir 2 lignes une ligne avec un gros montant en D ou C2 et un petit montant en D ou C2 mais il arrive qu'il y ai plusieurs ligne par exemple 4 grosses lignes (du même montant en général) et 4 petits montant , il faut garder que les gros (du coup les 4 gros montants). Que les montants soient en D ou C2 cela n'a pas d'importance la gymnastique est la même.

Je sais que cela peut être compliqué à comprendre mais n'hésitez pas à me poser le maximum de question pour la bonne compréhension. Le faire manuellement est très long donc si une macro peut le faire rapidement je ne dirais pas non ...

D'ailleurs toutes les colonnes doivent être présente.

Merci par avance.

Cordialement.
Tony

 

[sylvanu]

Bonjour Tony,
Une tentative en PJ.
Cependant je ne fais que 98.89% du travail, je n'ai pas traité les "quadriblons" et "sextyblons"
Il reste 24 lignes à analyser sur 2164. Mais au moins ça avance.
Par contre à bien vérifier car le fichier est gros et je n'ai pas tout vérifié.

 

[vgendron]

Bonjour

un double doublon..???
un doublon suffit non??
par définition, un doublon, c'est l'existance de deux lignes identiques..

si je regarde tes données, elles sont déjà triées
lorsqu'il y a deux lignes identiques, c'est la première qu'il faut supprimer
si il y en a 6, il faut supprimer la 1, 3 et la 5..
donc il suffit de supprimer une ligne sur deux sans meme chercher à savoir si les colonnes N et P sont identiques
(à supposer qu'il y a TOUJOURS un doublon pour chaque "code")

cette macro ferait l'affaire

Sub Supp1sur2()
Application.ScreenUpdating = False

Dim TabData() As Variant

With Sheets("Feuil1").ListObjects(1) 'avec la table
    TabData = .DataBodyRange.Value 'on met les données (hors entete) dans le tablo vba
    For i = LBound(TabData, 1) To UBound(TabData, 1) Step 2 'pour chaque ligne /2
        For j = LBound(TabData, 2) To UBound(TabData, 2) 'pour chaque colonne
            TabData(i, j) = "" 'on vide la ligne entière
        Next j
    Next i
    .DataBodyRange = TabData 'on recolle le résutat dans la table
   
    .Range.AutoFilter Field:=1, Criteria1:="=" 'on filtre sur la colonne 1, cellules vides
    Application.DisplayAlerts = False 'désactive message alerte
    .DataBodyRange.Delete 'on supprime les lignes filtrées (vides)
    ActiveSheet.ListObjects("Tableau1").Range.AutoFilter Field:=1 'on supprime le filtre pour réafficher les données restantes
    Application.DisplayAlerts = True 'on reactive les messages d'alerte
End With

Application.ScreenUpdating = True

End Sub

edit: hello @sylvanu

 

[sylvanu]

Re, Bonjour Vgendron,

si je regarde tes données, elles sont déjà triées

Il y a des doublons qui ne se suivent pas, par ex quelques uns :

Ce qui complique la solution.

 

[sylvanu]

Re,
Bug trouvés :
1- Ne tenait pas compte de C2 mais uniquement de D. Corrigé.
2- Pour les "quadrillons" et "sextyllons", les traitait accidentiellement en partie, donc résultat faux.
En PJ une V2 corrigée.
Résultat, plus que 16 lignes à corriger à la main ( soit 1.6% )

 

[Tony.R]

Merci à vous

Le problème c'est que les lignes ne se suivent pas forcement et il y a pas forcement de doublon tout dépend du taux de récupération de la TVA (petit montant) si le taux est de 50% alors oui il y aura un petit montant mais si sur une année il est à 100% alors il y aura pas de petite ligne. c'est pour cela que la recherche de doublon sur 2 critères est important. et les critères sont N et P si N est en doublon mais pas P alors il s'agit de 2 produit différents par contre si P et N sont en doublon au même moment alors cela signifie qu'il y a un petit montant.

Après je suis d'accord de se focalisé que sur D et de laisser C2 faire à la main car le nombre est non significatif par rapport à D.

Je ne sais pas si mes explications vont faire en sorte que la macro n'est pas adapté. Par contre pouvez-vous s'il vous plait me donner une explication de la macro pour savoir se qu'elle fait réellement ?

Merci par avance

 

[sylvanu]

Dites au moins si la V2 répond à vos attentes, car commenter davantage un code qui ne marche pas ....
( le code est déjà commenté )

 

[vgendron]

La question que je me pose; quelle colonne sert pour detecter les doublons?==> La colonne intitulée "N" ??
et si on trouve plusieurs fois le meme N à des dates comptables (colonne A) différentes..??

supprimer la ligne du plus petit montant en considérant (sans aucune vérif) qu'il s'agit d'une TVA à 50% (50% de quoi?) ca me semble un peu hasardeux..

pourquoi ne pas faire simplement un tri sur les colonnes "N" et "D" et donc.. supprimer une ligne sur deux..?

j'ai le sentiment qu'on ne nous dit pas tout..
un identifiant (colonne N) peut-il être unique? (une seule ligne = pas de doublon = pas de ligne à supprimer)
un identifiant (colonne N) peut il apparaitre un nombre impair de fois (= comment choisi-t-on la ou les lignes à supprimer = c'est quoi un "petit montant"....

 

[vgendron]

en ajoutant le tri en début de macro ca donne ca

après.. si ca ne donne pas le résultat attendu, il va falloir expliquer pourquoi..

Sub Supp1sur2()
Application.ScreenUpdating = False

Dim TabData() As Variant

With Sheets("Feuil1").ListObjects(1) 'avec la table

    .Sort.SortFields.Clear
    .Sort.SortFields.Add2 Key:=Range("Tableau1[N]"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    .Sort.SortFields.Add2 Key:=Range("Tableau1[C2]"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With .Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    
    TabData = .DataBodyRange.Value 'on met les données (hors entete) dans le tablo vba
    For i = LBound(TabData, 1) To UBound(TabData, 1) Step 2 'pour chaque ligne /2
        For j = LBound(TabData, 2) To UBound(TabData, 2) 'pour chaque colonne
            TabData(i, j) = "" 'on vide la ligne entière
        Next j
    Next i
    .DataBodyRange = TabData 'on recolle le résutat dans la table
  
    .Range.AutoFilter Field:=1, Criteria1:="=" 'on filtre sur la colonne 1, cellules vides
    Application.DisplayAlerts = False 'désactive message alerte
    .DataBodyRange.Delete 'on supprime les lignes filtrées (vides)
    ActiveSheet.ListObjects("Tableau1").Range.AutoFilter Field:=1 'on supprime le filtre pour réafficher les données restantes
    Application.DisplayAlerts = True 'on reactive les messages d'alerte
End With

Application.ScreenUpdating = True

End Sub

 

[laurent950]

Bonsoir,

Option Explicit
Sub testsurColonneD()
' macro qui va permettre de faire une double recherche pour supprimer les lignes inutiles
' Pour expliquer en détail pour un montant dans les colonnes intitulées D (Colonne C2 non prise en compte) il y a environ 50%
' de la TVA qui vient s'ajouter sauf que je n'en ai pas besoin
' Je parlais de double critère car en effet il faut un double doublon pour effectuer cette tâche de
' suppression : sur la colonne N et P de ce fait s'il y a un doublon sur la colonne N et P alors il
' faut supprimer la ligne la plus petite (qui équivaut à 50% de la TVA environ)
' Soit :
' A ) Identification de la ligne avec couleur jaune
' B ) Puis suppression de cette ligne
'
Application.ScreenUpdating = False
    Dim t(0 To 1) As Variant
    Dim a() As Variant
    Dim i, j As Long
        t(0) = Range(Cells(1, 1), Cells(Cells(65536, 1).End(xlUp).Row, Cells(1, Cells.Columns.Count).End(xlToLeft).Column))
    Set t(1) = Range(Cells(1, 1), Cells(Cells(65536, 1).End(xlUp).Row, Cells(1, Cells.Columns.Count).End(xlToLeft).Column))
    'ReDim preservet(LBound(t(0), 1) To UBound(t(0), 1), LBound(t(0), 2) To UBound(t(0), 2) + 1)
    '  A) Identification de la ligne avec couleur jaune
    For i = LBound(t(0), 1) + 1 To UBound(t(0), 1)
        For j = i + 1 To UBound(t(0), 1)
            If t(0)(i, 7) & t(0)(i, 10) = t(0)(j, 7) & t(0)(j, 10) Then
                a = Array(t(0)(i, 8), t(0)(j, 8))
                    If Application.Min(a) = t(0)(i, 8) Then
                        'MsgBox t(0)(i, 8)
                        't(0)(i, UBound(t(0), 2)) = "x"
                        Range(t(1).Cells(i, 1), t(1).Cells(i, UBound(t(0), 2))).Interior.Color = 65535
                    ElseIf Application.Min(a) = t(0)(j, 8) Then
                        'MsgBox t(0)(j, 8)
                        't(0)(j, UBound(t(0), 2)) = "x"
                        Range(t(1).Cells(j, 1), t(1).Cells(j, UBound(t(0), 2))).Interior.Color = 65535
                    End If
                    Erase a
            End If
        Next j
    Next i
        i = Empty: j = Empty
' B ) Puis suppression de cette ligne
        For i = t(1).Rows.Count To 1 Step -1
            If Range(t(1).Cells(i, 1), t(1).Cells(i, UBound(t(0), 2))).Interior.Color = 65535 Then
                Range(t(1).Cells(i, 1), t(1).Cells(i, UBound(t(0), 2))).Delete
            End If
        Next i
        Erase t
        i = Empty: j = Empty
Application.ScreenUpdating = True
End Sub

 

[Tony.R]

Je n'ai pas encore essayé vos macros mais pour donner une explication approfondie. Chaque année il y a un recalcul de la récupération de la TVA. C'est à dire qu'une partie de la TVA va en dépense. Sauf qu'il s'agit d'une donné financière. Les métiers n'ont pas cette information et donc les montants qu'ils tiennent en compte sont les gros montants. Au début je parlais de double doublons car il faut 2 critères pour pouvoir se dire qu'il faut supprimer le petit montant. Il peut y avoir plusieurs N identique mais un seul P est associé à un N.

Donc si lors d'une année nous avons conclue qu'il y aura pas de récupération alors oui il y aura qu'une ligne et donc pas de suppression à faire voila pourquoi je ne peux pas faire du hasard en disant je supprimer une ligne sur 2.

 

[Tony.R]

Bonsoir @laurent950 je viens de tester ta macro elle est oufisime par contre je viens de contrôler de mon côté aussi et il y a une légère différence entre ta macro et ce que j'ai fait.

En effet ta macro à également supprimer les gros montants lorsque ces derniers ce trouvais dans la colonne D et C2.

Pour les numéros de facture qu'elle à également supprimé :
- F2023-03-1459 pour le C2 10 800
- F2023-03-1459 pour le D 10 800
- 7380013284 pour C2 5 814,95
- 7380013284 pour D 5 814,95
- F2023-03-1459 pour C2 3 000
- F2023-03-1459 pour C2 3 000

Elle à bien supprimée les petits montants mais également les gros surement parce qu'il y avait un montant en D et en C2 mais en réalité ces montants doivent rester dans le tableau.

Est-ce que ton tableau prend en compte un nombre illimité de ligne ? car d'un mois sur l'autre il y a plus ou moins des valeurs.
De plus pour des raisons de sécurité j'ai modifié les entêtes des colonnes, est ce que cela pose problème par rapport à ta macro ?

Penses-tu pouvoir faire quelque chose ?

Je te remercie par avance.

Cordialement.

 

[laurent950]

Bonsoir,

Je vais regarder
- F2023-03-1459 pour le C2 10 800
Il y a bien la ligne avec 10 800 avec ma macro qui n est pas effacer ?
Car j ai testé que sur la colonne D
Mais
Pas la colonne C

Est-ce que ton tableau prend en compte un nombre illimité de ligne ? Oui

De plus pour des raisons de sécurité j'ai modifié les entêtes des colonnes, est ce que cela pose problème par rapport à ta macro ? = Non

Penses-tu pouvoir faire quelque chose ? = oui

 

[Tony.R]

Oui tout à fait mais j'en ai 2 en colonne D et 1 en colonne C et avec ta macro il en reste plus qu'une dans la D

 

[laurent950]

Bonjour,

J'ai bien sûr compris, la macro ci-dessus au poste #10 est correcte mais pour une seule colonne soit celle avec l'en-tête D

il faut la conserver

Pour le traitement avec la colonne en-tête D et C2 il n'y a rien de compliqué. Il faut créer la macro qui va faire le même travail que celle en Poste #10 mais pour deux colonnes, il faut dont se servir du tableau de base et y ajouter 2 colonnes en mémoire en créant une clé primaire avec la colonne N et P ainsi qu'un complément de codification associé au colonne (D et C2) et le tour est joué.

Une question ?
Exemple pour principe ci-dessous.
Juste sur une ligne pour colonne D et C2

1) cas 1
Si dans une cellule D le montant est 0 alors il y a forcément un Montant différent de 0 dans la colonne C2
Exemple :
Ligne 28 / colonne D = 200 / Colonne C2 = 0
= oui ou non ? Reponse
Et
Vis versa
Exemple :
Ligne 28 / colonne D = 0 / Colonne C2 = 200
= oui ou non ? Reponse

2 ) cas 2
C'est possible qu'il y est un montant supérieur 0 en D et C2
Exemple :
Ligne 28 / colonne D = 200 / Colonne C2 = 200
= oui ou non ? Réponse

en fonction des réponses je finalise le code mais se soir bonne journée.