Discussion :

[LG-69]

Bonjour,

Je ne suis pas très à l'aise avec les chaînes de caractères, et encore moins quand il s'agit de passer par des macros et je pense ne pas y échapper.

Mon problème :

J'ai en cellule A1 une référence : Référence1
J'ai en cellule B1 une liste de nombre séparé a priori par un espace (cellule saisie à la main) : 1245 1243 1542 7859 4123 4521 7510

Je ne connais pas le nombre de nombres (dans mon exemple il y en a 7 mais il peut ne pas y en avoir ou y en avoir plus ou moins)

Mon besoin est de reformater tout ça pour avoir un tableau :
colonne D colonne E
Référence1 1245
Référence1 1243
Référence1 1542
.... et ainsi de suite

a priori les nombres sont toujours de 4 chiffres
Bien sûr ma ligne à analyser va se répéter avec une référence2, une référence3 et ainsi de suite...
L'idée est donc d'obtenir un tableau avec dans la première colonne les références (répétées autant de fois que nécessaire) et en deuxième colonne un seul nombre à chaque référence.

Merci d'avance pour vos lumières sur ce sujet.

[Marc-L]

Bonjour,

ici ce n'est pas du tout le bon forum, celui dédié au VBA Excel où il y a deux fois plus de passage soit dit en passant,
voir dans l'aide VBA la méthode TextToColumns pour séparer les données en colonnes ou encore la fonction Split …

__________________________________________________________________________________________________
Tous unis, tous Charlie

[Kimy_Ire]

Bonjour LG-69,

A mon avis, tu peux t'en sortir facilement avec des opérations manuelles.

1. Tout d'abord, sélectionne ta colonne B.
2. Va dans l'onglet "Données" > "Convertir"
3. Tu sélectionnes "Délimité" > et tu coches "Espace"
4. Tu cliques sur "Terminer"

Déjà, tu as en ligne, l'ensemble de tes données.
Si plusieurs espaces sont présents, tu peux également utiliser la fonction =SUPPRESPACE.

Déjà, ça pourrait répondre à une première problématique.

Cependant, si ce n'est pas suffisant, les macros sont bien entendu relativement facile à faire pour ton cas de figure.

J'attends déjà ton retour !

Cordialement,
Kimy

EDIT : Désolé Marc. En même temps.

[Menhir]

Tu peux peut-être procéder en 2 temps : D'abord séparer les nombres de tes cellules ensuite mettre les cellules obtenues en ordre.

Tape en C1 la formule suivante :

=SIERREUR(STXT($B1;colonne(C1)*5-4;4);"")

Tu recopies cette cellule vers la droite autant de fois que tu as de nombres dans la cellule B1 et vers le bas autant que tu as de lignes.

Une fois que c'est fini et que tu as bien vérifié que le résultat était celui que tu souhaitais, tu fais sur ces valeur un gros copier/coller valeur, histoire de stabiliser tout ça.

Il ne te reste plus que l'autre moitié du problème : mettre en vertical.

[Pierre Fauconnier]

Salut.

Je procèderais en deux temps:
D'abord, comme expliqué par Kimy_Ire, tu splites les données de B en plusieurs colonnes via Données, Convertir...
Puis, avec une deux formules DECALER(), tu réorganises ton tableau en deux colonnes REF/VAL...

Si tu dois automatiser, tu fais exactement la même chose, mais en VBA... Cela t'évite les boucles et la programmation lourde. La seule contrainte sera de déterminer le nombre de colonnes créées lors du splitage pour utiliser les bons paramètres dans la fonction DECALER

[LG-69]

Merci à tous pour vos pistes qui me semblent très intéressantes, j'ai commencé à tester tout ça, je ne suis pas encore au bout, ça va me prendre un peu de temps je pense.
Je vous ferai un retour dès que c'est abouti (ou avant si j'ai d'autres questions)

[LG-69]

J'ai avancé :

1 - Découpage du texte de la cellule :

J'ai utilisé les 2 méthodes :
- La formule de Menhir légèrement adaptée avec la formule de Kimy pour supprimer les double-espaces (oui il y en a...) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SIERREUR(STXT(SUPPRESPACE($B1);COLONNE(C1)*5-4;4);"")

- La méthode de Kimy (en ayant au préalable supprimé les double-espace quand il y en a sinon ça me provoque des décalages)

Avec ces deux méthodes j'obtiens le tableau en horizontal

Référence1 1245 1243 1542 ....
Référence2 .... ..... .... ....

2 - mise en forme du tableau résultant :

Reste à le mettre en vertical sauf si....

Y-a-t-il moyen de rechercher une valeur dans une Matrice (on va dire : C1:Z100) et que ça me retourne le numéro de ligne ? une sorte de RECHERCHEVH() ou un EQUIV() mais dans une matrice, pas un vecteur...

Si oui pas besoin de modifier ma matrice pour la mettre en vertical.
Parce que la finalité de mon opération c'est de rechercher mon nombre dans cette matrice et de récupérer la référence correspondante.

Si non il faut transformer la matrice mais pour le moment je ne vois pas comment le faire de façon quasi automatique sans macro.

Remarque : pour trouver le nombre de nombres dans ma cellule j'ai la formule ci-dessous qui fonctionne (elle supprime les espaces superflus, compte le nombre de caractère total puis retranche le nombre de caractère sans les espaces et ajoute 1 si j'ai quelque chose dans la cellule uniquement (en gros si j'ai du texte sans espace)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=NBCAR(SUPPRESPACE($B1))-NBCAR(SUBSTITUE(SUPPRESPACE($B1);" ";""))+SI(NBCAR(SUBSTITUE(SUPPRESPACE($B1);" ";""));1;0)

[Kimy_Ire]

LG-69,

Je ne visualise pas encore très bien ton fichier et ne connait pas le fonctionnel. Cependant, si tu cherches une valeur qui peut apparaître dans différentes lignes, il me semble que, sans macro, tu sois obligés de faire une recherche ligne par ligne.
La fonction EQUIV te permet de récupérer, par exemple la colonne.
Ainsi, avec =EQUIV(1500;H1:K1;0)+COLONNE(H1)-1 tu récupères la fameuse colonne où tu trouveras "1500".

Or, si tu as plusieurs fois 1500 dans cette ligne ça ne fonctionnera qu'à moitié...
L'autre inconvénient, c'est qu'il faut donc qui tu appliques cette formules à l'ensemble des tes lignes.

Mais, si cela te convient, tu peux faire un filtre sur les valeurs différentes de #N/A.
Tu peux même rajouter un =SIERREUR(EQUIV(1500;H1:K1;0)+COLONNE(H1)-1; "") si jamais, tu ne veux pas voir de #N/A.
Ainsi, après voir filtré tes valeurs, tu ne récupères plus que les lignes sur lesquelles ta référence est présente.

Ne reste plus qu'à la paramétrer dans une cellule =SIERREUR(EQUIV($O$1;H1:K1;0)+COLONNE(H1)-1; "") et en O1, tu changes la valeur que tu cherches.
On peut, par un événement, automatiquement filtrer tes valeurs lorsque tu changes la cellule en question (ici O1). Comme ça, tu ne visualises que les valeurs qui t'intéressent, tout le temps.

En revanche, si un des points que je viens de présenter pose soucis, je pense que tu peux partir sur une macro, dès le départ.

Je te laisse revenir vers moi !

Cordialement,
Kimy

[Pierre Fauconnier]

Salut.


Une technique pour "basculer" tes données en colonnes en utilisant DECALER

Soit le tableau suivant:



que tu souhaites réorganiser ainsi:



En A2 de la feuille qui contiendra le tableau final, tu saisis la formule:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=DECALER(Feuil2!$A$2;ENT((LIGNE()-2)/4);0)

En B2 de la même feuille, tu saisis la formule:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SI(DECALER(Feuil1!$B$2;ENT((LIGNE()-2)/4);MOD(LIGNE()-2;4))=0;"";DECALER(Feuil1!$B$2;ENT((LIGNE()-2)/4);MOD(LIGNE()-2;4)))

Puis tu tires vers le bas. Attention que les paramètres utilisées avec DECALER dépendent de ton tableau de base ET de l'endroit de ton tableau d'arrivée. Ainsi, je démarre en ligne 2 dans le tableau d'arrivée et j'ai quatre colonnes à ramener en lignes...

Donc, j'utilise LIGNE()-2) pour arriver à 0. Si le tableau final commençait en ligne 5 (entête) avec la première donnée récupérée en ligne 6, j'utiliserais LIGNE()-6. De même, je réalise une division par 4 et un modulo 4 parce que dans mon exemple, j'ai quatre colonnes maximum à récupérer. Si tu as 7 colonnes, tu dois bien entendu diviser par 7 et utiliser un modulo 7.

Dans mon exemple, j'ai 5 lignes et 4 colonnes, donc, je dois tirer sur 20 lignes (5 * 4) pour récupérer toutes mes données. La ligne 22 donne 0 en A, preuve que j'ai tiré trop bas. Si tu as 1000 lignes et 14 colonnes, tu devras tirer sur 14000 lignes...

Il suffit, après, de supprimer les lignes qui sont vides en B (par filtre ou autre technique assimilée)

Un tout petit peu de vba permet d'automatiser cela.

[LG-69]

Merci à vous tous pour ces éléments. J'ai trouvé la solution qui me convient grace à vous.

En fait Kimy, mon besoin fonctionnel exact est le suivant :

- J'ai un export d'un outil qui me donne une liste d'identifiants (uniques) avec un certain nombre d'infos
- J'ai un second export d'un autre outil qui me donne une liste de références (uniques) avec d'autres infos
- J'ai besoin de récupérer des infos dans chacun de mes exports sachant que mon lien entre ces deux listes se fait par l'intermédiaire d'un fichier excel (renseigné manuellement par une personne) qui contient une liste de références et pour chaque référence, dans une cellule, la liste des identifiants séparés par des espaces (c'est pourquoi j'ai besoin d'éclater cette cellule pour avoir un lien identifiant/référence exploitable avec des RECHERCHEV()

Donc la solution, comme expliqué dans mon dernier poste, marche jusqu'à obtenir le tableau illustré par Pierre.

Pièce jointe 169941

- J'ai ensuite trouvé une formule (matricielle) sur internet que j'ai retravaillée pour obtenir le numéro de ligne de la cellule dans laquelle je trouve ma valeur (ici en exemple je cherche 14)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=MIN(SI($A$1:$E$6=14;LIGNE($A$1:$E$6)))

=> résultat obtenu : 3 (en cas de doublon, ça ne me ramène que la première occurence trouvée mais ça me va bien)

- Après ça je n'ai "plus qu'à" récupérer la référence de la ligne trouvée avec la formule (matricielle) ci-dessous (qui me mets un blanc si non trouvé) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SI(MIN(SI($A$1:$E$6=14;LIGNE($A$1:$E$6)))>0;INDEX($A:$A;MIN(SI($A$1:$E$6=14;LIGNE($A$1:$E$6))));"")

- Après avec un RECHERCHEV() me permet de faire le lien entre tout

Le problème qui me reste à gérer c'est que pour séparer les multiples valeurs d'identifiants dans la cellule unique la personne utilise parfois des simples espace, des fois des doubles, des fois des virgules, des fois des "Alt+enter",... là je vais lui imposer de le faire de manière uniforme sinon je m'en sortirai pas.

@Pierre : je garde en tête ton idée pour passer mes données en verticale, là dans le cas présent finalement je n'en aurai pas besoin, de plus ça ne serait pas très adapté sachant que je peux avoir de 0 à n identifiants dans la même cellule, n étant inconnu (j'ai compté jusqu'à 34 mais généralement 2 ou 3) mais je garde l'idée qui peut servir dans d'autres cas.