XL 2010 Rechercher la fréquence de présence de séquences communes dans des chaines

[micheldu52]

Bonjour,

Je bute sur le cas de figure suivant :

Soit les 4 séquences suivantes (présentes par exemple dans les cellules de A1 à B4

1 ABCDE
2 BFE
3 AG
4 BH

Je cherche à ressortir 1 et 2 (dans cet exemple le contenu des cellules A1 et A2) car B1 et B2 contiennent respectivement l'information B et E, les lignes suivantes ne vérifiant pas cette condition

L'exemple ci dessus est simplifié, quasi simpliste, mais je généraliserai. Dans la réalité :

1) j'ignore que le point commun sera B et E (j'ignore même que B et E puissent exister, en tout cas la liste -que je pourrais établir automatiquement- est ouverte et évolutive avec un max de 26^4), je me pose la question de savoir s'il y a des points communs et lesquels, et je dois (dans cet exemple) ressortir que les lignes 1 et 2 contiennent à la fois l'info B et E
2) la taille de la chaine peut varier de 1 à (environ) 10000
3) les infos (ici ABCDEFGH...) ont, en réalité, une taille fixe 4 caractères encadrés par des < et > (exemple d'une chaine <@@AB><@CDE><ASDE>);Au besoin l'encadrement par < et > peut être modifié. (la taille 4 donne en réalité le maxi des codes possibles/ 26 puissance 4)
3) le nombre de lignes (ici 4) est de plusieurs milliers
4) ici j'ai recherché deux points communs, mais je dois pouvoir paramétrer le nombre de points communs recherché (minimum 2 mais pas vraiment de maximum)

Je sèche pour arriver à identifier la présence (dans l'exemple) de B et E avec des formules, et je ne maîtrise pas assez VBA pour le lui dire....

Help me please !

D'avance un grand merci !

Bien cordialement,

Michel

 

[sylvanu]

Bonjour Michel,
En PJ un essai avec ce que j'ai compris. Les séparateurs de champs sont < et >.
C'est assez long, on doit pouvoir accélérer si ça marche.
Avec 1000 lignes ça donne 3.2s sur mon vieux PC.
Avec :

Dim Liste(), i%, Ind, L, T0
Application.ScreenUpdating = False
T0 = Timer
DL = Sheets("Données").Range("A65500").End(xlUp).Row
Tablo = Sheets("Données").Range("A1:A" & DL)
Ind = 0
For i = 1 To UBound(Tablo)
    S = Split(Tablo(i, 1), ">")
    For j = 0 To UBound(S) - 1
        ReDim Preserve Liste(Ind)
        Liste(Ind) = Mid(S(j), 2)
        Ind = Ind + 1
    Next j
Next i
' Colle la matrice résultat
[A:B].ClearContents
Range("A1").Resize(UBound(Liste) + 1, 1).Value = Application.Transpose(Liste)
' Calcul les occurences
Taille = Range("A1000000").End(xlUp).Row
For L = 1 To Taille
    Cells(L, "B").FormulaR1C1 = Application.CountIf([A:A], Range("A" & L))
Next L
' Suppression des doublons
    Range("$A$1:$B$" & Taille).RemoveDuplicates Columns:=1, Header:=xlNo
' Tri par occurence décroissante
    Columns("A:B").Select
    ActiveWorkbook.Worksheets("Analyse").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Analyse").Sort.SortFields.Add Key:=Range("B1:B" & Taille) _
        , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Analyse").Sort
        .SetRange Range("A1:B" & Taille)
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
' Actualisation de la barre de défilement verticale et mise en forme
    [A1].Select
    ActiveSheet.UsedRange
    Range("A1:B1").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    [A1] = "Item": [B1] = "Occurence"
    Application.ScreenUpdating = True
    MsgBox "Temps d'execution : " & Round(Timer - T0, 3) & "s" & Chr(10) & _
            " Nombre de lignes analysées : " & DL & Chr(10) & _
            "Nombre d'items trouvés : " & Range("A65500").End(xlUp).Row
End Sub

 

[micheldu52]

Merci pour ton aide. Ton fichier fonctionne très bien pour un critère (perso, étant plus formuliste que vbiste, j'aurais utilisé =nb.si(Données!A:A;concatener("*";Analyse!A2;"*")) pour avoir le nombre d'occurrences.
Mais ma question est plutôt (cf 1) message initiale) de déterminer que, à la fois, les lignes 1 et 2 contiennent, à la fois, B et E (et il peut y avoir -par exemple- 14 lignes qui contiennent 4 critères identiques).
Je cherche donc à avoir, dans ces deux exemples quelque chose du type

occurrences communes trouvées dans les lignes

B E 1 2
A Z E R 11 22 23 24 44 55 57 58 111 1234 1235 1244 1266 1270

mais la "présentation" ci dessus ne doit pas être un carcan, ce qui m'intéresse c'est d'identifier d'une façon ou d'une autre les lignes (la liste) concernées en commun par une série (paramétrable en nombre) de critères communs (et si possible, mais ce serait un plus pas une obligation, lesquels)

J'espère être clair dans ma demande... sinon dis moi !

Bien cordialement,

Michel (et encore merci pour ton aide!)

 

[sylvanu]

J'ai bien peur qu'un petit fichier test représentatif soit nécessaire, et de préférence avec l'attendu sur quelques lignes pour mieux comprendre.

 

[micheldu52]

Voilà...
Je n'est développé que deux exemples (à la main.... et c'est bien là le cœur de mon problème le "à la main"!!!!)
si tu veux que je précises plus, hésites pas !

 

[micheldu52]

oups je n'ai!!! horrible comme erreur !

 

[micheldu52]

d'ailleurs en réfléchissant en F5 et F6 on pourrait mettre 7 et 12 ca va me servir ! (contient strictement et contient au moins)

 

[sylvanu]

Bonsoir,
J'étais effectivement loin de la demande.
Un nouvel essai "pour voir".
J'ai listé tous les items trouvés, les occurrences et les N° de lignes.

 

[micheldu52]

Je vais regarder plus avant ce soir (je dois partir) mais, a priori, c'est exactement cela....
Je te dis en toute fin de soirée.
un GRAND merci à toi!

 

[micheldu52]

Bonjour,
J'ai regardé de (très) prés, de nouveau grand merci.
Juste deux "détails" (mais si cela pose problème, avec ton résultat je peux rebondir et y arriver avec un peu de temps) qui serait "nickel plus" :

Ton comptage compte différemment (par exemple) les deux chaines suivantes

<1234><5678><9000>
et
<1234><9000><5678>
(je peux me débrouiller pour concatener les deux listes de lignes que tu me donnes si modifier cela pose problème, ne t'en fais pas)

Et ton comptage compte différemment les deux (autres) chaînes suivantes

<1111><2222> lignes 1 2 3
et
<1111><2222><3333> lignes 4 5
or la première est comprise dans la deuxième et j'aurai besoin de

<1111><2222> lignes 1 2 3 4 5
<1111><2222><3333> lignes 4 5

mais la aussi je peux rebondir sur ton travail et y arriver en me débrouillant

ce qui est sûr c'est que je vais arriver à mes fins grâce à toi et que demain je vais pouvoir opérationnaliser un beau travail au bureau!

Donc, une nouvelle fois MERCI

Bien cordialement,

Michel

 

[sylvanu]

Bonjour,
Une nouvelle version avec un indicateur de progression, j'ai vu qu'avec beaucoup de lignes ( ici 5000 ) ça rame.
Par contre je vous laisse vous débrouillez avec la demande du post précédent car je ne vois pas comment faire car vous pourriez avoir :
L1 : <1111><2222><3333>
L2 : <2222><3333><4444>
L3 : <2222><3333><4444><5555>
L4 : <6666><2222><3333>
L5 : <3333><4444><6666>
etc, etc ...
<1111><2222> est présent en L1,L2,L3,L4 mais <3333><4444> est présent en L2,L3,L5.
et ça à l'infini.
Ca devient une vrai usine.

 

[micheldu52]

Pas de problème ! merci
je vais (avec les vraies données) gérer "sans problème" (mais avec le temps de l'écrire..) la deuxième partie
<1111><2222> lignes 1 2 3 4 5
<1111><2222><3333> lignes 4 5
par contre la première partie
<1234><5678><9000>
et
<1234><9000><5678>
(items strictement identiques mais dans un autre ordre)
a rassembler dans le comptage
va me donner un peu plus de fil à retordre....
Je regarde cela en début d'aprés midi !

 

[sylvanu]

Bonjour Michel,
Je pense avoir compris (enfin) la problématique. Je croyais qu'à priori on ne connaissait pas les vecteurs à rechercher, donc je les recherchait tous, mais dans leur totalité.
Un dernier essai en PJ.
Les occurrences en colonne A sont à remplir suivant ce qu'on cherche. On met le min et le max. ( j'ai fixé le max à 7 mais c'est modifiable facilement )
J'utilise un mix XL/VBA en utilisant la feuille cachée Travail;
En espérant m'approcher du besoin.