Concatener valeurs sur nb de lignes variables avec condition = ID ou vide

Question

J'ai un fichier extrait d'un ERP avec une liste de SAV à réaliser. L'extraction génère parfois plusieurs lignes pour une entrée (un SAV). Par exemple si 3 personnes sont assignées à l'opération SAV 2025-004, la première ligne contiendra mon ID 2025-004 en 1ère colonne puis dans une autre colonne le nom de la première personne assignée à cette opération ; puis 2 autres lignes sans ID mais avec le nom des 2 autres personnes assignées. Puis ensuite une autre ligne avec le SAV 2025-005.... On utilise cette extraction pour faire une cartographie des SAV à planifier. Je dois donc créer un onglet qui traite automatiquement les données issues de l'extraction ERP et collées dans le 1er onglet. Mon problème est que je n'arrive pas à concaténer dans une même cellule les données issues d'un nombre variable de lignes dont la 1ère contient mon ID unique jusqu'à la suivante ayant un autre ID unique... (en l'occurence, la liste de toutes les personnes assignées à ce SAV) J'ai fait une tentative dans le fichier exemple ci-dessous (que j'ai simplifié) et je ne comprends pas pourquoi ça ne fonctionne pas... Mes données brutes sont dans les colonnes A à D ; et ma tentative de formule dans la colonne H mon fichier exemple Merci d'avance de votre aide, j'espère avoir réussi à résumer mon problème, et que le fichier est évocateur...

saboh12617 · Accepted Answer

Re,

Après beaucoup de cassage de tete j'ai revu la formule. Elle devrait etre plus adaptable et plus rapide.

En fait il y a des différences de fonctionnement entre Excel et Google Sheets sur ces formules ce qui m'a fait tourner en bourrique. Notamment le ARRAYFORMULA à la ligne rowsIDs qui… beh déjà Excel bug, mais avec ceci Google Sheets fonctionne à merveille. J'ai corrigé votre histoire des IDs qui ne sont pas des nombres en remplaçant ISNUMBER par NOT(ISBLANK).

Voici donc la formule, avec les paramètres suivant :

myData : vous prenez toute la plage

myNames : vous mettez le numéro de colonne (sur cette plage, pas global) correspondant à la colonne des noms

myEtq : pareil que myNames, pour la colonne des étiquettes

Le reste s'adapte automatiquement.

=LET(myData;A2:E22;
myNames;CHOOSECOLS(myData;4);
myEtq;CHOOSECOLS(myData;5);
getConcat;LAMBDA(dataB;rowLen;rowStart;TEXTJOIN(", ";TRUE;CHOOSEROWS(dataB;SEQUENCE(rowLen;1;rowStart;1))));
myIDs;FILTER(CHOOSECOLS(myData;1);NOT(ISBLANK(CHOOSECOLS(myData;1))));
rowsIDs;VSTACK(ARRAYFORMULA(XMATCH(myIDs;CHOOSECOLS(myData;1)));rows(myData)+1);
matriceFinale;MAKEARRAY(ROWS(myIDs);3;LAMBDA(r;c;
SWITCH(c;
1;INDEX(myIDs;r);
2;getConcat(myNames;INDEX(rowsIDs;r+1)-INDEX(rowsIDs;r);INDEX(rowsIDs;r));
3;getConcat(myEtq;INDEX(rowsIDs;r+1)-INDEX(rowsIDs;r);INDEX(rowsIDs;r)))
));
matriceFinale)

On pourra sur cette formule facilement rajouter des colonnes au besoin.

Vous avez aussi en colonnes M,N,O,P une méthode de calcul plus "manuelle". C'est la même que cette formule, mais décomposée en étapes.

saboh12617 · Answer

Pouvez-vous partager votre fichier en édition svp ? Je voudrais tester ma formule...

saboh12617 · Answer

Re, merci beaucoup.

On peut utiliser la formule ci-dessous.

Je sais que c'est assez indigeste, c'est pourquoi je vous ai mis 2 paramètres en haut de la fonction :

myNames : qui va contenir la plage des noms, vous pouvez la modifier ici.

offsetID : qui va contenir le nombre de colonnes à décaler de cette plage pour récupérer les ID. Négatifs pour aller à gauche.

La fonction s'ajustera automatiquement à vos données, tant que la plage myNames couvre bien tous les noms.

=LET(
myNames; D2:D19;
offsetID; -3;
myIDs; OFFSET(myNames; 0; offsetID);
myTbl; REDUCE(0; myNames; LAMBDA(acc; nom;
IF(ISNUMBER(OFFSET(nom; 0; offsetID));
VSTACK(acc; nom);
VSTACK(CHOOSEROWS(acc; SEQUENCE(ROWS(acc)-1));TEXTJOIN(", "; TRUE; CHOOSEROWS(acc; -1);nom)))
));
HSTACK(FILTER(myIDs; ISNUMBER(myIDs)); CHOOSEROWS(myTbl; SEQUENCE(ROWS(myTbl)-1; 1; 2))))

saboh12617 · Answer

Je vous accorde que c'est difficile à relire. Quand on s'habitue c'est "toujours pareil", mais quand j'ai commencé à l'utiliser, j'avais aussi beaucoup de mal avec cette fonction REDUCE.

Pour faire simple, je parcours votre liste de noms, et pour chaque nom la fonction regarde ce qu'il y a dans la colonne ID.

Si c'est un numéro, parfait, on l'ajoute à notre liste de résultats.
Sinon (si c'est vide), on prend le nom du dessus, on lui ajoute ", " + le nom actuel, et on remplace dans notre liste de résultats la dernière valeur (= nom du dessus) par cette nouvelle valeur.

Donc on retrouve la liste des noms comme ça, qu'importe la quantité d'espace entre 2 numéros.

Puis dans la dernière ligne de la fonction que je vous ai écrite ("HSTACK..."), on ajoute simplement les IDs à cette liste (on "stack" horizontalement les 2 listes).

Si vous voulez de l'aide pour la suite, dans votre feuille ajoutez les colonnes dont vous parlez, et mettez quelques résultats "manuels" que je comprenne bien. J'essaierai de voir comment adapter.

saboh12617 · Answer

Je suis en train de voir une formule différente, mais pourquoi vos ID ne sont pas uniques ??? Michel et Bertrand sont tous les deux 6 est-ce normal ...

saboh12617 · Answer

Merci pour votre retour, content d'aider. Ho il y a surement d'autres méthodes, je vous en ai donné une, probablement pas la plus simple d'ailleurs, mais ça fonctionne 😄 Bonne journée ...