Extraction d'un total en fonction de multiples codes dans multiples colonne

Bonsoir à tous,

Je vous contacte car je suis bloqué sur le traitement de données sur mon fichier Excel. Il s"agit d'une base de données publique qui identifie des organismes de formation sur le territoire national avec 15 colonnes identifiant un "code spécialité" à 3 chiffres par organisme, sachant qu'un même organisme peut avoir jusqu'à 15 codes spécialité répartis en colonne.

Je cherche à extraire donc le nombre total d'organisme pour les départements de la Région Nouvelle Aquitaine (16, 17, 19, 23, 24, 33, 40, 47, 64, 79, 86 et 87) contenant certains codes spécialité (les codes commençant uniquement par 23). Ma difficulté est de trouver une formule adaptée pour extraire rapidement cette information en tenant compte du n° de département et sans tenir compte de l'itération d'un même code spécialité commençant par 23 pour un même organisme. La base de données est conséquente car elle contient près de 100 000 organismes. Je vous joins une version allégée du fichier afin d'avoir un aperçu de la présentation des données.

Quelqu'un pourrait-il m'aider à trouver une solution adaptée ?

Je vous remercie par avance pour votre aide.

Sam

Salut Samplay,

premier jet...
Un double-clic sur la feuille t'invite à encoder le n° de département et le code ou début de code de la formation, séparés par un symbole slash "/".

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'
Dim tTab, iDep%, iTot%, sFor$, sMsg$, sRep$
'
Do
    sRep = Application.InputBox("Veuillez encoder le n° de département et l'amorce du code de la formation.", , "57/23", , , , , 2)
Loop Until InStr(sRep, "/") > 0
If IsNumeric(Split(sRep, "/")(0)) Then iDep = CInt(Split(sRep, "/")(0))
If IsNumeric(Split(sRep, "/")(1)) Then sFor = Split(sRep, "/")(1)
If iDep = 0 Or sFor = "" Then
    MsgBox "Données invalides !"
Else
    tTab = [A1].CurrentRegion.Value
    For x = 2 To UBound(tTab, 1)
        If CInt(tTab(x, 9)) = iDep Then
            For y = 17 To UBound(tTab, 2) Step 3
                If Trim(tTab(x, y)) <> "" Then _
                    If Left(tTab(x, y), Len(sFor)) = sFor Then _
                        sMsg = sMsg & tTab(x, 3) & "  -  " & tTab(x, 1) & Chr(10): _
                        iTot = iTot + 1: _
                        Exit For
            Next
        End If
    Next
End If
MsgBox IIf(sMsg <> "", "Total d'organismes : " & iTot & Chr(10) & Chr(10) & sMsg, "Pas de correspondance !"), vbInformation + vbOKOnly, "Samplay"
'
End Sub

A+

Bonsoir Curulis,

Je te remercie pour ta réponse rapide et cette proposition de macro.

J'avoue que le langage Visual basic est trop complexe au vu de mes connaissances et je pensais qu'une solution par formule Excel était possible.

Dans tous les cas, je teste demain et te dis si je m'en suis sorti.

Merci pour ton aide et bonne soirée

Sam

Bonsoir Curulis,

Je viens de tester la macro sur la version complète de la base de données en partant du fichier que tu m'as remis. J'ai juste coller les données manquantes de la base de données initiales dans le fichier en m'assurant que toutes les données à exploiter sont au même format.

J'ai lancé la macro en testant plusieurs fois les départements 16 et 33 et les codes 23 et 230 et j'ai une erreur "13" qui s'affiche au lancement de la macro à chaque fois. En cliquant sur Debogage, elle concerne la ligne de code "If CInt(tTab(x, 9)) = iDep Then".

Aurais-tu une idée de la source du problème ?

Bien à toi,

Samuel

Bonne question, camarade!

Sur la ligne d'erreur surlignée de jaune, quelle est la valeur de X ?
- vérifier la donnée en ligne X (exception, vide,...) ;
- ou me procurer un fichier contenant ces X lignes...

Que dire d'autre ?

À ta disposition...

A+

Je te remercie pour ton retour.

Ne maitrisant pas le VBA, j'ai tenté à défaut une approche scientifique dans l'utilisation de la macro en prenant comme échantillon des données dont je sais qu'au moins un résultat juste pouvait être extrait avant de répondre. Et j'ai constaté cette erreur de manière systématique. J'ai checké également le format des cellules afin de m'assurer qu'il n'y ait pas de format parasite dans les données à traiter comme le format texte par exemple. Par contre, je te confirme que la colonne Département n'est pas systématiquement renseignée mais j'ai supposé que les cellules vides ne devraient pas poser de souci.

Pour creuser et faire un test sur l'ensemble des données, le forum limite le volume du fichier téléchargeable mais le fichier complet peut être extrait en format CSV. Malheureusement, le forum ne me permet pas de poster un lien car pas assez de post à ce jour...

Il est sur le 1er site qui apparait suite à la recherche Google suivante : liste OF gouv

Google t'affiche l'intitulé "Liste Publique des Organismes de Formation". Une fois sur la page, le fichier de travail complet est le 1er fichier téléchargeable sous la banière "Liste OF à jour".

J'avais enregistré ensuite le fichier en format .xslx et ajouté la colonne "Département" absente du fichier initial. J'ai récupéré cette info de la colonne identifiant le code postal des organismes.

Après, j'ai pas de souci pour te transmettre le fichier complet d'une autre manière si tu préfères (il pèse près de 23 Mo). N'hésites pas à me dire quelle solution t'arrange.

Après, j'ai conscience que je suis pas vraiment d'une grande aide pour trouver une solution et que tu fais tout le boulot aussi si tu es ok, on peut échanger en mp et se contacter via un site en ligne (messenger, whatsapp...) pour communiquer de vive voix et travailler ensemble sur une solution.

Bonne journée

Sam

Salut Samplay,

voici ce que je te propose après avoir téléchargé le fichier renseigné.
- formule à coller en [I2] et à étirer : =SI(J2="";"";GAUCHE(J2;2))
- à l'invite des critères de recherche, tu peux négliger de renseigner le préfixe départemental mais, quand même, renseigner l'amorce du code de formation.
Ex : 92/320 ou /320 = minimum syndical !

Je vois que le CP de "Manosque" = 4100 !! C'est un CP "normal" en France ? La formule en [I:I] le codera comme département 41...

À savoir : les MsgBox ne peuvent accepter qu'un nombre limité de caractères. Donc, si tu veux un relevé exhaustif et détaillé des résultats, il faudra passer par un affichage dans une feuille 'Extract' : à toi de me dire...

Nouveau code (à coller en lieu et place du précédent) qui s'affranchit de l'absence de CP selon les conditions d'encodage dans l'InputBox décrites ci-dessus !

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'
Dim tTab, iDep%, iTot%, sFor$, sMsg$, sRep$
'
Do
    sRep = Application.InputBox("Veuillez encoder le n° de département et l'amorce du code de la formation.", , "57/23", , , , , 2)
Loop Until InStr(sRep, "/") > 0 Or sRep = "Faux"
If sRep <> "Faux" Then
    If IsNumeric(Split(sRep, "/")(0)) Then iDep = CInt(Split(sRep, "/")(0))
    If IsNumeric(Split(sRep, "/")(1)) Then sFor = Split(sRep, "/")(1)
    If sFor = "" Then
        MsgBox "Données invalides !"
    Else
        tTab = [A1].CurrentRegion.Value
        For x = 2 To UBound(tTab, 1)
            If tTab(x, 9) = "" Then tTab(x, 9) = 0
            If CInt(tTab(x, 9)) = iDep Or iDep = 0 Then
                For y = 17 To UBound(tTab, 2) Step 3
                    If Trim(tTab(x, y)) <> "" Then _
                        If Left(tTab(x, y), Len(sFor)) = sFor Then _
                            sMsg = sMsg & tTab(x, 3) & "  -  " & tTab(x, 1) & Chr(10): _
                            iTot = iTot + 1: _
                            Exit For
                Next
            End If
        Next
    End If
    MsgBox IIf(sMsg <> "", "Total d'organismes : " & iTot & Chr(10) & Chr(10) & sMsg, "Pas de correspondance !"), vbInformation + vbOKOnly, "Samplay"
End If
'
End Sub

- donc, si tu encodes un CP, recherche SSI la macro rencontre le CP ;
- pas de CP = recherche globale dans la BDD.

Enjoy !

A+

Bonsoir,

Je te remercie pour ton retour super complet. Je teste cela demain mais je pense qu'on touche le bout ;-)

Un grand merci pour ton aide et le temps consacré à développer cette macro.

Bonne soirée !

Excellement vôtre