Scinder en plusieurs colonnes une séquence approximative

Le 11/08/2011 à 18:45

Bonjour !

Je souhaiter redispatcher les données d'une colonne, correspondant à une certaine séquence (mais environ 142 500 lignes au total), en plusieurs colonnes.

L'origine de ce fichier : environ 500 formulaires postés sur un site web nous sont parvenus depuis 2 ans sous forme de mails.

Impossible de les récupérer dans la base de données du site web, et c'est pour le fonctionnement de notre association une mine d'or qui l'avait jusqu'ici trop peu utilisé.

J'ai exporté d'Outlook ces 500 mails au format CSV (séparé par des tabulations), en pensant que chaque mail se retrouverait dans une colonne différente lors de l'import sous Excel.

Au passage, cette méthode d'export CSV contient l'ensemble des données des formulaires, contrairement à des exports direct en excel à partir d'outlook (30 à 60% de données perdues par formulaire en moyenne).

Ainsi, dans ma colonne se succèdent les 500 résultats du formulaire :

  • Le début du formulaire est identifié par le mot "Organisme"
  • Chaque formulaire n'a pas une taille fixe, puisque des champs libre, où le répondant faisait des sauts de ligne, occupent le nombre de ligne correspondant (mais en gros, 560 lignes par formulaire)

Ma question : à chaque fois qu'une cellule "Organisme" est rencontrée, peut-on basculer l'ensemble des cellules inférieures dans une autre colonne ? (pour ensuite réitérer le processus, afin d'avoir un formulaire par colonne, et pas plusieurs consécutifs)

Je vous remercie d'avance pour vos conseils !

Cordialement,

François

PS : j'utilise excel 2007, mais ai depuis aujourd'hui une version d'évaluation de 2010.

Le 11/08/2011 à 21:29

Bonjour Fhqed,

Plus facile avec une macro mais je te propose une solution par formule.

1- Juste après la dernière ligne, tu mets le mot "Organisme"

2- En B1, tu mets la formule suivante :

=SI($A1="";"";SI(LIGNE()>SOMMEPROD(PETITE.VALEUR(($A$1:$A$847="Organisme")*LIGNE($A$1:$A$847);NB.SI($A$1:$A$847;"<>Organisme")+COLONNE()))-SOMMEPROD(PETITE.VALEUR(($A$1:$A$847="Organisme")*LIGNE($A$1:$A$847);NB.SI($A$1:$A$847;"<>Organisme")+COLONNE()-1));"";DECALER($A$1;SOMMEPROD(PETITE.VALEUR(($A$1:$A$847="Organisme")*LIGNE($A$1:$A$847);NB.SI($A$1:$A$847;"<>Organisme")+COLONNE()-1))+LIGNE()-2;;;)))

Bien sûr tu adaptes en fonction de ton nombre de lignes en colonne A.

3- Tu tires cette formule sur 560 lignes car environ 560 lignes par formulaire d'après toi. Mais pour être sûr, tu peux la tirer sur + de ligne.

4- Tu tires tout ça sur la droite jusqu'à ce que tu rencontres l'erreur #NOMBRE!

5- Tu supprimes la ou les colonne contenant ces erreurs

6- Tu fais un copier-collage spécial valeurs pour enlever toutes les formules et le tour est joué (normalement).

Si c'est trop lent sur tes 100 000 et quelques lignes, une solution par macro est envisageable et normalement plus rapide.

Le 12/08/2011 à 10:56

Bonjour le forum,

Une proposition avec une macro dans le classeur joint

Cordialement

52re-sequencedansunecolonne.zip (26.20 Ko)
Le 12/08/2011 à 12:55

@vba-new

Merci pour avoir conçu cette formule qui me renverse !

En l'appliquant au fichier test avec 3 séquences, ils fonctionnent bien (sauf pour la dernière, mais elle peut-être faite en manuel).

Sur le fichier réel, la modification de la plage faite, j'obtiens le message #NOM? dans toutes les cellules... Ne comprenant pas tout le séquencement de la formule, je ne sais pas s'il faut modifier autre chose que la plage.

Au cas où, la formule MAJ :

=SI($A1="";"";SI(LIGNE()>SOMMEPROD(PETITE.VALEUR(($A$1:$A$142501="Organisme")*LIGNE($A$1:$A$142501);NB.SI($A$1:$A$142501;"<>Organisme")+COLONNE()))-SOMMEPROD(PETITE.VALEUR(($A$1:$A$142501="Organisme")*LIGNE($A$1:$A$142501);NB.SI($A$1:$A$142501;"<>Organisme")+COLONNE()-1));"";DECALER($A$1;SOMMEPROD(PETITE.VALEUR(($A$1:$A$142501="Organisme")*LIGNE($A$1:$A$142501);NB.SI($A$1:$A$142501;"<>Organisme")+COLONNE()-1))+LIGNE()-2;;;)))

Je vais faire des tests avec des sous-ensembles pour voir si c'est un problème de nombre de lignes/colonnes.

@Papouclo

Merci également pour cette macro !

(j'ai collé dans la première colonne les données réelles de mon problème, puis ai lancé la macro).

Erreur 6 : "dépassement de capacité"

Vu la variable 655630 j'ai pris un sous ensemble des données pour ne pas avoir erreur 6 (21145 lignes exactement).

Relancement de la macro : erreur 9, "l'indice n'appartient pas à la sélection."

La cellule active est à ce moment la dernière non vide de ma colonne.

Dans le debogueur, il surligne, dans la fonction "Détermination dans Tab2 des lignes des cellules contenant "Organisme"", la ligne "tab2(i) = cptli".

D'avance merci pour vos explications si l'erreur vous paraît compréhensible...

Le 12/08/2011 à 16:10

Re,

Fhqed a écrit :

ils fonctionnent bien (sauf pour la dernière, mais elle peut-être faite en manuel).

Tiens c'est bizarre, chez moi ça marchait quand j'ai testé. As-tu bien rajouté le mot "Organisme" après la dernière ligne ?
Fhqed a écrit :

Sur le fichier réel, la modification de la plage faite, j'obtiens le message #NOM? dans toutes les cellules

Je ne sais pas d'où ça vient. La formule me semble bonne.
Le 12/08/2011 à 23:06

@vba-new

Pour la dernière séquence, pardon, j'avais oublié la toute première étape !

En ce qui concerne le problème #NOM?, je viens de tester le nombre maximum que ta formule permet de gérer dans mon fichier réel, et il était en fait de 1089 lignes.

Or à cette ligne, comme à tant d'autres, apparaît #NOM?

Il en restait une petite centaine éparpillés dans les 140k lignes (j'ai trouvé comment les corriger rapidement, et une fois retirés, plus d'erreur en appelant ta fonction.

Bravo ! Merci ! Et pardon de t'avoir peut-être fait douter de ta fonction, c'était bien à cause de mes données issues de l'import XML.

Par contre, le traitement sur autant de lignes est lent, je vais tester à nouveau la macro de @Papouclo, en espérant que c'est aussi ce qui la perturbait !

'vous tiens au courant dès que c'est réglé.

François

-- il y a moins d’une minute --

Ayant rencontré trop de difficultés, tant sur la formule (durée et plantage d'excel) que sur la macro que je n'ai pas réussi à appliquer, j'ai proposé ce sujet sur un autre forum, et poste ici la macro qui a résolu mon problème.

Je tiens à remercier encore Vba-new et Papouclo pour le temps qu'ils m'ont consacré !

Sub test2()

Dim rng As Range, r As Range, t As Long

With Range("a1", Range("a" & Rows.Count).End(xlUp)).Offset(​, 1)

.Formula = "=if(a1<>""Adhérent"",1,"""")"​

Set rng = .SpecialCells(-4123, 1)

End With

For Each r In rng.Areas

t = t + 1

With r.Resize(r.Rows.Count + 1).Offset(-1)

Sheets(2).Cells(1, t).Resize(.Rows.Count).Value = .Offset(, -1).Value

End With

Next

End Sub

Le 13/08/2011 à 07:42

Bonjour François,

Pour le fun, une solution plus rapide à base de variables tableau :

Sub ranger()
    Dim derlign As Long, i&, j&, repere&, col&, maxi&
    Dim tablo, temp

    derlign = range("a" & Rows.Count).End(xlUp).Row 'dernière ligne de la colonne A
    'on mets les données de la colonne A en variable
    'le traitement est plus rapide
    tablo = range("a1:a" & derlign).Value
    repere = 1: col = 1: maxi = 0

    Application.ScreenUpdating = False
    'boucle permettant de trouver le nombre de lignes maxi entre 2 formulaires
    For i = 1 To derlign
        If tablo(i, 1) = "Organisme" Or i = derlign Then maxi = IIf(i - repere > maxi, i - repere, maxi): repere = i
    Next i

    maxi = maxi + 1: repere = 1
    ReDim temp(1 To maxi, 1 To 1) 'création d'une variable tableau temporaire contenant chaque formulaire
    For i = 2 To derlign
        j = j + 1
        temp(j, 1) = tablo(i - 1, 1)
        If tablo(i, 1) = "Organisme" Or i = derlign Then
            If i = derlign Then temp(j + 1, 1) = tablo(i, 1)
            col = col + 1
            Sheets(2).Cells(1, col).Resize(i + 1 - repere) = temp 'on copie le tableau temporaire dans les colonne adéquates
            repere = i
            ReDim temp(1 To maxi, 1 To 1): j = 0
        End If
    Next i
End Sub
Rechercher des sujets similaires à "scinder colonnes sequence approximative"