Création de clés de répartition en masse

[jessica.francois]

Bonjour à tous,

J'ai encore besoin de votre aide !!

Dans le cadre de mon travail, je dois créer des clés d'analyse a partir d'un fichier volumineux.

J'ai préparé un petit fichier exemple que vous trouverez en pièce jointe qui illustrera bien mieux mon problème qu'une longue explication !

Au final mon fichier comporte 45K lignes donc j'estime le fichier résultat de plusieurs millions de lignes ...
je souhaiterais que le résultat sorte directement dans un fichier .txt pour ne pas avoir le pb des 1 millions de lignes Excel 🙂

Merci d'avance de votre aide !!!

Jessica

 

[herve62]

Re : Création de clés de répartition en masse

Bonsoir
J'ai juste jeté un oeil !
Ce que tu souhaites , c'est par exemple extraire une selection du fichier comme juste le matricule MM0001 et sa clé( ou role) puis la copier dans un .txt ?

 

[job75]

Re : Création de clés de répartition en masse

Bonsoir jessica.francois, herve62,

Pourquoi ne pas stocker les résultats dans un tableau (vecteur) VBA à une dimension ?

Je crois que les tableaux VBA ne sont limités que par la mémoire disponible.

Le tableau pourra être mémorisé dans un nom défini pour ne pas avoir à le recalculer à chaque ouverture d'Excel.

Ainsi on pourra l'exploiter aussi facilement (et beaucoup plus rapidement) que sur une feuille de calcul.

Pour tester cette solution, il nous faudrait un exemple de ce que vous voulez en faire.

A+

 

[Cousinhub]

Re : Création de clés de répartition en masse

Bonsoir,

Bonsoir Job 😀

Bonsoir Hervé (PS, n'oublie pas de suivre tes fils.....)

@jessica....

Essaie ce code
Avec le fichier que tu as joint, je fais 1378 lignes (sachant que tu n'as que 67 lignes dans le fichier initial), et ceci en 0.08 seconde et quelques....
Enregistre ton fichier initial dans un répertoire temporaire, par exemple

Dans le code, j'effectue tout d'abord un tri de tes données, afin d'avoir les données en colonne A dans l'ordre1, et les données de la colonne B en ordre2

En fin de code, dans ton répertoire, tu dois récupérer un fichier texte nommé "Concat.txt", et chaque donnée est séparée par un ";"

Plus un message box te donnant le temps de traitement...(pour mon info personnelle, je serai curieux de connaître le temps de traitement sur ton fichier réel)

Option Explicit
Sub essai()
Dim Cel As Range
Dim Matricules As Object, Roles As Object
Dim Lig As Long, Nbr As Long, I As Long, DerLig As Long, J As Long, K As Long, L As Long
Dim It, Tbl1, Tbl2
Dim LeRep As String, Ligne As String
Dim T As Single
T = Timer
Set Matricules = CreateObject("Scripting.Dictionary")
Set Roles = CreateObject("Scripting.Dictionary")
DerLig = Cells(Rows.Count, 1).End(xlUp).Row
Range("A1:B" & DerLig).Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2") _
        , Order2:=xlAscending, Header:=xlYes
For Each Cel In Range("A2:A" & DerLig)
    Matricules(Cel.Value) = Cel.Value
Next Cel
K = 1
For Each It In Matricules.Items
    L = 0
    Lig = Application.Match(It, Columns(1), 0)
    Nbr = Application.CountIf(Columns(1), It)
    For I = 1 To Nbr
        For J = 1 To Nbr
            If J <> I Then
                Roles(It & ";" & K) = Cells(Lig, 2).Offset(L).Value & ";" & Cells(Lig, 2).Offset(J - 1).Value
                K = K + 1
            End If
        Next J
        L = L + 1
    Next I
Next It
Tbl1 = Roles.Items
Tbl2 = Roles.keys
LeRep = ThisWorkbook.Path
Open LeRep & "\Concat.txt" For Output As #1
For I = 0 To Roles.Count - 1
    Ligne = ""
    Ligne = Ligne & Split(Tbl2(I), ";")(0) & ";" & Tbl1(I) & ";"
    Print #1, Left(Ligne, Len(Ligne) - 1)
Next I
Close #1
MsgBox Timer - T
End Sub

Bon courage, et bonne soirée

 

[jessica.francois]

Re : Création de clés de répartition en masse

Super !

Ca a l'air de fonctionner. La macro a mis 250 secondes. le fichier de sorti est un fichier plat de 100mo.

Merci 1000 fois

 

[job75]

Re : Création de clés de répartition en masse

Bonjour jessica, salut bhbh, mes meilleurs voeux pour 2012 🙂

Contrairement à ce que je disais au post #3 il n'est pas possible de mémoriser dans un nom défini.

Apparemment (sur Excel 2003) un nom stocke au maximum 256 éléments d'un vecteur.

Alors voyez le fichier joint.

La macro crée 2 vecteurs VBA t1 et t2 qui sont mémorisés.

Pour les récupérer dans la feuille de calcul j'utilise les fonctions volatiles Matricule() et Clé()

Le code dans Module1 (Alt+F11) :

Option Explicit
Dim t1$(), t2$() 'mémorisation des tableaux

Sub Mémoriser()
Dim h&, t, lig&, k&, i&, j&, n&
[A:B].Sort [A1], , [B1], Header:=xlYes 'tri du tableau
h = Cells(Rows.Count, 1).End(xlUp).Row
t = [A1].Resize(h + 1, 2) 'matrice (plus rapide) avec ligne vide
For lig = 2 To h
  k = lig + 1
  While t(k, 1) = t(lig, 1): k = k + 1: Wend
  k = k - 1
  For i = lig To k
    For j = lig To k
      If i <> j Then
        ReDim Preserve t1(n): ReDim Preserve t2(n)
        t1(n) = t(i, 1): t2(n) = t(i, 2) & t(j, 2)
        n = n + 1
      End If
    Next j
  Next i
  lig = k
Next lig
If n = 0 Then Erase t1: Erase t2 'RAZ
ActiveSheet.Calculate
End Sub

Function Matricule()
Application.Volatile
Matricule = t1
End Function

Function Clé()
Application.Volatile
Clé = t2
End Function

Le code dans ThisWorkbook recalcule les 2 vecteurs à chaque ouverture du fichier :

Private Sub Workbook_Open()
Feuil1.Activate 'Codename
Mémoriser
Me.Saved = True
End Sub

Fichier joint, voir les formules en D6 et E6 ainsi qu'en D71 et D72.

A+

 

[job75]

Re : Création de clés de répartition en masse

Re,

Si l'on préfère, au lieu de 2 vecteurs, on peut créer une matrice à 2 dimensions.

Restitution dans la feuille par la fonction RESULT :

Option Explicit
Dim t1$() 'mémorisation du tableau

Sub Mémoriser()
Dim h&, t, lig&, k&, i&, j&, n&
[A:B].Sort [A1], , [B1], Header:=xlYes 'tri du tableau
h = Cells(Rows.Count, 1).End(xlUp).Row
t = [A1].Resize(h + 1, 2) 'matrice (plus rapide) avec ligne vide
For lig = 2 To h
  k = lig + 1
  While t(k, 1) = t(lig, 1): k = k + 1: Wend
  k = k - 1
  For i = lig To k
    For j = lig To k
      If i <> j Then
        ReDim Preserve t1(1, n)
        t1(0, n) = t(i, 1): t1(1, n) = t(i, 2) & t(j, 2)
        n = n + 1
      End If
    Next j
  Next i
  lig = k
Next lig
If n = 0 Then Erase t1 'RAZ
ActiveSheet.Calculate
End Sub

Function RESULT()
Application.Volatile
RESULT = Application.Transpose(t1)
End Function

Fichier (2).

A+

 

[job75]

Re : Création de clés de répartition en masse

Re,

Ah j'oubliais, Application.Transpose ne va que jusqu'à 65536 lignes ou colonnes 😡

Remplacer le fichier (2) précédent par le fichier joint.

Et voyez l'inversion des argument dans les formules en D6 et E6.

A+