Discussion :

[unparia]

Tu ARRETES, oui, avec COUNTIF ????
Relis-moi ! on PASSE A UNE AUTRE MANIERE (où Countif ne sera même pas utilisé) !
Tu suis ou je vais dormir ?

[patricktoulon]

Tu commences à deviner le reste (la 3ème étape) ?

oui la on reviens vers ton model que nous connaissons

ensuite tu tri la colon concat odre alpha
ensuite tu pointe le i+1 si c'est pareil tu fait ce que tu veux de la manière que tu veux pour pointer soit tu blanchi la cell soit tu utilise une col pour les index en marge
en dernier tu tri la colonne des rows pour les remettre a l'ordre d'origine
tu supprime les colonnes temporaires
j'ai très bien compris ton model 200milieme de plus que collection a peu près sur la totalité de ligne d'un sheets xls(65536)

[unparia]

ensuite tu tri la colon concat odre alpha

Non ! Pas encore ! Je n'ai pas envie du tout de torturer Excel en triant dès maintenant !
Et pour la suite : tu attendras demain. Je vais dormir

[RyuAutodidacte]

re,

ça ne serait pas plus rapide comme cela :

Code :

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

Range("F1:F" & n).Formula = Evaluate("A1 & ""#"" & B1 & ""#"" & C1 & ""#"" & D1 & ""#"" & E1")

Edit : hola je suis fatigué je viens de me rendre compte de l'erreur

Edit2 : c'était plutôt comme cela que je voulais l'écrire

Code :

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

Range("F" & n) = Evaluate("=A" & n & "& ""#"" & B" & n & "& ""#"" & C" & n & "& ""#"" & D" & n & "& ""#"" & E" & n & "& ""#""")

et en effet trop long 5 s sur 200000

[unparia]

Bonjour Ryu.
Non (plus long et concaténation non faite)
Mais à demain. J'attends que chacun ait retrouvé ses esprits.

Bonjour à tous
A nous deux, patricktoulon
Ce matin nous allons, avant toute autre chose, nous constituer tous les deux très exactement les mêmes cellules de données, sur le même nombre de lignes, de sorte à être certains de ce que nous ayons de l'identique absolu entre les mains à tout moment.
J'ai choisi 200 000 à traiter car :
- c'est une bonne moyenne pour refléter les situations de traitement des doublons
- c'est souvent au dessus de 100 000 que les collections deviennent plus lentes.
je n'y fais allusion qu'à titre de comparaison, puisque nous allons traiter sans collection, ni dico, ni countif, ni match
Si tu veux "rebondir" sur cet aspect-là : mets-le sur un pense-bête et n'y reviens qu'à la fin de ce que nous
traitons en ce moment. Je n'accepterai pas de nouveaux méandres et/ou égarements "en chemin". A la moindre incartade à ce propos : je te quitte tout net.
On est bien d'accord ?

Allez, donc :
On se constitue tous les deux la même base de travail, avec ce code :

Code :

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

1
2
3
4
5
6
7
8
9
Application.ScreenUpdating = True
  Dim i As Long, k As Long
  Randomize Timer
  For i = 1 To 200000
    For k = 1 To 5
      Cells(i, k).Value = Chr(Int((5 * Rnd) + 65))
    Next
  Next
  Application.ScreenUpdating = False

J'ai choisi cette méthode car elle nous donne la quasi certitude d'obtenir des doublons disséminés.
Lance et sois patient (traitement long). Reviens me dire quand ta base est ainsi prête

EDIT : si d'autres suivent parallèlement, il sera bien qu'ils fassent très exactement la même chose de leur côté, également. Cela permettra qu'aucun doute n'existe en matière de "comparaisons", s'ils interviennent.

EDIT 2 : la base que nous avons ainsi construite va probablement contenir, chez chacun, un très grand nombre de doublons.
Lorsque nous voudrons faire des tests différents (beaucoup de doublons - moins de doublons - peu de doublons, etc ...) il suffira de partir du même code en ne modifiant qu'un seul chiffre (ici le 5 du "5 * rnd"). Le nombre de doublons sera toujours (Chacun comprendra bien pourquoi) inversement proportionnel à celui de ce chiffre.

[pijaku]

Bonjour à toutes et tous,

Ce qu'il est IMPORTANT de garder à l'esprit :
Une chose est le recensement des doublons. Une autre est ce que l'on fait de ce recensement.
Et donc :
1) recenser (selon les critères de colonnes) --->> obtention d'une plage des doublons

C'est très exactement cela que j'entendais en parlant de fonction universelle à propos des doublons...

Bon, ceci étant dit, j'ai préparé mon fichier et bien fait mes devoirs.
Pour les débutants suivants cette discussion, j'ai utilisé ce code pour ne pas passer 2-3 minutes devant 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
Option Explicit
 
Sub Remplissage()
  Dim i As Long, k As Long, L(199999, 4) As String
  Randomize Timer
  For i = 0 To 199999
    For k = 0 To 4
      L(i, k) = Chr(Int((5 * Rnd) + 65))
    Next
  Next
  Application.ScreenUpdating = False
  Range("A1").Resize(200000, 5) = L
  Application.ScreenUpdating = True
End Sub

Je suis prêt, j'attends la suite...

[unparia]

Bonjour Franck
Et merci. Passer par une matrice est très bien (plutôt que de bailler devant la machine).
On attend que patricktoulon soit prêt et le fasse savoir.

[RyuAutodidacte]

Bonjour,
Des que j'ai un peu de temps (ça va etre chaud, j'ai presque pas 2mn) je vous suis

[unparia]

Pas encore là, patricktoulon ...
J'ai en attendant une très bonne nouvelle : moins de 4,5 secondes pour dresser ma plage d'environ 196000 doublons ("composés" sur 5 colonnes) d'une feuille de calcul comprenant 200.000 lignes (pas seulement une soixantaine de milliers).
Je ne crois pas pouvoir faire encore plus vite

[Marc-L]

Bonjour !

On se constitue tous les deux la même base de travail, avec ce code :

Code :

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

  Randomize Timer

Jacques, ne vaudrait-il pas mieux imposer une valeur au Randomize afin que tous disposent exactement de la même base ? …

_________________________________________________________________________________________________________
Je suis Paris, Istanbul, Berlin, Nice, Bruxelles, Charlie, …

[unparia]

Bonjour Marc
Non ! Car la répartition et le nombre des doublons n'ont eux (tu pourras le constater) aucune espèce d'incidence sur la vitesse d'exécution. Le seul facteur d'importance (et immuable) restera le nombre de lignes de la plage de base et le nombre des colonnes servant à la concaténation.
Avec les mêmes données : la durée restera fixe indépendamment du nombre des doublons et de leur dissémination.
C'est même l'une des raisons qui ont fait (voir plus haut) que je me suis arrangé pour que des tests puissent être faits en modifiant le nombre qui détermine la fréquence possible de doublons. On pourra constater que ces différences de fréquences n'ont aucun impact, elles, sur la vitesse d'exécution.
Mais on aura l'occasion d'en reparler après tests.

EDIT : mais patricktoulon ne s'est toujours pas manifesté (alors même qu'il est passé par là ce matin) et nous ne savons même pas s'il est prêt à suivre ... la suite ...
Que faire en cas de mutisme persistant ? Je propose que l'on continue seuls s'il ne s'est toujours pas manifesté à 22 heures.

[pijaku]

Je propose que l'on continue seuls s'il ne s'est toujours pas manifesté à 22 heures.

A mon avis, tu peux y aller, il est assez compétent pour reprendre en cours de route...

[unparia]

Bonjour Franck
Non. Il n'y a rien d'urgent et j'attends donc jusqu'à 22 heures
Il n'en va pas que de l'avenir de la présente discussion, mais d'un principe concernant plein de discussions de même "nature" ou "dirigées" dans le même "type de contexte"

[RyuAutodidacte]

Re,
En tout cas pour 22h moi ça m'arrange car je serai plus disponible et les enfants seront déjà couchés
Donc si tout le monde est ok ça me va

[unparia]

Bien.
Il est 22 heures et je pense que ceux qui étaient intéressés ont mis en place la "matière de travail" (200 000 lignes sur 5 colonnes). Les autres sortiront du bois plus tard, je pense
Nous allons maintenant repartir de l'endroit où nous en étions (message 119), à savoir :

Bien --> seconde étape ?
attribue un N° de ligne en colonne G (toujours par Formula avec la fonction ROW() )
Tu commences à deviner le reste (la 3ème étape) ?

je mets ici tout le code qui conduit jusqu'à cette 3ème étape, qui consiste à mettre en colonne G tout simplement les n°s de lignes correspondant aux concaténations obtenues en colonne F

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
Private Sub CommandButton5_Click()
  Application.ScreenUpdating = False
  Dim i As Long, n As Long, c As String, pl_doublons As Range, derlig As Long, les_cols
  Columns("H").ClearContents
  deb = Timer '===================================== juste pour tester
  ActiveSheet.UsedRange
  n = Cells.SpecialCells(xlCellTypeLastCell).Row
  les_cols = Array("A", "B", "C", "D", "E")
  formule = "=" & les_cols(0) & "1" ' ----->> début de notre formule (toujours le même)
  For i = 1 To UBound(les_cols) '-----------------------------------------
    formule = formule & " & """ & Chr(1) & """ & " & les_cols(i) & "1"   '| et on concatème la formule en boucle
  Next ' -----------------------------------------------------------------
  With ActiveSheet
    .Range("F1:F" & n).Formula = formule '--->> nous concaténons ici (regardez la construction de la chaîne formule)
    .Range("G1:G" & n).Formula = "=Row()" ' --->> nous attribuons (par simple constat) les Nos de lignes correspondants
 
'.....
'.....
   End With
  Application.ScreenUpdating = True
End Sub

et je répondais (message 123) à patricktoulon, qui entrevoyait un tri à l'issue de cette étape :

Non ! Pas encore ! Je n'ai pas envie du tout de torturer Excel en triant dès maintenant !

Pourquoi ? Tout simplement parce que les colonnes F et G contiennent des FORMULES. Si on les trie dès maintenant, non seulement Excel sortira du pc pour nous ficher une baffe, mais nous sèmerions en plus une belle pagaille (les concaténations ne correspondront plus. Les N°s de ligne non plus).

"Figeons" donc de suite les valeurs de ces deux colonnes, par le simple ajout de cette ligne de code en ligne 16:

Code :

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

.Range("F1:G" & n).Value = Range("F1:G" & n).Value '--->> nous figeons ces valeurs (indépendantes alors des formules) pour ne pas torturer Excel

-->> plus de formules, maintenant, mais des constantes.
Faites-en de même puis nous continuerons (je pense que vous avez déjà deviné tout le reste, mais nous allons le faire étape par étape, histoire de ne rien rater du mécanisme)
J'attends vos confirmations.

NB : juste une petite précision pour éviter de s'écarter du principal. J'ai dans ce test choisi d'utiliser les colonnes F et G uniquement pour que l'on puisse aisément les visualiser. Lorsque nous en aurons fini, nous utiliserons, au choix, les toutes dernières colonnes possibles de la feuille (columns.count) ou une autre feuille .

[RyuAutodidacte]

Re
peu de retard mais je prépare cela vite fais

Edit : C'est OK là je vous suis

Edit 2 : En effet c'est rapide même si sur Mac c'est légèrement moins rapide que sur PC

Edit 3 : C'est malin Patrick t'a fait fuir unparia , bon j'ai plus qu'a attendre (en rapport du message ci-dessous)

[patricktoulon]

Bonsoir a tous
purée pas sympa de pas m'attendre

journée pourrie ,du boulot par dessus a tète et un weekend boulot en perspective et ben continuez ne vous arrêtez pas de toute façon pour ce weekend c'est râpé pour moi le calme
mes cuisines ont enfin été livrées avec quelques marbres ébréchés mais tant pis je les poses quand même ce ce weekend
suivrais petit a petit a partir de la ou j'ai quitté

la vraiment trop crevé vais me mettre un peu de musique et me couché

[RyuAutodidacte]

re
je suppose qu'une fois qu'on a les value, on passe au tri des doublons …

[patricktoulon]

re
non ryu on ne tri pas les doublons tout de suite apres avoir les values

on tri l'ordre alpha je crois de "F" d'abors les doublons se suivront forcement

j'ai fait le truc vite fait pour vous rattraper

Code :

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

1
2
3
4
5
6
7
8
9
Sub test10()
    With Sheets(1)
        Set plage = .Range("A1:E" & .Cells(Rows.Count, 1).End(xlUp).Row)    ' plage a prendre en compte
        n = .Cells(Rows.Count, 1).End(xlUp).Row
        .Range("F1:F" & n).Formula = "=A1 & ""#"" & B1 & ""#"" & C1 & ""#"" & D1 & ""#"" & E1"
        .Range("G1:G" & n).Formula = "=Row()"
        .Range("F1:G" & n).Value = .Range("F1:G" & n).Value
    End With
End Sub

entre parenthèses (bien vu le .value=.value ca shunte le calculate pour la suite

[unparia]

je suppose qu'une fois qu'on a les value, on passe au tri des doublons …

pas tout-à-fait, Ryu.
On le les connais pas encore, les doublons (on ne les connapitra qu'à la fin.
On va maintenant plutôt trier en bloc les colonnes F et G, par ordre alphabétique des concaténations obtenues en F, ainsi (à mettre en ligne 17, donc) :

Code :

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

1
2
.Range("F1:G" & n).Sort key1:=.Range("F1:G" & n), order1:=xlAscending, Header:=xlNo ' -->> et nous trions les concaténations sans (surtout) toucher aux
                                                                                        ' autres colonnes. Seuls les N)s de lignes suivront ce tri

sans rien toucher de l'ordre des données (colonne A à E)
fais cela et regarde le résultat.

Une fois cela vu, on va rajouter (en ligne 18) de quoi identifier les doublons, ainsi :

Code :

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

1
2
3
.Range("H1").Value = Range("F1").Value ' --->> la 1ère ligne n'est forcément pas un doublon
    .Range("H2:H" & n).Formula = "=IF(F2=F1,"""",F2)" ' --->> nous mettons à "" les valeurs trouvées si égales à celles de la
                                                      ' cellule atteinte par un déplacement type cheval aux échecs

observe ce qui se passe en colonne H
Et on va les figer, maintenant, ces valeurs (ce sont en effet des formules !) --->>

Code :

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

.Range("H2:H" & n).Value = .Range("H2:H" & n).Value '--->> et là aussi, nous figeons les valeurs, pour toujours les mêmes raisons

Voilà voilà : on remet maintenant tout dans l'ordre de départ --->>

Code :

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

1
2
  .Range("G1:F" & n).Sort key1:=.Range("G1"), order1:=xlAscending, Header:=xlNo '-->> nous remettons les choses dans l'ordre originel, toujours sans toucher à l'ordre
                                                                                  ' des données traitées. Du coup, les doublons reviendront à leur rang de départ

et nos doublons ? Ils sont tout bêtement dans cette plage :

Code :

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

Set pl_doublons = Range("H1:H" & n).SpecialCells(xlCellTypeBlanks) '--->> ben la voilà, notre plage des doublons, non ?

Il nous reste à balayer le sol pour rendre à son propriétaire un local propre --->>

Code :

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

  Columns("F:H").ClearContents '--->> histoire de rendre l'état des lieux tel qu'il l'était au départ (juste du "ménage")

C'est tout bête, non ?

Voilà donc maintenant le code dans sa totalité (pas besoin d'autres explications) :

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
30
31
32
33
34
35
36
37
38
39
40
41
 
Private Sub CommandButton5_Click()
 
  Application.ScreenUpdating = False
  Dim i As Long, n As Long, c As String, pl_doublons As Range, derlig As Long, les_cols
  Columns("H").ClearContents
  deb = Timer '===================================== juste pour tester
  ActiveSheet.UsedRange
  n = Cells.SpecialCells(xlCellTypeLastCell).Row
  les_cols = Array("A", "B", "C", "D", "E")
  formule = "=" & les_cols(0) & "1" ' ----->> début de notre formule (toujours le même)
  For i = 1 To UBound(les_cols) '-----------------------------------------
    formule = formule & " & """ & Chr(1) & """ & " & les_cols(i) & "1"   '| et on concatème la formule en boucle
  Next ' -----------------------------------------------------------------
  With ActiveSheet
    .Range("F1:F" & n).Formula = formule '--->> nous concaténons ici (regardez la construction de la chaîne formule)
    .Range("G1:G" & n).Formula = "=Row()" ' --->> nous attribuons (par simple constat) les Nos de lignes correspondants
    .Range("F1:G" & n).Value = Range("F1:G" & n).Value '--->> nous figeons ces valeurs (indépendantes alors des formules) pour ne pas torturer Excel
    .Range("F1:G" & n).Sort key1:=.Range("F1:G" & n), order1:=xlAscending, Header:=xlNo ' -->> et nous trions les concaténations sans (surtout) toucher aux
                                                                                        ' autres colonnes. Seuls les N)s de lignes suivront ce tri
    .Range("H1").Value = Range("F1").Value ' --->> la 1ère ligne n'est forcément pas un doublon
    .Range("H2:H" & n).Formula = "=IF(F2=F1,"""",F2)" ' --->> nous mettons à "" les valeurs trouvées si égales à celles de la
                                                      ' cellule atteinte par un déplacement type cheval aux échecs
    .Range("H2:H" & n).Value = .Range("H2:H" & n).Value '--->> et là aussi, nous figeons les valeurs, pour toujours les mêmes raisons
    .Range("G1:F" & n).Sort key1:=.Range("G1"), order1:=xlAscending, Header:=xlNo '-->> nous remettons les choses dans l'ordre originel, toujours sans toucher à l'ordre
                                                                                  ' des données traitées. Du coup, les doublons reviendront à leur rang de départ
    Set pl_doublons = Range("H1:H" & n).SpecialCells(xlCellTypeBlanks) '--->> ben la voilà, notre plage des doublons, non ?
    Columns("F:H").ClearContents '--->> histoire de rendre l'état des lieux tel qu'il l'était au départ (juste du "ménage")
  End With
  Application.ScreenUpdating = True
 
  '==========================================================================================================================
  '================ le travail est terminé
  ' =============== ce qui suit n'est là qu'en tant que visualisation (l'une de ses formes possibles suffira)
 
  MsgBox Timer - deb & "  secondes pour traiter " & n & " lignes de 5 colonnes et extraire la plage " & pl_doublons.Address
  'Application.ScreenUpdating = True
  MsgBox "tiens ... on va (par exemple) la sélectionner"
  pl_doublons.EntireRow.Select
 
End Sub

et une copie de mon écran :


Il faudra penser au cas "aucun doublon" et donc "If not plage_doublons is nothing then ... tant pour la msgbox que pour la sélection

Bonne nuit.