RECHERCHEV sur plusieurs valeurs dans une même cellule
Résolu/Fermé
BIRDYFROM85
Messages postés
15
Date d'inscription
jeudi 17 janvier 2013
Statut
Membre
Dernière intervention
19 juin 2013
-
17 juin 2013 à 12:19
miyhun - 1 nov. 2015 à 17:37
miyhun - 1 nov. 2015 à 17:37
A voir également:
- Plusieurs recherchev dans une cellule
- Aller à la ligne dans une cellule excel - Guide
- Verrouiller cellule excel - Guide
- Formule excel pour additionner plusieurs cellules - Guide
- Excel cellule couleur si condition texte - Guide
- Si une cellule contient un mot alors ✓ - Forum Excel
7 réponses
Mike-31
Messages postés
18313
Date d'inscription
dimanche 17 février 2008
Statut
Contributeur
Dernière intervention
21 avril 2024
5 073
Modifié par Mike-31 le 17/06/2013 à 12:38
Modifié par Mike-31 le 17/06/2013 à 12:38
Salut,
As tu une base de données détaillée qui identifie le client par rapport à un code exemple en A1 666 et en B1 bidule, en A2 667 et en B2 truc etc
et combien de valeurs séparées par un tiret dans une cellule
A+
Mike-31
Une période d'échec est un moment rêvé pour semer les graines du savoir.
As tu une base de données détaillée qui identifie le client par rapport à un code exemple en A1 666 et en B1 bidule, en A2 667 et en B2 truc etc
et combien de valeurs séparées par un tiret dans une cellule
A+
Mike-31
Une période d'échec est un moment rêvé pour semer les graines du savoir.
Mike-31
Messages postés
18313
Date d'inscription
dimanche 17 février 2008
Statut
Contributeur
Dernière intervention
21 avril 2024
5 073
17 juin 2013 à 13:55
17 juin 2013 à 13:55
Re,
tu as 16 valeurs dans ta cellule séparées par des tirets, c'est cela
tu as 16 valeurs dans ta cellule séparées par des tirets, c'est cela
BIRDYFROM85
Messages postés
15
Date d'inscription
jeudi 17 janvier 2013
Statut
Membre
Dernière intervention
19 juin 2013
17 juin 2013 à 13:57
17 juin 2013 à 13:57
oui c'est bien cela, jusqu'à 16 nombres séparés par des tirets
Mike-31
Messages postés
18313
Date d'inscription
dimanche 17 février 2008
Statut
Contributeur
Dernière intervention
21 avril 2024
5 073
17 juin 2013 à 14:12
17 juin 2013 à 14:12
Re,
tu vas avoir des formules usines à gaz et très lentes, pourquoi ne pas utiliser la fonction convertir pour séparer tes données, ensuite les transposer avec une rechercheV pour chaque valeur
exemple tu as tes valeurs de A1 à A50, tu mets la plage en surbrillance et onglet Données/Outils de Données/Convertir
coche délimité/dans autre tu saisis le tiret/suivant et OK
tes données séparées, il suffit de les sélectionner/Copier/Collage Spécial/cocher transposer et OK
en face chaque données tu utilise une RECHERCHEV
ou même sans les transposer, si tu as tes données séparées ligne 2 entre B2 et X2, au dessous tu peux utiliser directement une recherche
tu vas avoir des formules usines à gaz et très lentes, pourquoi ne pas utiliser la fonction convertir pour séparer tes données, ensuite les transposer avec une rechercheV pour chaque valeur
exemple tu as tes valeurs de A1 à A50, tu mets la plage en surbrillance et onglet Données/Outils de Données/Convertir
coche délimité/dans autre tu saisis le tiret/suivant et OK
tes données séparées, il suffit de les sélectionner/Copier/Collage Spécial/cocher transposer et OK
en face chaque données tu utilise une RECHERCHEV
ou même sans les transposer, si tu as tes données séparées ligne 2 entre B2 et X2, au dessous tu peux utiliser directement une recherche
BIRDYFROM85
Messages postés
15
Date d'inscription
jeudi 17 janvier 2013
Statut
Membre
Dernière intervention
19 juin 2013
17 juin 2013 à 14:29
17 juin 2013 à 14:29
OK je vais essayer ça !
merci bcp et au plaisir
merci bcp et au plaisir
Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre question
Frenchie83
Messages postés
2240
Date d'inscription
lundi 6 mai 2013
Statut
Membre
Dernière intervention
11 août 2023
337
17 juin 2013 à 16:06
17 juin 2013 à 16:06
Bonjour
une autre façon mettant en oeuvre une fonction maison
https://www.cjoint.com/?CFrp7SviHvG
une feuille nommée "Liste" contenant la liste des numéros et des clients
une feuille extraction ou sont extraient les noms par formule
taper en B2: =Extract(A2) , tirez la formule vers le bas.
à essayer
cdlt
une autre façon mettant en oeuvre une fonction maison
https://www.cjoint.com/?CFrp7SviHvG
une feuille nommée "Liste" contenant la liste des numéros et des clients
une feuille extraction ou sont extraient les noms par formule
taper en B2: =Extract(A2) , tirez la formule vers le bas.
à essayer
cdlt
BIRDYFROM85
Messages postés
15
Date d'inscription
jeudi 17 janvier 2013
Statut
Membre
Dernière intervention
19 juin 2013
18 juin 2013 à 11:36
18 juin 2013 à 11:36
bonjour Frenchie83,
cà me parait effectivement très fonctionnel, ça marche comment, avec une macro ?
comment se fait le lien entre ta feuille "extraction" et ta feuille "liste" ?
merci de tes explications
bien cdt,
cà me parait effectivement très fonctionnel, ça marche comment, avec une macro ?
comment se fait le lien entre ta feuille "extraction" et ta feuille "liste" ?
merci de tes explications
bien cdt,
Frenchie83
Messages postés
2240
Date d'inscription
lundi 6 mai 2013
Statut
Membre
Dernière intervention
11 août 2023
337
18 juin 2013 à 13:19
18 juin 2013 à 13:19
Bonjour BIRDYFROM85
Ce n'est pas une macro mais une fonction (macro qui permet de créer sa propre formule), elle se trouve dans le module 1
Le principe: on compte le nombre de tiret: NBTiret = UBound(Split(Sel, "-", , 1))
on isole les 3 caractères qui suivent chaque tiret (on les mémorise).
pour chaque tronçon isolé, on recherche dans la feuille liste le nom situé dans la colonne B correspondant à chaque numéro (on les mémorise).
on reconstruit le tout en insérant un tiret entre chaque nom.
à la fin on supprime le premier tiret à gauche qui a été ajouté lors de la mémorisation des noms.
Comme la fonction s'appelle Extract et que le paramètre est le contenu de la cellule A de la même ligne, la formule est donc : =Extract(A2) pour la ligne 2,=Extract(A3) pour la ligne 3, etc..
Ce n'est pas une macro mais une fonction (macro qui permet de créer sa propre formule), elle se trouve dans le module 1
Le principe: on compte le nombre de tiret: NBTiret = UBound(Split(Sel, "-", , 1))
on isole les 3 caractères qui suivent chaque tiret (on les mémorise).
pour chaque tronçon isolé, on recherche dans la feuille liste le nom situé dans la colonne B correspondant à chaque numéro (on les mémorise).
on reconstruit le tout en insérant un tiret entre chaque nom.
à la fin on supprime le premier tiret à gauche qui a été ajouté lors de la mémorisation des noms.
Comme la fonction s'appelle Extract et que le paramètre est le contenu de la cellule A de la même ligne, la formule est donc : =Extract(A2) pour la ligne 2,=Extract(A3) pour la ligne 3, etc..
BIRDYFROM85
Messages postés
15
Date d'inscription
jeudi 17 janvier 2013
Statut
Membre
Dernière intervention
19 juin 2013
18 juin 2013 à 14:16
18 juin 2013 à 14:16
OK mon souci c'est que le nombre de tiret est tout à fait aléatoire et variable (maximum 15)...
je ne sais pas, du coup, si c'est transposable ligne à ligne
merci quand même pour l'astuce, du coup j'ai appliqué l'idée transmise par Mike et ça a très bien fonctionné
au plaisir
je ne sais pas, du coup, si c'est transposable ligne à ligne
merci quand même pour l'astuce, du coup j'ai appliqué l'idée transmise par Mike et ça a très bien fonctionné
au plaisir
Frenchie83
Messages postés
2240
Date d'inscription
lundi 6 mai 2013
Statut
Membre
Dernière intervention
11 août 2023
337
18 juin 2013 à 14:22
18 juin 2013 à 14:22
aucun souci concernant le nombre de tirets puisque celui est recalculé pour chaque cellule, donc ça peut aller bien au-delà de 15
cdlt
cdlt
BIRDYFROM85
Messages postés
15
Date d'inscription
jeudi 17 janvier 2013
Statut
Membre
Dernière intervention
19 juin 2013
19 juin 2013 à 13:59
19 juin 2013 à 13:59
Slt
pour un autre tableau plus complexe, ta formule peut m'intéresser
pourrais-tu stp m'expliquer la démarche pas-à-pas notamment comment fait-on pour mémoriser les caractères et comment on les associe ensuite...
merci d'avance
bien cdt,
pour un autre tableau plus complexe, ta formule peut m'intéresser
pourrais-tu stp m'expliquer la démarche pas-à-pas notamment comment fait-on pour mémoriser les caractères et comment on les associe ensuite...
merci d'avance
bien cdt,
Frenchie83
Messages postés
2240
Date d'inscription
lundi 6 mai 2013
Statut
Membre
Dernière intervention
11 août 2023
337
19 juin 2013 à 19:23
19 juin 2013 à 19:23
Bonjour
on va décomposer tout ça
Function Extract(Sel As Range) "sel pour la cellule à tester"
If Len(Sel) = 0 Then Exit Function " si le nombre de caractères de la cellule à tester est vide on sort du programme"
NBTiret = UBound(Split(Sel, "-", , 1)) "on compte le nombre de tirets"
ReDim Extraction(Len(Sel)) As String "on crée une variable nommée Extraction à laquelle on attribuera un numéro qui sera incrémenté dans une boucle avec un maximum égal au nombre de caractères, l'incrémentation se fera par pas de 4 (nombre de chiffres par code client à extraire + le tiret)"
ReDim Nom(Len(Sel)) As String "on crée une variable nommée Nom à laquelle on attribuera un numéro qui sera incrémenté dans une boucle avec un maximum égal au nombre de caractères"
A partir de là, dans cette boucle, la valeur i va prendre la valeur 1 au premier tour, puis 5, puis 9 ainsi de suite jusqu'à atteindre le nombre de caractères de la cellule à tester
donc la variable Extraction va s'appeler Extraction1, puis Extraction5 , etc..
For i = 1 To Len(Sel) Step 4
Extraction(i) = Mid(Sel, i, 3)
next
"mid: =chaine de caractères à extraire dans la cellule à tester"
"Sel ;= cellule à tester"
" i:=valeur de i à chaque tour en commençant par 1, puis 5, puis 9, etc", cette valeur représente la position du premier caractère de chaque numéro de clients
" 3: = nombre de caractères à retenir" à chaque passage
exemple; cellule=666-667-668
nb de caract=10
nb de tirets =2
extraction de chaque numéro
premier tour:
i=1
Extraction1=on isole les 3 caractères à partir du premier caractère= 666
Deuxième tour:
i=1+4 =5
Extraction1=on isole les 3 caractères à partir du 5ème caractère= 667
Troisième tour:
i=5+4 =9
Extraction1=on isole les 3 caractères à partir du 9ème caractère= 668
Quatrième tour
i =9+4 =13
i est supérieur au nombre de caractères de la cellule, on passe à la suite
maintenant, on va associer chaque Extraction faite auparavant à un client,
d'où l'intérêt d'avoir une liste de code associée a chaque client
en reprenant le principe de la boucle précédente, on va chercher dans la feuille liste, colonne A
le numéro correspondant à l'extraction(i)
For i = 1 To Len(Sel) Step 4
Set c = Sheets("Liste").Columns(1).Find(Extraction(i), LookIn:=xlValues)
If c Is Nothing Then " si pas de correspondance, le nom sera marqué "inconnu"
Nom(i) = "inconnu"
GoTo Suite et on passe au code suivant
End If
Nom(i) = c.Offset(0, 1).Value " sinon la variable Nom(associée à la valeur de i) prendra le nom situé en colonne B et sur la même ligne que le code client"
Suite:
Extract = Extract & "-" & Nom(i)
avant le premier passage Extract était égal rien ,
donc après le premier passage Extract = -TOTO,
au passage suivant, Extract= Extract & "-" & Nom(i), soit "-TOTO-TITI"
ainsi de suite
Next i
Extract = Mid(Extract, 2, 1000)
ici on enlève le premier tiret, donc dans la valeur Extract, à partir du 2ème caractère, on prend les 1000 caractères suivants, ce qui couvre une grande quantité de code rentrés
End Function
j'ai essayé d'être le plus clair possible, j'espère avoir répondu à tes questions
Cordialement
on va décomposer tout ça
Function Extract(Sel As Range) "sel pour la cellule à tester"
If Len(Sel) = 0 Then Exit Function " si le nombre de caractères de la cellule à tester est vide on sort du programme"
NBTiret = UBound(Split(Sel, "-", , 1)) "on compte le nombre de tirets"
ReDim Extraction(Len(Sel)) As String "on crée une variable nommée Extraction à laquelle on attribuera un numéro qui sera incrémenté dans une boucle avec un maximum égal au nombre de caractères, l'incrémentation se fera par pas de 4 (nombre de chiffres par code client à extraire + le tiret)"
ReDim Nom(Len(Sel)) As String "on crée une variable nommée Nom à laquelle on attribuera un numéro qui sera incrémenté dans une boucle avec un maximum égal au nombre de caractères"
A partir de là, dans cette boucle, la valeur i va prendre la valeur 1 au premier tour, puis 5, puis 9 ainsi de suite jusqu'à atteindre le nombre de caractères de la cellule à tester
donc la variable Extraction va s'appeler Extraction1, puis Extraction5 , etc..
For i = 1 To Len(Sel) Step 4
Extraction(i) = Mid(Sel, i, 3)
next
"mid: =chaine de caractères à extraire dans la cellule à tester"
"Sel ;= cellule à tester"
" i:=valeur de i à chaque tour en commençant par 1, puis 5, puis 9, etc", cette valeur représente la position du premier caractère de chaque numéro de clients
" 3: = nombre de caractères à retenir" à chaque passage
exemple; cellule=666-667-668
nb de caract=10
nb de tirets =2
extraction de chaque numéro
premier tour:
i=1
Extraction1=on isole les 3 caractères à partir du premier caractère= 666
Deuxième tour:
i=1+4 =5
Extraction1=on isole les 3 caractères à partir du 5ème caractère= 667
Troisième tour:
i=5+4 =9
Extraction1=on isole les 3 caractères à partir du 9ème caractère= 668
Quatrième tour
i =9+4 =13
i est supérieur au nombre de caractères de la cellule, on passe à la suite
maintenant, on va associer chaque Extraction faite auparavant à un client,
d'où l'intérêt d'avoir une liste de code associée a chaque client
en reprenant le principe de la boucle précédente, on va chercher dans la feuille liste, colonne A
le numéro correspondant à l'extraction(i)
For i = 1 To Len(Sel) Step 4
Set c = Sheets("Liste").Columns(1).Find(Extraction(i), LookIn:=xlValues)
If c Is Nothing Then " si pas de correspondance, le nom sera marqué "inconnu"
Nom(i) = "inconnu"
GoTo Suite et on passe au code suivant
End If
Nom(i) = c.Offset(0, 1).Value " sinon la variable Nom(associée à la valeur de i) prendra le nom situé en colonne B et sur la même ligne que le code client"
Suite:
Extract = Extract & "-" & Nom(i)
avant le premier passage Extract était égal rien ,
donc après le premier passage Extract = -TOTO,
au passage suivant, Extract= Extract & "-" & Nom(i), soit "-TOTO-TITI"
ainsi de suite
Next i
Extract = Mid(Extract, 2, 1000)
ici on enlève le premier tiret, donc dans la valeur Extract, à partir du 2ème caractère, on prend les 1000 caractères suivants, ce qui couvre une grande quantité de code rentrés
End Function
j'ai essayé d'être le plus clair possible, j'espère avoir répondu à tes questions
Cordialement
miyhun
>
Frenchie83
Messages postés
2240
Date d'inscription
lundi 6 mai 2013
Statut
Membre
Dernière intervention
11 août 2023
1 nov. 2015 à 17:37
1 nov. 2015 à 17:37
Bonjour Frenchie,
Je déterre un peu le sujet :D
J'ai pas essayé la formule mais j'ai essayé de comprendre ton raisonnement. Une question me taraude: Elle est valable uniquement pour un code client égale à 3 caractères? Moi ça m'intéresserait mais avec une longueur de code client variable entre 1 et 5 caractères.... C'est pas possible? Ton fichier joint n'existe plus :(
Je déterre un peu le sujet :D
J'ai pas essayé la formule mais j'ai essayé de comprendre ton raisonnement. Une question me taraude: Elle est valable uniquement pour un code client égale à 3 caractères? Moi ça m'intéresserait mais avec une longueur de code client variable entre 1 et 5 caractères.... C'est pas possible? Ton fichier joint n'existe plus :(
17 juin 2013 à 13:51
oui j'ai cette base et le nombre de valeurs est différent selon les cellules, ça varie de 1 seule (ça marche avec recherchev jusqu'à 16 valeurs maxi
merci d'avance de ton aide