View original (French)

Excel formula problem

Maestro réj Posted messages 15 Registration date   Status Member Last intervention   -  
Le Pingou Posted messages 12273 Registration date   Status Contributor Last intervention   -

Hello and thank you in advance for your help,

I want to find a formula to concatenate data, but I am facing the following problem with my file

I found the following formula

if (A1="";concat (b1;" ";b2))

if (a1="";a2="";concat b1;" "; b2;" ";b3)  

etc etc I can have labels up to 7 lines

the formula works but I can't find the solution to nest 

example from my file

      column a (code)                               column b (a part of the label)   

1            000001                                        sofa

2                                                                   gray with armrest

3                                                                   and wooden foot

4           000002                                           click clack

4            000003                                          armchairs

6                                                                     sets of 2 green

7           000004                                             carpet

8                                                                     multicolored

9         

10

9 answers

Answer 1 / 9
cousinhub29 Posted messages 1112 Registration date   Status Member Last intervention   383
 

Hello,

Here is what we can achieve with Power Query (natively integrated into Excel since 2016)

Example file under this link
1
Le Pingou Posted messages 12273 Registration date   Status Contributor Last intervention   1 476
 

Hello cousinhub29,

Great solution, well done.

0
Answer 2 / 9
Le Pingou Posted messages 12273 Registration date   Status Contributor Last intervention   1 476
 

Hello,

I am sending you a small macro.

The data starts from line 2.

See the file: https://www.swisstransfer.com/d/3c23e37f-ffef-4120-b9d5-53aecc41182e

Best regards.
The Penguin
1
Answer 3 / 9
danielc0 Posted messages 2175 Registration date   Status Member Last intervention   286
 

Hello everyone,

Solution with Excel 365, the data being in column A and B:

 =GROUPBY(SCAN("",A1:A8,LAMBDA(x,y;IF(y<>"",y,x)));B1:B8&" ";CONCAT;;0)

Daniel
1
Answer 4 / 9
Le Pingou Posted messages 12273 Registration date   Status Contributor Last intervention   1 476
 

Hello,

In which column is the formula ... and the expected result?

Regards.
The Penguin
0
Answer 5 / 9
Maestro réj Posted messages 15 Registration date   Status Member Last intervention  
 

hello,

first of all, thank you for looking into my issue

column C
0
Answer 6 / 9
Maestro réj Posted messages 15 Registration date   Status Member Last intervention  
 

a second important indication, my file has more than 10,000 rows, I would like to be able to drag the formula down the entire column knowing that the formula must be able to take the right number of rows to concatenate, knowing that I may need to concatenate 2 rows for the next code, 7 rows for the next one, and 4 rows for the third

I hope I have been clear in my example

Thank you in advance
0
DjiDji59430 Posted messages 4278 Registration date   Status Member Last intervention   717
 

"I hope I was clear in my example"

cousinhub was just as clear!

Especially since you didn't specify your version of Excel either!


Best regards

0
Answer 7 / 9
Nain_Porte_Quoi Posted messages 152 Registration date   Status Member Last intervention   30
 

Hello,

If it's a one-time operation, I would copy your data into Word and use Find and Replace "^p^t" with "space" to concatenate everything, then just switch it back to Excel.
0
Answer 8 / 9
PHILOU10120 Posted messages 6463 Registration date   Status Contributor Last intervention   835
 

Hello Maestro réj

One idea with A1:A10 as the selected cells containing an x in B1:B10 for the labels.

In C1 this formula: =IF($A$1="x",$B$1,"")&IF($A$2="x",$B$2,"")&IF($A$3="x",$B$3,"")&IF($A$4="x",$B$4,"")&IF($A$5="x",$B$5,"")&IF($A$6="x",$B$6,"")&IF($A$7="x",$B$7,"")&IF($A$8="x",$B$8,"")

It is by forging that one becomes a blacksmith. It is at the foot of the wall that one sees the mason. One always learns from one's mistakes.
0
Nain_Porte_Quoi Posted messages 152 Registration date   Status Member Last intervention   30
 

@PHILOU10120 StatusContributor Maestro Réj specified that he had 10,000 lines. Your formula uses absolute addresses, so it can't be copied, and that would mean putting "x" in the N thousands of lines to be gathered… without making a mistake…

0
Answer 9 / 9
Maestro réj Posted messages 15 Registration date   Status Member Last intervention  
 

Thank you for all your suggestions, I will do the tests on Monday,

Have a good Sunday
0
Le Pingou Posted messages 12273 Registration date   Status Contributor Last intervention   1 476
 

Hello, Thank you for your feedback.

I made a small modification to have the same presentation of the results as danielc0 and cousinhub29 (regards).

The file: https://www.swisstransfer.com/d/c28b6938-ddd4-4212-bb1c-9333d608cabc

0