XL 2016 Nombre Occurences

[Jean Jan]

Bonsoir a tous,
Pour vous cela ne seras qu'une formalité mais pour moi c'est un mystère...
Je souhaiterai faire par VBA connaître les nombre d'occurrences de ma feuille 1 et l'afficher sur ma feuille 2.
Pourquoi en VB car la feuille 1 est détruit puis reconstruit a chaque nouvelle importation de données !!

Merci d'avance.

 

[chris]

Bonjour

C'est la feuille 1 qui est détruite ou juste son contenu ?

 

[Jean Jan]

Bonjour Chris,
La feuille est détruite effectivement ..

 

[pierrejean]

Bonjour Jean Jan
Bises @chris

 

[Robert]

Bonjour le fil, bonjour le forum,

Pfff ! ils sont trop rapides !... Mais puisque c'est fait et commenté j'envoie quand même.

Sub Macro1()
Dim OS As Worksheet 'déclare la variable OS (onglet Source)
Dim OD As Worksheet 'déclare la variable OD (onglet Destination)
Dim TV As Variant 'déclare la variable TV (Tableau des Valeurs)
Dim D As Object 'déclare la variable D (Dictionnaire)
Dim I As Integer 'déclare la variable I (Incrément)

Set OS = Worksheets("Feuil1") 'définit l'onglet source OS
Set OD = Worksheets("Feuil2") 'définit l'onglet destination OD
TV = OS.Range("A21").CurrentRegion 'définit le tableau des valeurs TV
OD.Range("B3").CurrentRegion.ClearContents 'efface d'eventuelles anciennes données de l'onglet destination
Set D = CreateObject("Scripting.Dictionary") 'définit le dictionnaire D
For I = 2 To UBound(TV, 1) 'boucle sur toutes les lignes I du tableau des valeurs TV (en partant de la seconde)
  D(TV(I, 1)) = D(TV(I, 1)) + 1 'alimente le distionnaire D
Next I 'prochaine ligne de la boucle
OD.Range("B3").Resize(D.Count).Value = Application.Transpose(D.keys) 'renvoie à partir de B3 la liste des éléments du dictionnaire D sans doublons
OD.Range("D3").Resize(D.Count).Value = Application.Transpose(D.Items) 'renvoie à partir de D3 le nombre d'occurrences de chaque élément du dictionnaire D sans doublons
End Sub

 

[pierrejean]

Salut @Robert et Bravo !!!! (Impeccable et complet )

 

[job75]

Bonjour jean jan, chris, Pierre, Robert,

Sans VBA il suffit que la feuille détruite soit toujours reconstruite avec le même nom, "Import" par exemple.

Alors en Feuil2 formule en C3 =NB.SI(INDIRECT("Import!A:A");B3)

A+

 

[chris]

RE

Bise à pierrejean

Si le feuil1 est détruite, il faut mettre le bouton sur Feuil2, le code du bouton sur le module de la feuille 2 et le code du module 1 doit se référer à la feuille 1

Sub nb_occur()
With Worksheets("Feuil1")
    tablo = .Range("A22:A" & .Range("A" & .Rows.Count).End(xlUp).Row)
End With
    Set dico = CreateObject("Scripting.dictionary")
    For n = LBound(tablo, 1) To UBound(tablo, 1)
     x = tablo(n, 1)
     dico(x) = dico(x) + 1
    Next
    a = dico.keys
    b = dico.items
     Sheets("Feuil2").Range("A2:B" & Rows.Count).ClearContents
    ligne = 2
    For n = LBound(a) To UBound(a)
       Sheets("Feuil2").Range("A" & ligne) = a(n)
       Sheets("Feuil2").Range("B" & ligne) = b(n)
       ligne = ligne + 1
    Next
End Sub

Edit : coucou Robert. Je n'avais pas vu ton post

 

[chris]

RE

@job75
Bien vu le INDIRECT

 

[job75]

Si l'on veut lister les textes à rechercher entrer cette formule matricielle en Feuil2!B3 :

=SIERREUR(INDEX(INDIRECT("Import!A:A");PETITE.VALEUR(SI((INDIRECT("Import!A22:A10000")<>"")*NON(NB.SI(B$2:B2;INDIRECT("Import!A22:A10000")));LIGNE(INDIRECT("Import!A22:A10000")));1));"")

A valider par Ctrl+Maj+Entrée.

 

[Jean Jan]

Merci a Tous....
C'est super j'ai plus que souhaité
Bravo et encore merci...

 

[chris]

RE

Inspirée de la solution de job75 mais avec des formules nommées et un TCD qu'il suffit d'actualiser après reconstruction de la feuil1

 

[job75]

Re,

Les solutions proposées ne sont pas équivalentes en terme de rapidité.

Pour obtenir les 3 lignes de résultats :

- macros de pierrejean et chris => 21 millisecondes

- macro de Robert => 31 millisecondes

- formules de job75 post #10 => 72 millisecondes, les NB.SI prennent du temps...

Pas testé le TCD de chris mais c'est forcément très rapide.

A+

 

[chris]

RE

@job75 : tu mesures les timing via VBA ?
Et pour la mémoire que tu analyses parfois ?

 

[job75]

Re chris,

Pour les macros ce n'est pas compliqué : le Timer + au besoin une boucle de 1000.

Pour les formules il faut les rendre volatiles avec ALEA() + boucle de 1000 sur Calculate.

Pour la mémoire j'ai créé il y a longtemps un fichier .xls :

https://www.excel-downloads.com/threads/nombre-doctets-dune-formule-calcule-par-macro-vba.148601/

A+