Macro VBA comparaison de 2 listes A et B

JJ1 · 12 Février 2014

Bonsoir à tous,

J'ai pratiquement terminé mon fichier de comparaison.

Je n'arrive pas à intégrer dans mon code la comparaison liste A-liste B pour alimenter la colonne L (10 cellules jaunes) et donc la somme en M1.

Pouvez-vous m'aider à finir, je joins le fichier expliqué.

Ce n'est peut-être pas optimal.

Merci et bonne nuit.

mapomme · 13 Février 2014

Re : Macro VBA comparaison de 2 listes A et B

Bonsoir JJ1,

Voir un essai dans le fichier joint. Le code est dans module 2:

VB:

Sub Test()
For Each xcell In Range("L1:L10")
  xcell.Value = LigneDansZone(xcell.Offset(, -5).Resize(, 5), Range("a3:e20"))
Next xcell
End Sub

Public Function LigneDansZone(xLigne As Range, xZone As Range)
Dim xcell As Range, xOK As Range
  For Each xcell In xLigne
    Set xOK = Nothing
    Set xOK = xZone.Find(xcell, LookIn:=xlValues, lookat:=xlWhole, _
            searchdirection:=xlPrevious, after:=xZone(1, 1))
    If Not xOK Is Nothing Then LigneDansZone = LigneDansZone + 1
  Next xcell
  LigneDansZone = IIf(LigneDansZone = 5, 1, Empty)
End Function

Edit: préférer la version v1a plus indépendante du cas particulier. Remplacé la ligne LigneDansZone = IIf(LigneDansZone = 5, 1, Empty) par la ligne LigneDansZone = IIf(LigneDansZone = xLigne.Count, 1, Empty)

mapomme · 13 Février 2014

Re : Macro VBA comparaison de 2 listes A et B

Bonsoir JJ1, Staple1600 🙂,

Pour ce que j'en ai compris, une adaptation de Sub Alea5() avec la fonction LigneDansZone.

VB:

Sub Alea5()
Const Nligne = 30   'Nbre de ligne à tirer au sort
Const SeuilMini = 25 'Nbre mini de lignes dans liste A
Dim i As Long

'effacement des précédents tirages
Range("G:L").ClearContents
'saisie de la formule dans les cellules de la colonne L
Range("L1:L" & Nligne).FormulaR1C1 = "=LigneDansZone(RC[-5]:RC[-1],R3C1:R20C5)"
' formule somme
Range("M1").FormulaLocal = "=SOMME(L:L)"
Application.ScreenUpdating = False

While [M1] < SeuilMini    'fin macro
  For i = 1 To Nligne
    ' si la ligne a déjà tous ses éléments dans la liste A
    ' alors on passe à la ligne suivante
    If Range("L" & i) <> 1 Then
      ' la ligne n'avait pas tous ses éléments dans la liste A
      Randomize
      Set dico = CreateObject("Scripting.dictionary")
      ' tirage de cinq numéros différents
      While dico.Count < 5
        x = Int((49 * Rnd) + 1)
        dico(x) = x
      Wend
      ' écriture des cinq numéros
      Range("G" & i).Resize(, 5) = dico.keys
    End If
  Next i
Wend

Application.ScreenUpdating = True
End Sub

Public Function LigneDansZone(xLigne As Range, xZone As Range)
Dim xcell As Range, xOK As Range
  For Each xcell In xLigne
    Set xOK = Nothing
    Set xOK = xZone.Find(xcell, LookIn:=xlValues, lookat:=xlWhole, _
            searchdirection:=xlPrevious, after:=xZone(1, 1))
    If Not xOK Is Nothing Then LigneDansZone = LigneDansZone + 1
  Next xcell
  LigneDansZone = IIf(LigneDansZone = xLigne.Count, 1, "")
End Function

Edit : une version paramétrée (voir les deux constantes Nligne et SeuilMini)

JJ1 · 13 Février 2014

Re : Macro VBA comparaison de 2 listes A et B

Bonjour Ma Pomme,

Merci pour ta réponse et ton fichier.
Toutefois, le comptage du résultat "1" concerne l'ensemble de la série de 5 (voir mon exemple joint)et non un nombre dans la plage.
merci à toi.

Bon AM

ps: une "formula local" est-elle plus rapide qu'une simple somme sur la Feuille? Vu la faible proba, je dois tester un max dans un min de temps, le paradoxe constant....

mapomme · 14 Février 2014

Re : Macro VBA comparaison de 2 listes A et B

Bonjour JJ1,

Une autre essai avec une nouvelle fonction (module 3). A mon humble avis, cela risque d'être très, très long, ne serait ce que pour trouver un seul tirage de ligne compris dans tableau A.

NOTA: Préferez la version JJ1 - test3b.xlsm du message #10

job75 · 14 Février 2014

Re : Macro VBA comparaison de 2 listes A et B

Bonjour JJ1, mapomme,

Si en L1 on veut le nombre de lignes qui répondent au critère :

Code:

=SOMMEPROD(N(ESTNUM(TROUVE(" "&G1&" ";" "&A$3:A$21&" "&B$3:B$21&" "&C$3:C$21&" "&D$3:D$21&" "&E$3:E$21&" "))+ESTNUM(TROUVE(" "&H1&" ";" "&A$3:A$21&" "&B$3:B$21&" "&C$3:C$21&" "&D$3:D$21&" "&E$3:E$21&" "))+ESTNUM(TROUVE(" "&I1&" ";" "&A$3:A$21&" "&B$3:B$21&" "&C$3:C$21&" "&D$3:D$21&" "&E$3:E$21&" "))+ESTNUM(TROUVE(" "&J1&" ";" "&A$3:A$21&" "&B$3:B$21&" "&C$3:C$21&" "&D$3:D$21&" "&E$3:E$21&" "))+ESTNUM(TROUVE(" "&K1&" ";" "&A$3:A$21&" "&B$3:B$21&" "&C$3:C$21&" "&D$3:D$21&" "&E$3:E$21&" "))=5))

Si l'on veut juste 1 ou 0 en L1 :

Code:

=SIGNE(SOMMEPROD(N(ESTNUM(TROUVE(" "&G1&" ";" "&A$3:A$21&" "&B$3:B$21&" "&C$3:C$21&" "&D$3:D$21&" "&E$3:E$21&" "))+ESTNUM(TROUVE(" "&H1&" ";" "&A$3:A$21&" "&B$3:B$21&" "&C$3:C$21&" "&D$3:D$21&" "&E$3:E$21&" "))+ESTNUM(TROUVE(" "&I1&" ";" "&A$3:A$21&" "&B$3:B$21&" "&C$3:C$21&" "&D$3:D$21&" "&E$3:E$21&" "))+ESTNUM(TROUVE(" "&J1&" ";" "&A$3:A$21&" "&B$3:B$21&" "&C$3:C$21&" "&D$3:D$21&" "&E$3:E$21&" "))+ESTNUM(TROUVE(" "&K1&" ";" "&A$3:A$21&" "&B$3:B$21&" "&C$3:C$21&" "&D$3:D$21&" "&E$3:E$21&" "))=5)))

On devrait pouvoir faire des formules plus simples sur le même principe.

A+

job75 · 14 Février 2014

Re : Macro VBA comparaison de 2 listes A et B

Re,

Evidemment on peut toujours définir le nom matrice par :

Code:

=" "&Feuil1!A$3:A$21&" "&Feuil1!B$3:B$21&" "&Feuil1!C$3:C$21&" "&Feuil1!D$3:D$21&" "&Feuil1!E$3:E$21&" "

et entrer en L1 :

Code:

=SIGNE(SOMMEPROD(N(ESTNUM(TROUVE(" "&G1&" ";matrice))+ESTNUM(TROUVE(" "&H1&" ";matrice))+ESTNUM(TROUVE(" "&I1&" ";matrice))+ESTNUM(TROUVE(" "&J1&" ";matrice))+ESTNUM(TROUVE(" "&K1&" ";matrice))=5)))

A+

job75 · 14 Février 2014

Re : Macro VBA comparaison de 2 listes A et B

Re,

Si l'on veut un tableau dynamique définir le nom matrice par :

Code:

=" "&INDIRECT("A3:A"&derlig)&" "&INDIRECT("B3:B"&derlig)&" "&INDIRECT("C3:C"&derlig)&" "&INDIRECT("D3:D"&derlig)&" "&INDIRECT("E3:E"&derlig)&" "

avec le nom derlig défini par :

Code:

=EQUIV(9^9;Feuil1!$A:$A)

A+

JJ1 · 14 Février 2014

Re : Macro VBA comparaison de 2 listes A et B

Bonjour Mapomme, Job, le Forum

Merci pour la modification VBA (parfaite ) et bravo Job pour cette courte formule..;si tu veux bien, je conserve le code vba de Mapomme pour le test !!!!

Vu la proba, comme j'avais dit, il va falloir que "ça aille trés, trés vite" et laisser tourner longtemps (surtout que j'avais fixé mon mini à 9/10............)

Bonne journée et encore merci

mapomme · 14 Février 2014

Re : Macro VBA comparaison de 2 listes A et B

Bonsoir JJ1, le forum,

Attention!

Pour des besoins de test, j'avais rajouté une instruction inutile en fonctionnement normal qui ralentit un peu la procédure Alea5b().

Il vaut mieux utiliser le fichier joint d'où a été retirée l'instruction Zone = Range("A3:E21").Value juste après la ligne de commentaire ' ecriture de la comparaison.

JJ1 · 14 Février 2014

Re : Macro VBA comparaison de 2 listes A et B

Bonsoir ma pomme merci pour l'info. Je n'arrive pas a compter le nb de fois ou la série donne 5 au lieu de mettre systématiquement 1? Si tu as un moment merci bonne soiree

mapomme · 14 Février 2014

Re : Macro VBA comparaison de 2 listes A et B

Bonsoir JJ1,

Je ne comprends pas la question "Compter le nombre de fois où la série donne 5".

La fonction LigneDansZoneb renvoie 1 si les cinq nombres de la ligne du tableau B sont dans une même ligne du tableau A sinon elle renvoie 0.

Attention! cette fonction n'est pas directement utilisable sur la feuille de calcul.

JJ1 · 14 Février 2014

Re : Macro VBA comparaison de 2 listes A et B

Bonsoir Ma pomme,
je voulais dire qu'en cas de doublon ou plus en colonne A, je souhaitais le nombre de fois où la même série est présente et pas systématiquement 1 comme ici dans le code:
If nEgal = 5 Then
Zoneb = 1' ou plus
Exit Function
End If

Je viens de t'écrire.
merci à toi
Bonne nuit.

mapomme · 15 Février 2014

Re : Macro VBA comparaison de 2 listes A et B

Bonjour JJ1,

Une version avec comptage des apparitions de chaque ligne du tableau B dans les lignes du tableau A. La durée d'exécution a été raccourcie car il ne se fait plus d'écriture de résultats intermédiaires sur la feuille.

Pour les besoins de test, j'ai introduit une nouvelle constante Ncol qui est le nombre de tirages à faire par ligne. Ncol doit être supérieur ou égal à deux.

Si Ncol = 2 alors on ne prend en compte que les Ncol premières colonnes du tableau A et on ne tire que Ncol valeurs par ligne du tableau B. Toujours pour les tests, un jeu du tableau A a été construit avec des redondances dans les lignes et interversion des colonnes 1 et 2.

Avec les paramètres initiaux du fichier, on voit bien le temps d’exécution augmenter avec Ncol (entre deux et cinq), la probabilité d'atteindre le seuil diminuer et le nombre de tirages augmenter.

Nota: Au cours des tirages successifs de tout le tableau B, chaque fois qu'une ligne de B est présente dans le tableau A, elle est figée pour les tirages suivants. Ce n'est peut-être pas ce vous désirez.

JJ1 · 15 Février 2014

Re : Macro VBA comparaison de 2 listes A et B

Bonjour Mapomme, Le Forum

Merci pour cette mise à jour. Par contre, je dois étendre la série à 5 (dans le fichier il n'y a plus que 2 nombres) et surtout ne pas figer un tirage ---Cf message.
C'est ce point RND qui me pose souci. Je vais essayer de passer par une autre méthode pour comparer ensuite simulation/résultat en insérant cette nouvelle méthode (remplaçant alea) dans ton code. Je pense que je vais m'y amuser.........mais rien ne presse!

Je prépare un exemple de cette méthode et je le mets sur le Forum.

Un grand merci.

Bon WE

Macro VBA comparaison de 2 listes A et B

JJ1

Guest

Pièces jointes

mapomme

XLDnaute Barbatruc

Pièces jointes

mapomme

XLDnaute Barbatruc

Pièces jointes

JJ1

Guest

Pièces jointes

mapomme

XLDnaute Barbatruc

job75

XLDnaute Barbatruc

job75

XLDnaute Barbatruc

job75

XLDnaute Barbatruc

JJ1

Guest

mapomme

XLDnaute Barbatruc

Pièces jointes

JJ1

Guest

mapomme

XLDnaute Barbatruc

JJ1

Guest

mapomme

XLDnaute Barbatruc

Pièces jointes

JJ1

Guest

Discussions similaires

Macro VBA comparaison de 2 listes A et B

JJ1

Guest

Pièces jointes

XLDnaute Barbatruc

Pièces jointes

XLDnaute Barbatruc

Pièces jointes

JJ1

Guest

Pièces jointes

XLDnaute Barbatruc

XLDnaute Barbatruc

XLDnaute Barbatruc

XLDnaute Barbatruc

JJ1

Guest

XLDnaute Barbatruc

Pièces jointes

JJ1

Guest

XLDnaute Barbatruc

JJ1

Guest

XLDnaute Barbatruc

Pièces jointes

JJ1

Guest

Discussions similaires

Privacy & Transparency

Privacy & Transparency