Discussion :

[49rio49]

bonjour,
j'ai refait mon classeur.Le but est de supprimer les doublons de la feuil2 qui ont été trouvé via la formule que Pierre m'a trouvé ( encore merci).
J'ai essayé avec une macro pour effacer les cellules avec fond jaune mais comme les cellules sont issues d'une MFC cela ne fonctionne pas.
Avec un tri par colonne c'est possible mais comme j'aurai pas mal de colonnes ça va devenir très vite fastidieux d’où ma recherche de solution.
merci.

[Pierre Fauconnier]

Je ne sais pas ce que tu as utilisé comme formule, car celle que j'ai donnée pour la MFC ne signale pas les doublons. Elle signale les valeurs de la feuille 2 qui sont aussi dans la feuille 1. Il n'y a pas de notions de doublons.

souhaites-tu supprimer de la feuille 2 les valeurs qui sont reprises dans la 1 (et qui sont donc allumées par la MFC formulée que je t'ai renseignée?

[49rio49]

oui Pierre c'est tout à fait ça.
en partant de ta formule les valeurs de la feuille1 se sont colorées en feuill2:normal.
maintenant il faut que je puisse supprimer ces valeurs qui sont (sur fond jaune) de la feuill2.

[Pierre Fauconnier]

La mfc et sa couleur ne peuvent pas servir au niveau vba (pas facilement, en tout cas). Ni Excel ni VBA ne sont équipés pour traiter cela correctement. Par contre, il est possible d'utiliser la fonction EQUIV (celle utilisée dans la MFC) au sein d'un code VBA. Dans la feuille 2, tu n'utilises pas de tableau (ça a la couleur du tableau, l'aspect du tableau, mais ce n'est pas un tableau). il faut donc déterminer la plage. Ici, pour l'exemple, j'ai codé A2:J68 en dur. L'idée est de partir de la dernière cellule de cette plage, de tester avec EQUIV en VBA (Application.Match) si la valeur de la cellule testée est en feuil1 (dans Colonne1 de Tableau1) et si oui (pas d'erreur dans le Match), on supprime. En prenant les cellules à rebours et en remontant, on ne doit dès lors pas gérer les sauts intempestifs de cellule lors de la suppression.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Sub DeleteCells()
  Dim f1 As Range
  Dim f2 As Range
  Dim i As Long
  Dim r
 
  Set f1 = Range("tableau1[colonne1]")
  Set f2 = Feuil2.Range("a2:j68")
  For i = f2.Count To 1 Step -1
    r = Application.Match(f2(i), f1, 0)
    If Not IsError(r) Then
      f2(i).Delete
    End If
  Next
End Sub

[49rio49]

bon..je viens de lancer la macro sur le fichier test4 et ça fonctionne bien, par contre sur un fichier beaucoup beaucoup plus remplis au bout de 7mn il travaille encore.
petite question sur la formule MFC que tu m'as donné, n'est il pas possible de supprimé la valeur trouvée sur la feuil2 plutôt que de la mettre sur fond jaune ? pour éviter de lancer une macro qui sera forcement plus long.

[49rio49]

bon excel tournez toujours j'ai finis par l’arrêter.
la macro a dû avoir une indigestion de donnée.
si tu as une autre piste

[Pierre Fauconnier]

Le problème, c'est la taille des données, dans ce cas, car la macro boucle sur toutes les cellules. Je ne sais pas ce que tu entends par rempli. Je crois comprendre que tu as placé la MFC pour t'appuyer sur la couleur pour supprimerldes données. Si c'est le cas, tu n'as plus besoin de la MFC et tu peux la supprimer. Ca fera du calcul en moins. Si tu dois la garder, tu peux aussi passer en calcul manuel en début de macro et le rétablir en fin de macro. Tu peux également geler l'affichage pour gagner encore du temps. Ce sont des techniques de base d'optimisation de procédures, surtout sur du code bouclant sur de grandes plages.

Avec beaucoup de données, je travaillerais en VBA avec un array (un tableau en VBA) dans lequel je pousserais les données de la plage de Feuille 2. Je parcours alors l'array et lorsqu'une correspondance est trouvée, je supprime de la plage la cellule correspondante à celle de l'array. La ligne t = f2.Value passe les valeurs de la plage dans un tableau à deux dimensions. Lorsque la valeur t(i,j) est trouvée dans Feuille1, il suffit de supprimer la cellule de Feuil2.range("a2:j68")(i,j). Ca devrait aller beaucoup plus vite. J'y ai ajouté le calcul manuel et le gel de l'écran.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
Sub DeleteCells()
  Dim f1 As Range
  Dim f2 As Range
  Dim t
  Dim i As Long, j As Long
  Dim r
  Dim CalculMode As XlCalculation
 
  On Error GoTo Catch
  CalculMode = Application.Calculation
  Application.ScreenUpdating = False
 
  Set f1 = Range("tableau1[colonne1]")
  Set f2 = Feuil2.Range("a2:j68")
  t = f2.Value
 
  For i = UBound(t) To 1 Step -1
    For j = UBound(t, 2) To 1 Step -1
      r = Application.Match(t(i, j), f1, 0)
      If Not IsError(r) Then
        f2(i, j).Delete
      End If
    Next
  Next
 
Catch:
  Application.Calculation = CalculMode
  Application.ScreenUpdating = True
End Sub

Cela a-t-il amélioré le temps d'exécution?

[49rio49]

Bonjour Pierre,
bon je ne suis pas convaincu,autant la mise en forme conditionnelle a été très rapide pour mettre sur fond jaune les doublons sur la feuille2 autant pour les supprimé (voir trier pour les mettre en tête ce colonne/ligne) devient long et fastidieux.
je ne sais plus trop sur quelle base partir (macro/mfc..)
le but final étant de repérer si les valeurs de la feuille1 sont aussi sur la feuille2 pour ensuite les supprimer de la feuille2.
quand je dis remplis cela veut dire que j'aurais beaucoup de valeur dans la feuille2.
je suis un peu dans une impasse...
avec une nouvelle MFC n'est il pas possible ces cellules sur fond jaunes?

[Alex020181]

Heu pas tout suivi.

Ta demande initiale était de mettre un fond jaune sur les valeurs communes entre Feuil1 et Feuil2.

On t'a donné 2 méthodes pour cela et Pierre t'en souffle une troisième dans sa dernière réponse.

Maintenant tu demandes à supprimer ces correspondances.

Pourquoi ne pas avoir dit tout de suite ce que tu veux réellement au final ?

Si tu veux les supprimer dis-nous d'abord, comme demandé précédement au moment de la suppression dans quel sens veux-tu décaler tes données restantes ou bien si tu veux uniquement effacer le contenu ded cellules contenant les valeurs communes.

[Pierre Fauconnier]

Heu pas tout suivi.

Ta demande initiale était de mettre un fond jaune sur les valeurs communes entre Feuil1 et Feuil2.[...]

C'est souvent le problème. Au lieu d'exposer la demande réelle (dans ce cas-ci, supprimer les valeurs d'une plage présentes dans une autre), il est demandé de résoudre "une partie" du problème tel qu'envisagé par le demandeur, sur une plage de données qui ne reflètent pas la réalité (nombre de lignes/Colonnes, complexité, ...)... Ca me rappelle cette discussion, dans laquelle on part de l'idée de retrouver le mot "pomme" pour s'apercevoir qu'en ait, il faut récupérer les cellules pour lesquelles une locution se trouve entre parenthèses sauf que c'est parfois entre la première, la deuxième ou la troisième et que la locution est tout sauf systématique... Bref, insoluble...

Dans le cas présent, je n'ai, en fait, pas compris le besoin initial, visiblement...

[Alex020181]

Oh si tu as compris. Et tu as même donné plusieurs solutions.
Mais on a tout les 2 compris la demande initiale qui était de colorer en jaune.

Aucun problème de compréhension.

Le problème est dans la demande.

S'il ou elle veut qu'on l'aide alors il faut faire une demande complète et claire.

[49rio49]

... et disons que sur la feuille1 il y aura 15000lignes maxi sur une colonne.

[Pierre Fauconnier]

Combien de lignes en feuille 1, combien de lignes et colonnes en feuille 2?

Il n'y a pas de miracles.Pour ce genre de traitement, la taille des tables impacte directement les performances

[Pierre Fauconnier]

Je pense avoir donné une solution optimale avec mon dernier code, au vu de la taille de tes donnés. On pourrait optimiser avec un double index-equiv en triant d'abord les données de la feuille 1. On gagnerait du temps de calcul. Peut-être que placer les données de feuille 1 dans un array et de boucher dessus permettrait de gagner un peu de temps. Mais vu le nombre de lignes et de colonnes en feuille 2 et le nombre de.lignes en feuil1, je doute qu'une solution de quelques secondes puisse être mise en place...

[Alex020181]

OK Régis je regarde ça avec plusieurs solutions et je vois.

[Pierre Fauconnier]

Apporté en mp?

[Alex020181]

Apporté en mp?

Non.

Il ne m'a rien envoyé en MP. Pourquoi ?

Je me base sur son dernier message pour faire plusieurs essais.

[Alex020181]

Voilà une solution qui EFFACE LES DONNÉES des cellules pour les valeurs communes.

20 minutes sur mon PC avec 200 000 lignes en Feuil1 et 60 colonnes de 200 000 lignes en Feuil2.

Tu as un bouton pour lancer le code sur Feuil2.

Je dois enlever une partie des données exemple pour répondre aux limites de taille des PJ. A toi de compléter ce fichier exemple en ajoutant des lignes en Feuil1 et Feuil2 si besoin et des lignes et colonnes en Feuil2 pour tester.

Si cela te va tu reprends le code (sans oublier de cocher la référence vue plus haut) et tu appliques à ton fichier réel.

[49rio49]

bon,je tiens d'abord à remercier toutes les personnes qui m'ont donné de précieux conseils (notamment Pierre et Alex pour leur patience et leur persévérance )
J'ai bien avancé sur mon futur classeur et je pense que maintenant je vais commencer à le mettre en place.
je vais mettre fin à la discutions (je clique où?)
grand grand merci à vous tous et longue vie à " www.developpez.net"