Accélération d'un Algo pour recherche d'occurrences de sommes entre 2 tableaux

[@+Thierry]

Bonsoir, ou plutôt Bonjour mes Amis et Amies, les Forumeurs et Forumeuses !

Une fois n'est pas coutume, mais je vais demander votre assistance car je pense que je peux trouver ici, si ce n'est une solution, en tout cas un autre regard sur la façon de rédiger un Algo, dont le but est très simple :

Réconciliation automatique entre deux états constituéS de seulement quelques informations en clefs d'enregistrements, mais hélas pas suffisamment et bien sûr aucune clef unique par enregistrement à "matcher". Et le pire reste à venir !!! lol

En effet, le Statement "DataC" contient moins d'enregistrements que le Statement "DataW". En fait cet état "DataW" contient le détails de transactions qui seront groupées sur l'état "DataC", exemple :

Sur DataW : en date d'entrée du 1er Mars on a acheté :
CHF 1,000.00 Valeur 2/03 qui vont coûter USD 809.45
EUR 1,500.00 Valeur 2/03 qui vont coûter USD 1,915.35
GBP 1,000.00 Valeur 2/03 qui vont coûter USD 1,907.40

Sur DataC : : en date d'entrée du 4 Mars on verra une seule ligne :
USD 4,632.20 débit Valeur 4/03

J'ai bien sûr différents comptes et aussi différentes currencies sur les deux Etats (là n'est pas la difficulté). Je gère aussi les plus/moins deux jours...

Mon problème réside dans la structure de mon algo. Pour les 3 occurences de l'exemple ci-dessus çà va rapidement.... pour 4 çà va encore sur un petit tableau...

Mais plus... alors là, je crains de monopoliser un PC toute une semaine !!! lol

Dans l'exemple démo ci-joint réduit au plus petit possible à 50 ko, je fais écrire des commentaires par VBA qui identifient les écritures qui "matchent" avec le détail nécessaire en commentaire...

Même si vous n'avez pas d'idée, la pièce ci-jointe est déjà un outil pratique pour faire des réconcilitations de un à quatres montants en calculant toutes les combinaison possible...

Mais si vous avez une idée....... ce Fil est à vous !!!

Merci d'avance et bonne nuit
@+Thierry

 

[@+Thierry]

Evidemment si c'est moi qui post un fichier ile ne passe pas !! lol

Seconde tentative !!!

Sorry
@+Thierry

 

[@+Thierry]

bon 1 ko !! arf dans le temps il y avait un message quand le fichier passait pas ...

bon ce n'est pas grave je le mets chez moi !

==> Ce lien n'existe plus (50 k) |i]et des bananes lol|/i]

Bonne Nuit
@+Thierry

 

[Pascal76]

Salut Thierry

Je me permets de soumettre une idée (Monaco étant qualifié je suis sur que tu seras encore plus magnanime à mon égard si je dis une ânerie)

Si les 2 tableaux sont triés avant par la colonne A est-ce qu'on ne gagne pas du temps dans les boucles car possibilité de sortir de la 2ème boucle aussitôt que les codes sont différents ??

J'avoue que je n'ais pas testé car je suis au boulot et le temps de relire tout ton code et de l'appliquer avec cette idée bah il faut que je bosse un peu quand même.

Bon ça me parait peut-être un peu simple mais .........

Bon courage et encore merci pour toutes tes démos et réponses

Pascal

 

[@+Thierry]

Bonjour Pascal, le Forum

Il semble qu'il n'y ait pas que moi qui ne soit pas arrivé à uploader mon fichier... (Voir fil : Simplifer UserForm)

Merci pour l'idée, mais évidemment, même si çà fera gagner un peu de temps, je ne ne pense pas que çà permette de faire une 5em occurrence (voire plus)...

Mais c'est sympa de te lancer !!! Et vive Monaco !!

Bonne Journée
@+Thierry

 

[@+Thierry]

Re Bonjour Pascal, le Forum

Je viens d'appliquer ta suggestion, à savoir faire un tri préliminiare sur les Colonnes Account des bases, avec un tri secondaire sur les Input Dates, non, "y a pas bon" lol voici les temps sur un P-IV :

Tri tel qu'il est dans la démo :
3 Occurrences 0.78
4 Occurrences 17.36

Une Fois le tri effectué :
3 Occurrences 0.88
4 Occurrences 22.61

Mais merci
@+Thierry

 

[Pascal76]

Re Thierry

Bon bah tu vois mauvaise idée

Pourtant je me disais qu'en sortant de la boucle et en revenant qu'à la ligne que l'on venait de quitter cela evitait de reboucler à chaque fois sur toutes les lignes et de reprendre ligne 2.

M'enfin

Bon courage

Pascal

 

[Zon]

Salut,

Excel est lent dés qu'on écrit dans des cellules surtout avec de la mise en forme, donc il faut essayer d'y écrire qu'une fois pour avoir un temps d'éxécution optimum en passant par des tableaux VBA...
@Thierry ma question est la suivante: Es tu obligé de garder cette structure avec commentaires crées et couleurs rajoutées ?=> la mise en forme est trés longue à l'éxécution. Si non en construisant un tableau VBA où en ligne on met le N° de ligne et en colonne les réferences trouvées., avec un resize on écrira que 2 fois dans tes 2 feuilles.(cela reste à tester)


A+++

Ps: Bravo Monaco mais Allez Newcastle.

 

[@+Thierry]

Hello Zon, re Pascal, le Forum

M'enfin, Pascal !! mais merci pour tout !

Merci beaucoup Zon, non l'écriture sur Excel pourrait n'intervenir qu'en fin de traitement, les commentaires et les couleurs ce n'est que pour le besoin visuel de la démo mise en ligne. (toutefois, j'ai, peut-être à tord, l'impression que celà ne gène pas vraiment la vitesse de traitement puisque la macro "ComBuild" ne peut s'exécuter qu'une fois que le Select Case "Match" entre C et un des WSXX en cours des enchainement de boucle For Each X, for Each XX, For Each XXX... Etant entendu qu'une fois ce Match et ComBuid exécuté, on sort et repart au next i...

Maintenant ton idée me semble très interressante, je vais y travailler mais par contre va falloir que je me documente sur le "Resize" car je n'ai jamais utilisé dans un Tableau VBA autre chose qu'un simple "Redim". Si jamais tu as un exemple d'utilisation sur tableau en 2 dimensions je suis preneur.

Merci pour le temps consacré.

Bon Après Midi
@+Thierry


PS ...

 

[@+Thierry]

Re Zon, Pascal, Le Forum

J'ai juste essayé de mettre en pratique la possibilité de passer sur une variable en Tableau Séquentiel en deux dimensions pour récupérer la Ligne de "DataC" qui match avec un des "WX00" ainsi que la String "WLC" en seconde colonne, ce qui me permet de mettre les commentaires uniquement en fin de procédure (au lieu d'écrire les commentaires pendant la boucle) le code ci-joint en module.Bas zippé.

Hélas, c'est bien ce que je craignais, je ne vois vraiment pas une amélioration significative par rapport à ma précédente méthode :

4 Occurrences = 17.61 (avec écriture comment et coloriage des cellules en cours de boucle)

4 Occurrences = 16.03 (Avant toute écriture sur cellule, qui ne se fait qu'ensuite)

Mais j'ai quand même tenté sur 5 Occurrences toujours en report sur Tableau VBA, et toujours un P-IV et çà nous fait 1520 !! soit pas loin de 26 minutes !!! (Avec l'écriture de comment pendant la boucle, il faut compter 1 à 2 minutes de plus)

Donc imaginons la même boucle sur 6 ou 7 occurences... Je me demande si ce que je veux faire n'est pas un peu trop poussé pour Excel...

Enfin j'attends si un XLDéen , ou XLDéenne n'aurait pas une idée géniale...

Bon Aprèm
@+Thierry

 

[Bernard]

Bonjour @+Thierry et le forum

Je découvre ton problème et je voudrais pouvoir t'aider pour toute l'aide que tu nous as apportée si généreusement à tous.

Si j'ai bien compris, tu cherches à repérer dans un état (DataW) des montants que l'on retrouve additionnés dans un deuxième état (DataC).

Cette addition de montants pouvant regrouper de 1 à n montants du premier état.

Si j'ai bien situé le problème, une solution pour aller plus vite serait de retirer, avant toute nouvelle recherche, les lignes déjà localisées dans DataW. Plus le nombre de données à traiter est petit, plus le traitement sera rapide.

J'espère avoir pu te donner un embryon d'idée qui t’aidera ?

Très cordialement

Bernard

 

[Mytå]

Bonjour le Forum

Un idée peut-être sera de travailler sur la DataW en pré-filtre

.AutoFilter Field:=1, Criteria1:=TabC(i, 1)
.AutoFilter Field:=6, Criteria1:=TabC(i, 4)

la plage de recherche serait ensuite moins longue.

Mytå

 

[Ti]

alors j'ai un peu amélioré ton fichier. Il recherche toutes les occurences qu'il peut trouver directement. Sur ton fichier il fait ça entre 0.36 et 1 seconde (il va jusqu'à 6 occurences sur ce tableau), mais je n'ai pas trop fignolé, on pourrait peut-être encore arranger ça. Enfin tel quel, ça semble déjà bien aller, sauf si tu constates des erreurs (ce qui est possible vu la rapidité des tests !)

 

[Ti]

j'ai oublié de dire, pour que ça tienne en moins de 50 k, j'ai dû élaguer : modules d'origine, 1 ou 2 boutons, désolé...

 

[myDearFriend]

Bonsoir le Forum,

Thierry, j'espère que je ne vais pas me rendre ridicule...

J'ai essayé de regarder ton problème, et j'ai effectué quelques modifs dans ton code :

Voici les résultats que j'obtiens :

4 occurences avec le document d'origine :
11,64

4 occurences avec les quelques modifs :
6.,09

(le gain est beaucoup moins flagrant avec 3 occurences)

Bien sûr, je n'ai pas réinventé la roue et n'ai même pas cherché à remettre en cause ta façon de traiter les boucles mais je me suis contenté d'économiser par ci, par là, le temps de calcul machine qui me semblait s'évaporer...

J'ai laissé exprès tes anciennes lignes (en commentaire) de façon à ce que tu puisses repérer les changements apportés. A mon avis, le gain de temps le plus significatif, je l'ai obtenu en dimensionnant les variables Currency en...Double. Eh oui, ne me demande pas pourquoi mais ça booste le calcul. Et ça n'a aucune conséquence sur les résultats dans le cas présent.

Peut-être qu'avec un amalgame de tous ses posts, tu pourras trouver la solution miracle !

Cordialement.
Didier