Combining a string of text - TEXTJOIN

[IanG]

Hi

I have attached a spreadsheet.

Currently I am using =TEXTJOIN(",",TRUE,$A_:$A_) to combine the data in column A and inserting the data into row of Column B and C where the terms "configurable and grouped" appear in column A

I click on B152 and enter the formula =TEXTJOIN(",",TRUE,$A2:$A151) into B152, this gave me the correct result, I then copied the same result into C153

I scrolled down until I saw

31B12_configurable
31B12_grouped

in Column A (row 284 + 285) and pasted my copied formula into B284:C285 and changed the formula search to =TEXTJOIN(",",TRUE,$A154:$A283)

I repeat these steps above very time I see the term "configurable and grouped" in Column A

Because I need all the data between grouped and configurable (and not including) i was hoping there would be a formula where i could say start after configurable and finish before grouped I could use? The number of rows between each set varies.

The way I am doing is very time consuming and I have 7 spreadeagled with a total of 120,000 rows to do.

Thanks Ian

 

[AliGW]

Sounds like a job for Power Query (Data ribbon - Get & Transform), which you have with Office 365.

 

[IanG]

Hi Ali, looks like its time I learnt power query.....I wasn't aware of it.

 

[GraH - Guido]

Something like this in PQ

 

[IanG]

Something like this in PQ

That works, excellent, it just the data is back to front, as in I need the data to be the other way around. I tried unsuccessfully to flip but i couldn't

 

[AliGW]

What exactly do you mean by "back to front"?

 

[GraH - Guido]

Like this perhaps?