Recherche de résultas mutiples

Question

Bonjour,

J'essaie de trouver une formule qui me permettrait d'identifier les différents résultats d'une recherche et selon ces résultats, rapporter une valeur(Texte) prédéterminée.

Par exemple: (Formule en col I dans mon échantillon ici-bas)
Si pour un même numéro de transit (col A) les valeurs en J pour les lignes qui suivent (lignes ayant en A la même valeur qu'en A2) sont "MLO" et "BOTH", la valeur rapportée en I devrait être "LOADED BOTH".

Mais il y a plus d'une possibilité:

- Les valeurs en J pourraient toutes être "MLO", alors la valeur rapportée en I devrait être "LOADED MLO"

- Les valeurs en J pourraient toutes être "WEL", alors la valeur rapportée en I devrait être "LOADED WEL"

- Les valeurs en J pourraient toutes être "BOTH", alors la valeur rapportée en I devrait être "LOADED BOTH"

- Et si il n'y a aucune autres lignes ayant en A la même valeur que A2, la valeur rapportée devrait être "BALLAST"

Voir lien:   https://www.cjoint.com/c/EByocjJJJ4Z

 Si je me suis mal expliquer, n'hésitez pas à me poser des questions.

Merci!

ccm81 · Answer

Bonjour

Un début de réponse via une macro
https://www.cjoint.com/?3Bypup2nzyX 

Cdlmnt

BILLING · Answer

Ok, maintenant j'ai réussi à faire fonctionner la fonction. 

Il y a une situation que la fonction ne gère pas. Il s'agit des cas où il n'y a qu'une seule ligne pour un numéro de transit. Dans ces cas, il faudrait que la fonction retourne "BALLAST".

ccm81 · Answer

Ajoutes la ligne soulignée au code
nbli = li2 - li1If nbli = 0 Then f = "BALLAST": Exit FunctionSet plageSec = ActiveSheet.Range(Cells(li1 + 1, coSec), Cells(li2, coSec))
Mais il doit rester des cas non traités

BILLING · Answer

En fait on y est presque. J'ai omis d'ajouter que lorsqu'il n'y a qu'une seule ligne avec un même numéro de transit, la fonction devrait retournée "BALLAST BOTH" ou "BALLAST MLO" ou "BALLAST WEL" selon la valeur en J pour cette ligne.

J'ai tenté de l'ajouter mais je ne m'y connais vraiment pas assez pour le faire.

ccm81 · Answer

Comme ça ?
If nbli = 0 Then f = "BALLAST " & Sec: Exit Function

ccm81 · Answer

Je ne sais pas si j'ai bien interprété ta demande
https://www.cjoint.com/?3BBpvBPkIMC  

Je pense que tu vas encore avoir des soucis avec cette fonction, on y verrait plus clair si tu détaillais de façon précise et complète en français tous les différents cas
Si Transit = 1 seule ligne alors f=BALLAST + Section
Si Tansit = "BOTH" alors ....

Cdlmnt

ccm81 · Answer

Je crois que ça devient plus clair

1. Si Transit = 1 seule ligne alors f=BALLAST + Section 
donne 
If nbli = 0 Then f = "BALLAST " & Section: Exit Function

2. Si Transit TOUTES les lignes suivantes = "BOTH" alors f= "LOADED BOTH"
Si Transit des lignes suivantes = "MLO" alors f= "LOADED MLO"
Si Transit des lignes suivantes = "WEL" alors f= "LOADED WEL"
donne
If nbli = b Then f = "LOADED BOTH": Exit Function
If nbli = m Then f = "LOADED MLO": Exit Function
If nbli = w Then f = "LOADED WEL": Exit Function

3. Si Transit des lignes suivantes = "MLO" et "WEL" alors f= "LOADED BOTH" 
donne
f nbli = m + w Then f = "LOADED BOTH": Exit Function

Toute la prodédure

Const coSection = "J"

Public Function f(cel As Range) As String
Dim li1 As Long, li2 As Long, li As Long, Transit As Long, coTransit As Long, nbli As Long
Dim plageSection As Range, Section As String, m As Long, b As Long, w As Long, nbSectionTransit As Long
li1 = cel.Row
Transit = cel.Value
coTransit = cel.Column
Section = ActiveSheet.Cells(li1, coSection)
If cel.Offset(-1, 0) = Transit Then f = "": Exit Function
li2 = li1
While ActiveSheet.Cells(li2, coTransit).Value = Transit
  li2 = li2 + 1
Wend
li2 = li2 - 1
nbli = li2 - li1
If nbli = 0 Then f = "BALLAST " & Section: Exit Function
Set plageSection = ActiveSheet.Range(Cells(li1 + 1, coSection), Cells(li2, coSection))
nbSectionTransit = Application.WorksheetFunction.CountIf(plageSection, Section)
If nbSectionTransit = 0 Then f = "BALLAST": Exit Function
b = Application.WorksheetFunction.CountIf(plageSection, "BOTH")
m = Application.WorksheetFunction.CountIf(plageSection, "MLO")
w = Application.WorksheetFunction.CountIf(plageSection, "WEL")
If nbli = b Then f = "LOADED BOTH": Exit Function
If nbli = m Then f = "LOADED MLO": Exit Function
If nbli = w Then f = "LOADED WEL": Exit Function
If nbli = m + w Then f = "LOADED BOTH": Exit Function
End Function
Cdlmnt

ccm81 · Answer

Tu n'as pas envisagé le cas où il y a des BOTH et autre chose
Donc je n'ai rien prévu !

ccm81 · Answer

En français décris moi ces cas

ccm81 · Answer

En résumé :
post #11
Si Transit = 1 seule ligne alors f=BALLAST + Section
Si Transit TOUTES les lignes suivantes = "BOTH" alors f= "LOADED BOTH"
Si Transit des lignes suivantes = "MLO" alors f= "LOADED MLO"
Si Transit des lignes suivantes = "WEL" alors f= "LOADED WEL"

Et dans tous les autres cas post #17
"LOADED BOTH"  ??

Si oui

Const coSection = "J"

Public Function f(cel As Range) As String
Dim li1 As Long, li2 As Long, li As Long, Transit As Long, coTransit As Long, nbli As Long
Dim plageSection As Range, Section As String, m As Long, b As Long, w As Long, nbSectionTransit As Long
li1 = cel.Row
Transit = cel.Value
coTransit = cel.Column
Section = ActiveSheet.Cells(li1, coSection)
If cel.Offset(-1, 0) = Transit Then f = "": Exit Function
li2 = li1
While ActiveSheet.Cells(li2, coTransit).Value = Transit
  li2 = li2 + 1
Wend
li2 = li2 - 1
nbli = li2 - li1
If nbli = 0 Then f = "BALLAST " & Section: Exit Function
Set plageSection = ActiveSheet.Range(Cells(li1 + 1, coSection), Cells(li2, coSection))
nbSectionTransit = Application.WorksheetFunction.CountIf(plageSection, Section)
b = Application.WorksheetFunction.CountIf(plageSection, "BOTH")
m = Application.WorksheetFunction.CountIf(plageSection, "MLO")
w = Application.WorksheetFunction.CountIf(plageSection, "WEL")
If nbli = b Then f = "LOADED BOTH": Exit Function
If nbli = m Then f = "LOADED MLO": Exit Function
If nbli = w Then f = "LOADED WEL": Exit Function
f = "LOADED BOTH": Exit Function
End Function
Bonne soirée

ccm81 · Answer

Je n'ai pas changé grand chose, mais peut être n'avais tu pas re-validé les formules
Avec cette version, tu n'auras plus besoin de le faire
https://www.cjoint.com/?3BCr7WOxNgm 

Y a t'il d'autres cas que ceux de mon fichier ?
Si oui, complètes-le avec les résultats attendus (et expliqués)

Cdlmnt

ccm81 · Answer

Il existe 3 autres possibilités de combinaison (que j'ai ajouté au fichier échantillon: 68249, 68300, 68000) mais la fonction telle qu'elle est semble bien les gérer. 
La dernière ligne  traite les cas non envisagés plus haut dans le code, dont ces trois là font partie
If nbli = w Then f = "LOADED WEL": Exit Functionf = "LOADED BOTH": Exit FunctionEnd Function

Cdlmnt

ccm81 · Answer

1. Que veux tu dire par 
si j'ai 2 classeurs ouverts en même temps, et que ces 2 classeurs ont cette fonction, un affecte l'autre
Peut être essayer en ouvrant tes deux classeurs dans deux sessions différentes d'excel
2. Peux tu me donner un ordre de grandeur du nombre de lignes des tes classeurs

ccm81 · Answer

1. as tu essayé d'ouvrir tes deux classeurs sur deux sessions différentes d'excel comme demandé plus heut
Ouvrir le premier classeur
Depuis le bureau, lancer excel et ouvrir le deuxième classeur
Dans le menu Fenetre tu ne dois voir qu'un seul classeur

2. Est-ce possible de transformer cette en fonction en macro reliée au classeur spécifiquement?
Oui, c'est pour ça que je t'ai demandé le nombre de lignes

3. Au passage j'ai besoin d'une précision
dans la colonne I (le résultat)
il y a par exemple 4 transit n° 67890, plage A2:A5, 
le resultat
I2=LOADED MLO
I3, I4, I5 = vide 
comme calculé par la fonction f de la macro
c'est bien ce que tu veux ? (1 seul résultat par transit sur la première ligne)

Tu dis

ccm81 · Answer

A tester
https://www.cjoint.com/?3CfuBF8MwsE 
RQ. La colonne J ne comportant plus que des valeurs sur les premières lignes des transit, le fichier devrait être plus léger. Tu me dis ce que ça donne au niveau du temps d'exécution

Cdlmnt

ccm81 · Answer

Ajoutes la ligne soulignée qui nettoie la colonne "résultat" avant de la recalculer (en cas de modification du fichier çà évitera les scories)

With ActiveSheet  lifin = .Range(coTra & Rows.Count).End(xlUp).Row  .Range(coVal & lideb & ":" & coVal & lifin).ClearContents
Cdlmnt

ccm81 · Answer

Cette phrase est surlignée en jaune lorsque je fais le débogage: « Transit = .Range(coTra & li1).Value »
1. Sur mon fichier ?
2. Lorsque tu es sur la ligne surlignée en jaune, mets la souris sur les variables coTra et li21
tu dois avoir coTra = A et li1 = 2
3. La variable transit est déclarée comme entier long (c'était bien des nombres en colonne A dans ton fichier), si chez toi c'est une chaine de caractères (du texte) , normal que ça plante. Si c'est ça, déclares là comme string (Tansit as string)
4. La colonne Transit est bien la colonne A ?

5. Chez moi, je n'ai pas cette erreur, je t'envoie la dernière version complétée, tu commences par essayer celle là et tu dis
https://www.cjoint.com/?3CgojnuSpHg

ccm81 · Answer

3. La variable transit est déclarée comme entier long (c'était bien des nombres en colonne A dans ton fichier), si chez toi c'est une chaine de caractères (du texte) , normal que ça plante. C'était format «Standard», pas «Text». Je l'ai maintenant changé au format «Nombre». 
Et qu'est ce que ça donne lors de l'exécution ?
RQ. Ce n'est pas en changeant le format de la colonne transit que tu vas changer la nature des objets qu'elle contient
La question : est ce que ce sont des nombres ou du texte
pour savoir, tu mets en format standard si ça se cale à droite ce sont des nombres, si ça se cale à gauche c'est du texte
La macro peut très bien fonctionner avec n'importe lequel des deux types, il suffit de le lui dire dans les déclarations
Dim Transit as long pour un entier, Dim Transit as string pour du texte
RQ. tu as aussi Dim Transit (sans préciser le type, donc type Variant pour excel, mais c'est moins propre)

2. Lorsque tu es sur la ligne surlignée en jaune, mets la souris sur les variables coTra et li21 :: Voulais-tu dire coTra et li1 ? 
Si oui, j'ai coTra = "A" et li1 = 2 
ça c'est correct, si du moins en colonne A tu as les transit et que la première ligne des données est 2

5. la colonne où se passe la macro est "AT". 
que veux tu dire par là ? 
que le résultat de la macro est en colonne AT ?
Le fichier échantillon devait être allégé alors beaucoup de colonnes et lignes ont été éliminées. Est-ce que ceci peut être la cause du problème? 
Oui voir ci-dessous
Est-ce que l'emplacement des résultats de la macro est considéré outre dans
What ?
les colonnes de travail de la macro sont définies dans les constantes en début de code, ça permet justement d'éviter de toucher au code de la procédure en cas de changement de configuration dans la feuille 
Const coTra = "A"    ' colonne TransitConst coSec = "J"    ' colonne SectionConst coVal = "I"    ' colonne Valeur rapportéeConst lideb = 2      ' première ligne des donnéesà toi de modifier les valeurs des constantes selon ta configuration

ccm81 · Answer

2. En analysant ta macro, j'ai compris que je devais changer «Const lideb = 2» à «Const lideb = 3» car le premier transit est sur la ligne 3 dans mon fichier.
Ceci étant dit, et-ce que ce changement engendre d'autres changements dans la macro? 
en principe non

6.1. Mais les résultats ne sont pas exacts. Les seuls résultats apparaissant sont :
"BALLAST BOTH"
"BALLAST MLO"
"BALLAST WEL"
"LOADED BOTH" 
Là je ne vois pas, sur mon exemple j'ai tout ce qu'il faut

6. 2. De plus, le tout dernier transit en ligne 7393 n'a pas de résultat, comme si la macro s'était arrêtée avant 7392.
<= au lieu de <      ici
 While li2 <= lifin

Peux tu m'envoyer un bout de ton fichier au format excel 2003 avec tous les cas possibles

ccm81 · Answer

Si tu ne veux pas que ton envoi soit public, tu peux envoyer ton fichier via la messagerie personnelle
Tu fais un double-clic sur mon peudo et tu as "Lui envoyer un message"

ccm81 · Answer

La maladie n'était pas très grave
Dans la colonne Section (devenue I), les valeurs WEL et MLO étaient suivies d'un espace c'est ce qui a entrainé les résultats erronés.
J'ai donc "nettoyé" cette colonne des espaces en trop avant de lancer la macro
Temps mis, 1,2 s sur ma vieille machine
https://www.cjoint.com/?3ChkvJFJD2V  

RQ1. J'ai vérifié les 100 premières lignes, elles sont correctes
RQ. Si les espaces doivent être conservés, tu dis, il faudra modifier le code

Bon week end

Recherche de résultas mutiples - Page 2

Discussions similaires

Newsletters