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?

[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

[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.

[Alex020181]

Si le but FINAL est de vider les cellules de Feuil2 contenant des données communes avec Feuil1 il faut le dire tout de suite.
Il suffit de modifier 1 seule ligne de code dans mon fichuer réponse du 05 août.

[Pierre Fauconnier]

Si le but FINAL est de vider les cellules de Feuil2 contenant des données communes avec Feuil1 il faut le dire tout de suite.
Il suffit de modifier 1 seule ligne de code dans mon fichuer réponse du 05 août.

De toute façon, si le but final est de supprimer (ou de "vider") les cellules de la feuille 2 dont la valeur est présente dans une liste, il faudra boucler sur les lignes et les colonnes de Feuil2 => temps d'exécution = temps d'exécution d'une boucle * nombre de boucles. Si 500000 lignes et 50 colonnes => 25000000 boucles VBA. Par belle ou par laide, on passera par ce genre de trucs, car je ne vois aucun moyen, dans le cas de figure tel que présenté, de réduire la charge et de travailler par lot.

La réduction du temps de traitement, sur des tableaux dont la taille nous est aujourd'hui inconnue et ce n'es pas faute de l'avoir demandée, ne pourra passer que par des optimisations périphériques (mode de calcul, gel du rafraichissement d'écran, ...) qui, sur Nombre lignes * nombre colonnes (boucles imbriquées obligent), ne feront gagner que peu de temps.

[Alex020181]

Tableaux en mémoire ou diictionnaire mais quoi qu'il en soit il y aura du temps de traitement.

Mais sans demande précise et énoncé clair on ne peut que supposer.

[Pierre Fauconnier]

Tableaux en mémoire[...]

C'est pourquoi que je suis étonné que la solution de passer par un array, gel d'affichage et gel du calcul n'apporte pas un résultat satisfaisant. Mais sans "sizing" des données (nombres des données en feuill1 et nombre de lignes/Colonnes en feuil2), nous ne pourrons que spéculer, comme souvent.

[49rio49]

bonjour,
je me suis mal fait comprendre sur le but final .
donc le but est de trouver les doublons que je peux avoir de la feuille1 à la feuille2 (oui je les considère comme des doublons entre les deux feuilles)
et ensuite de les supprimer de la feuille2
l'estimation de mon classeur sera de 200000lignes sur 60 colonnes (les lignes peuvent varier mais pas le nombre de colonne).
si je mets une valeur sur la feuille1 et qu'elle est aussi sur la feuille2 la valeur de la feuille2 devra être supprimée.

[49rio49]

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

[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.