Columns data into one cell on separate rows

[ridug]

Hi all,

I have a sheet containing a series of different columns with varying data, and I'm trying to figure out a way to automate pulling those columns' data into a line-by-line separated list in a single cell. The "list" format wouldn't change in column A.

Example data:

Is there a way to do this or an alternative method that I'm perhaps overlooking? Any input would be greatly appreciated! Would love to have a semi-automated method at the very least, aha

 

[ridug]

Hi all,

I have a sheet containing a series of different columns with varying data, and I'm trying to figure out a way to automate pulling those columns' data into a line-by-line separated list in a single cell. The "list" format wouldn't change in column A.

Example data:
View attachment 95776

Is there a way to do this or an alternative method that I'm perhaps overlooking? Any input would be greatly appreciated! Would love to have a semi-automated method at the very least, aha

Oops - pretend that "Current URL" and "New URL" say "Page2" and "Page-2"

 

[Anonymous1378]

How about

=TEXTAFTER(TEXTSPLIT(A2,CHAR(10)),"-",1)

 

[Peter_SSs]

Could be wrong, but I am interpreting it the other way around.

I'm trying to figure out a way to automate pulling those columns' data into a line-by-line separated list in a single cell.

23 07 23.xlsm
	A	B	C	D	E
1	Hdr 1	Hdr 2	Hdr 3	Hdr 4	Hdr 5
2	Hdr 2 - Data 1 Hdr 3 - Data 3 Hdr 4 - Data 5 Hdr 5 - Data 7	Data 1	Data 3	Data 5	Data 7
3	Hdr 2 - Data 2 Hdr 3 - Data 4 Hdr 4 - Data 6 Hdr 5 - Data 8	Data 2	Data 4	Data 6	Data 8
To single cell

Cell Formulas
Range	Formula
A2:A3	A2	=LET(a,B$1:E2,SUBSTITUTE(TEXTJOIN({" - ","#"},,TOCOL(CHOOSEROWS(a,1,ROWS(a)),,1)),"#",CHAR(10)))

For the future, I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

 

[Anonymous1378]

Oops, pretty sure you're right then. Guess I was misled by the mention of

The "list" format wouldn't change in column A.

So I believed that was the source rather than the output.

 

[Rick Rothstein]

Using the formula @Peter_SSs posted in Message #4 as a basis, this single formula will spill the entire result from this single formula placed in cell A2 (edit the 3 in the E3 at the beginning of the formula to match the last row number in your actual data set)...

=BYROW(B2:E3,LAMBDA(r,SUBSTITUTE(TEXTJOIN({" - ","#"},,TOCOL(VSTACK(B1:E1,r),,1)),"#",CHAR(10))))

 

[Peter_SSs]

Depending on whether you wanted to copy down or have a single formula spill down, these would be other options.

23 07 23.xlsm
	A	B	C	D	E
1	Hdr 1	Hdr 2	Hdr 3	Hdr 4	Hdr 5
2	Hdr 2 - Data 1 Hdr 3 - Data 3 Hdr 4 - Data 5 Hdr 5 - Data 7	Data 1	Data 3	Data 5	Data 7
3	Hdr 2 - Data 2 Hdr 3 - Data 4 Hdr 4 - Data 6 Hdr 5 - Data 8	Data 2	Data 4	Data 6	Data 8
To single cell (2)

Cell Formulas
Range	Formula
A2:A3	A2	=TEXTJOIN(CHAR(10),,B$1:E$1&" - "&B2:E2)

23 07 23.xlsm
	A	B	C	D	E
1	Hdr 1	Hdr 2	Hdr 3	Hdr 4	Hdr 5
2	Hdr 2 - Data 1 Hdr 3 - Data 3 Hdr 4 - Data 5 Hdr 5 - Data 7	Data 1	Data 3	Data 5	Data 7
3	Hdr 2 - Data 2 Hdr 3 - Data 4 Hdr 4 - Data 6 Hdr 5 - Data 8	Data 2	Data 4	Data 6	Data 8
To single cell (3)

Cell Formulas
Range	Formula
A2:A3	A2	=BYROW(B2:E3,LAMBDA(r,TEXTJOIN(CHAR(10),,B1:E1&" - "&r)))
Dynamic array formulas.

 

[alansidman]

With Power Query aka Get and Transform Data found on the Data Tab of the Ribbon. Bring the data into the PQ Editor. Highlight the first column, click on Transform-->Unpivot--Unpivot other columns.

 

[ridug]

Depending on whether you wanted to copy down or have a single formula spill down, these would be other options.

23 07 23.xlsm
	A	B	C	D	E
1	Hdr 1	Hdr 2	Hdr 3	Hdr 4	Hdr 5
2	Hdr 2 - Data 1 Hdr 3 - Data 3 Hdr 4 - Data 5 Hdr 5 - Data 7	Data 1	Data 3	Data 5	Data 7
3	Hdr 2 - Data 2 Hdr 3 - Data 4 Hdr 4 - Data 6 Hdr 5 - Data 8	Data 2	Data 4	Data 6	Data 8
To single cell (2)

Cell Formulas
Range	Formula
A2:A3	A2	=TEXTJOIN(CHAR(10),,B$1:E$1&" - "&B2:E2)

23 07 23.xlsm
	A	B	C	D	E
1	Hdr 1	Hdr 2	Hdr 3	Hdr 4	Hdr 5
2	Hdr 2 - Data 1 Hdr 3 - Data 3 Hdr 4 - Data 5 Hdr 5 - Data 7	Data 1	Data 3	Data 5	Data 7
3	Hdr 2 - Data 2 Hdr 3 - Data 4 Hdr 4 - Data 6 Hdr 5 - Data 8	Data 2	Data 4	Data 6	Data 8
To single cell (3)

Cell Formulas
Range	Formula
A2:A3	A2	=BYROW(B2:E3,LAMBDA(r,TEXTJOIN(CHAR(10),,B1:E1&" - "&r)))
Dynamic array formulas.

Thank you so much! Works a treat! I'll make sure to include an XL2BB-based example next time.

 

[ridug]

Lots of excellent suggestions here, thanks all!