Comparer deux plages excel rapidement [Résolu/Fermé]

Bonjour,

Merci pour cette intervention rapide.

Voilà procédons avec un exemple réaliste bien que mon besoin soit récurent sur d'autres recherches.

Une entreprise a 80000 contrats en colonne A. En colonne B, on a des contrats résiliés dont certains sont absents de la colonne A.

En colonne C, j'aimerai une fonction VB du style DIFFTAB(colonne A; colonne B; colonne C) - ou cela peut-être DIFFTAB(colonne A à C;colonne D à F) - qui donne en résultat toutes les valeurs du premier argument - ici colonne A absentes de la l'argument 2 - ici colonne B et place le résultat dans l'argument 3 - ici colonne C. On considère d'office que l'argument 1 est la plage à comparer, l'argument 2 la plage de recherche donc l'argument 3 ne comportera que des valeurs de l'argument 1 - plage à comparer - absentes de l'argument 2 -plage de recherche.

Dans notre exemple :

* le résultats de la colonne C comportera les contrats en vigueur.

Je pars sur une fonction car mes besoins sont divers. De plus, dans mon job on peut avoir des volumétries importantes.

Pour répondre à ta question "si la valeur en différence appartient à la plage A = "(A) ", je pense qu'il faut oublier car c'était une mauvaise idée. L'idée était de poser la fonction et de choisir dans le numéro de la valeur en différence. Exemple : supposons que nous trouvions 5 valeurs de la colonne A en différence avec la colonne B, en C1, je sollicitais première valeur en différence. En tirant la cellule C1 vers le bas, j'obtenais en C2 la deuxième valeur en différence ...etc. Mais oublions.

Je joins un fichier excel en colonne D, on trouve tous les contrats de la colonne A absents de la la colonne B. Dans mon exemple, je dois chercher les absents par EQUIV - très long pour 80000 contrats - puis faire un copier/coller texte puis classer la colonne D.

JE NE VOIS PAS DE QUOI CHARGER MON FICHIER EXCEL ????

J'espère avoir été plus clair.

Cordialement.

Bonjour,

Avant de chercher à accélérer il faut déjà faire les bons choix au départ.
Une fonction personnalisée sera toujours plus lente que les fonctions natives sur feuille, et qu'un sub qui traite tout d'un coup.
Quand tu as 80000 lignes c'est beaucoup ( beaucoup , beaucoup ) plus rapide de tout charger et de travailler en mémoire avec un sub.
Ton sub peut très bien être interactif et que tu lui désignes les colonnes à comparer, ainsi que où tu veux le résultat (même autre feuille éventuellement). Tu te rapproches de la souplesse d'une fonction, et tu as ton résultat en 2s...

Pour la restitution j'ai cru comprendre que tu veux la liste sans trous (pas alignée sur A), mais ce n'est pas dit explicitement.

ou cela peut-être DIFFTAB(colonne A à C;colonne D à F)
Et il faut A=D, B=E et C=F je suppose.
Et la restitution ? Uniquement A ou A, B et C ?

Précise aussi si la case (majuscules/minuscules) est toujours respectée entre A et B (ou A:C, D:F). C'est chronophage et si on peut se passer de cette conversion c'est toujours ça de pris.

Merci de déposer le fichier xls (réduit au nécessaire et anonymisé, avec les explications et éventuellement le résultat attendu) sur cjoint.com et coller ici le lien fourni.

eric

Bonsoir,

Je vais essayer avec un SUB paramétrable comme tu le suggères. Je peux appeler d'une cellule, un SUB comme une fonction ?

Convenons qu'à ta prochaine réponse à une ultime question, je marquerai la question comme résolu.

Question : avec un SUB, je peux afficher le contenu d'une cellule avec un ".select". Pourquoi cela ne marche-t-il pas avec FUNCTION ?

Merci pour la réponse précédente. Bonne soirée.

Je peux appeler d'une cellule, un SUB comme une fonction ?
Non.
Mais tu peux faire un raccourci clavier, ou bien lancer la macro sur un évènement comme activation de la feuille, ou clic sur un bouton, ou double-clic sur une cellule ou ...

Question : avec un SUB, je peux afficher le contenu d'une cellule avec un ".select". Pourquoi cela ne marche-t-il pas avec FUNCTION ?
Une fonction ne peut que retourner une valeur (ou une table de valeur si elle est matricielle), c'est tout.

eric

Regarde si ça t'intéresse : https://www.cjoint.com/?DDsrvwwwGdY
J'ai mis différentes options pour garder de la souplesse, comme tu disais que les besoins étaient divers.
L'option 'colorer' n'est pas encore opérationnelle, mais celle qui t'intéresse (extraire) l'est.
Teste sur tes 80000 lignes et dit ce qu'il en est.

eric

Bonsoir Eric,

Bravo ! cela marche nickel mais il y a un mais....

Utilisation : j'ouvre ton excel puis le mien. Je sollicite ta Macro depuis le mien.

D'abord, j'ai fait mon test avec mes 80000 contrats en colonne A et 15000 contrats résiliés en colonne B. J'ai fait sauté le contrôle de départ sur le même nombre de champs.

Il s'est planté à la ligne code "pl2 = Application.Transpose(result)". J'ai cru voir que la version de ton Excel fonctionne avec les 65536 lignes possibles par feuillet. J'ai diminué mes 80000 jusqu'à 65536. J'utilise Office 2010.

J'ai lancé la comparaison (EX 1 : extrait sur une autre feuille) Colonne A avec B et résultat en C) :

1 - choix absents ==> il a correctement calculer les différences des A absents de B dans la colonne C. J'ai contrôlé nickel. Il a mis 6 secondes .. parfait.

2- choix présents ==> il a correctement calculer les différences des A présents de B dans la colonne C. J'ai contrôlé nickel. Il a mis 6 secondes .. parfait (j'ai du complètement du réinitialiser la démarche).

J'ai voulu tenté sur 69800 en les recopiant. Surprise cela a marché aussi.

Problèmes rencontrés :

a) à partir d'un certain nombre de lignes (?), on bugge sur la ligne code "pl2 = Application.Transpose(result)",

b) si on fait une comparaison absents par exemple, plusieurs fois à suivre, cela marche. Si on décide de changer un choix dans la fenêtre, le calcul se perd - rien n'est plus possible et il faut scratcher le EXCEL.EXE. On dirait un problème de réinitialisation.

Je n'ai pas testé les autres choix (EX2 et EX3).

Je reste impressionner par la rapidité de ta solution qui pour moi me convient. Dans mon métier, on manipule de gros volumes. Une compagnie d'assurance va avoir un contrat d'assurance-vie ouvert en 1921 par exemple. Légalement, tant que les ayants-droit ne se sont pas manifestés, il est conservé. Ce sont les fameux sommes dormantes.

Je dois dire que ma sollicitation tient du confort car j'aime bien Excel. Mais quand il s'agit de gros volumes, il ne peut pas lutter contre ACCESS qui malheureusement est limité aussi à 1 Giga 8. Après base réseau.

Je te fais ce retour mais je comprendrai que tu estimes en avoir assez fait. Si toutefois, mes retours te conduisent à débugger, je serai ravi. Tout dépend pour ton goût pour les challenges.

Dores et déjà, tous mes remerciements.

JOYEUSES PAQUES.

J'ai fait sauté le contrôle de départ sur le même nombre de champs.
Un petit bug, remplacer >1 par >=1

a) à partir d'un certain nombre de lignes (?), on bugge sur la ligne code "pl2 = Application.Transpose(result)"
J'ai l'impression que c'est une limite d'excel qui est dépassée.
Il faut que je réfléchisse un peu pour l'outrepasser sans trop de perte de temps.

b) si on fait une comparaison absents par exemple, plusieurs fois à suivre, cela marche. Si on décide de changer un choix dans la fenêtre, le calcul se perd - rien n'est plus possible et il faut scratcher le EXCEL.EXE. On dirait un problème de réinitialisation.
J'ai pu reproduire.
Et ça, ça me gène plus car la macro n'est même pas lancée, difficile de déboguer qcq chose là...
Pour l'instant à part fermer excel avant de changer présent/absent je n'ai pas grand chose à te proposer.
Je vais d'abord m'attacher aux 65000 lignes, plus urgent je pense

eric

suite...

- limite 65000 lignes corrigée, à tester
- ajout option 'Corriger les dates JMA'. A décocher si aucune date JMA dans le champ d'extraction, divise environ par 2 le temps de traitement.
https://www.cjoint.com/c/DDtmFCM25XR

eric

Bonsoir Eric,


Cela fonctionne. Cela prend 28 secondes pour les 80000 lignes.

Excuses-moi mais quand dois-je décocher les dates JMA précisément. je n'ai pas bien compris "si aucune date JMA dans le champ d'extraction".

IL reste un ultime souci mais je crois avoir la solution.

Lorsque je fais une comparaison sur les "Absents" puis sur les "Présents" => la fenêtre se bloque mais contrairement à la première fois, cela ne fait rien - plante pas mais si je ferme la fenêtre alors cela plante.

L'astuce : je fais une comparaison des "Absents" puis je réinitialise les zones de la fenêtre et je fais le choix "Présents" + je choisis les zones "de" - "dans" - "extraire...". Cela marche alors.
En fait, il faudrait réinitialiser les zones "de", "dans", "extraire..." à l'affichage de la plage résultat.

Une fois cet élément vu, je considérai mon problème résolut.

Avec toute ma gratitude.

Cordialement.

28s c'est encore beaucoup je trouve.
Quel processeur et combien de ram ?

quand dois-je décocher les dates JMA précisément
Si, dans l'extraction, il n'y a pas de dates au format français jour/mois/année tu peux décocher.
Sinon certaines se retrouvent au format anglais avec jour et mois inversés et nécessitent un traitement supplémentaire.

J'avais détecté que c'était le contrôle RefEdit qui est bogué.
J'essaie de faire autrement pour m'en passer mais il va me falloir du temps disponible pour le faire.
En attendant voici une version avec raz des refEdit, mis également sur sélection présent/absent.
+ surlignage opérationnel : https://www.cjoint.com/?DDtxNOiLzvP
eric

Suite...
Nouvelle version sans les refedit, et donc un fonctionnement plus correct : https://www.cjoint.com/c/DDunlyvr5m4
Plus traitement modifié pour un gain de temps de 30% environ.
Modif assez profondes, donc à tester en long, en large et en travers.

eric

PS: à-priori les dates réagissent mieux, je laisse quand même l'option 'Corriger les dates JMA' au cas ou, mais désactivée par défaut.

Bonsoir Eric,

Excellent travail. Tu tiens LA SOLUTION : nickel.

J'ai testé en long et large la nouvelle version.

Comparaison "Absents" ou "Présents" = 7,8 à 8,3 secondes = excellent.
Comparaison "Absents" avec couleur = 7,4 secondes = excellent.
Comparaison "Présents" avec couleur = 2,5 à 2,6 secondes = excellent.

J'ai fait plusieurs tests avec permutation de choix.

Retours tests :

1 - à la première utilisation avec couleur, la couleur est en fond noir,
2 - lorsque l'on procède à la comparaison avec couleur, à la comparaison suivante avec couleur + permutation - passer de "Absents" à "Présents" (ou inverse), les cellules non sélectionnées prennent la couleur de la comparaison précédente avec couleur,
3 - les comparaisons de "Absents" ou "Présents", en cochant "corriger les dates JMA", le temps prend entre 25 et 26 secondes,
4 - le test avec "Supprimer" est très long (j'ai scratché).

Configuration :

Performances Windows 4,6
Processeur : AMD Athlon (tm) 64 X2 Dual Core Processor 3600+ 1,99 GHz
RAM : 2 Go
Type de système : 32 bits

Me concernant, la solution est très bonne. Le choix avec "supprimer" est pour moi moins important.

Espérant avoir été un bon testeur,

Cordialement.

Bon, on arrive à des temps raisonnables :-)

Retours tests :
1) ok, je crois voir l'oubli.
2) effectivement j'ai fait le choix de ne pas modifier l'environnement des cellules non sélectionnées. J'ajouterai éventuellement le choix à faire par l'utilisateur.
3) là on aura du mal à faire mieux. Obligé de ré-écrire les données une par une en cellule et c'est très long. Mais je ne suis pas sûr que cette option soit encore utile (?)
C'est un peu pareil pour la couleur présents/absents : ça dépend fortement du nombre de cellules à traiter.
4) ouais. J'ai mis l'option vite fait qu'elle soit présente dès le départ. Vue la technique utilisée ça ne m'étonne pas. Ca sera nettement mieux à la prochaine version.

Prochaine version qu'on pourra appeler Deluxe si j'arrive au bout d'une nouvelle idée ;-)
Lundi ou mardi, je travaille par petits bouts.

eric

Bonjour Eric,

Je te laisse juge des améliorations à apporter - sans que tu es aucune obligation.

Pour moi, dans l'état, le but est d'ores et déjà atteint. Bravo pour les résultats obtenus.

Dès la semaine prochaine, je compte en faire usage dans un contexte professionnel.

Re-mille mercis.

Cordialement.

Bonsoir Eric,

Tout d'abord mille mercis pour cette nouvelle mouture. Par rapport à ma demande initiale, la réponse va bien au-delà de mes attentes. Convenons en effet que nous sommes arrivés au but.

Peu familier de ce forum, dois-je suite à cette réponse, cliquer sur "Marque comme résolu" ?

Bravo encore pour ce bon travail.

Cordialement.

Bonjour,

Oui, tu peux mettre en résolu, en haut vers ton titre.
Il reste des passages qui font un peu bricolage suite aux modif pour dysfonctionnements sur 2010 que je dois reprendre car ils ne me plaisent pas, ça sera un peu plus tard.
Et il reste sans doute des bugs...

eric

Bonsoir Eric,

Je vais mentionner la question comme résolue.

Je te remercie pour toutes démarches.

A titre purement informel, deux cas de figures (j'ai du occulter quelques tests)
- si l'on choisit deux colonnes dans "de" - A:B par exemple et deux colonnes dans "dans" - C:D par exemple en résultat colonne E, quand B est en différence dans C:D, il donne la référence de A systématiquement,
- autre cas : si nous avons deux colonnes - A:B du Feuil1 par exemple = A 80000 lignes B=15000 lignes et deux colonnes - C:D Feuil2 par exemple = C 15000 lignes D=80000 lignes soit l'inverse du Feuil1, quand nous comparons "de" Feuil2!B:B, "dans" Feuil1!A:A avec en résultat colonne Feuil2!D:D, - (nous comparons donc la même chose, on s'attend à un résultat vide) - il trouve en différence les valeurs portant égales à partir de la 150001 lignes sur les 80000 lignes.

Je te souhaite bonne continuation.

Cordialement.

Bonsoir,

1) quand B est en différence dans C:D, il donne la référence de A systématiquement,
C'est normal, c'est ce que j'ai prévu.
On compare 2 champs, mais on n'est pas obligé d'avoir les 2 champs en résultat.
Si on en veut qu'un seul c'est le 1er qui est retourné.
Si c'est le 2nd qui t'intéresse le plus simple c'est de mettre 2 colonnes et de supprimer celle qui ne t'intéresse pas. Le demander à chaque fois deviendrait lourd.

2) ça doit être un bug, il va falloir que je me mette en situation pour constater.
J'essaie de voir ça demain, mais j'aurais d'autres choses avant.

eric

Bonjour,

J'ai un peu de mal à dégager suffisamment de temps mais je ne t'oublie pas.
eric

Bonjour,

Ca y est, j'ai pu reprendre la partie qui ne me plaisait qu'à moitié.
Je n'ai pas pu reproduire ton 2nd pb signalé, On est bien d'accord que toutes les plages sont dans le même classeur ?
Car si tu repars d'un profil, je n'y stocke pas le nom du classeur, juste les références des plages.
Regarde si tout est ok pour toi.
https://www.cjoint.com/c/DDDlscdw1b2

eric

PS: j'ai ajouté la possibilité d'insérer une colonne résultat pour pouvoir filtrer dessus.