Combining two columns in one(not concut)

[datastudent]

Hi,

Please help me combine two set of data in one column without using pivot.

The first table is the data that I have and the second table is how I want it to be. I would like a formula that would give me the result for both tables please like if I have table2 how do I get table1 and vice versa. Thank you!

Template_v1.xlsm
	A	B	C	D	E	F	G
1	Name	Fixed	Mobile		Name	Service	Cost
2	AAA	123	122		AAA	Fixed	123
3	BBB	234	345		AAA	Mobile	122
4					BBB	Fixed	234
5					BBB	Mobile	345
Sheet7

 

[Cubist]

Try:

Book2
	A	B	C	D	E	F	G
1	Name	Fixed	Mobile		Name	Service	Cost
2	AAA	123	122		AAA	Fixed	123
3	BBB	234	345		AAA	Mobile	122
4					BBB	Fixed	234
5					BBB	Mobile	345
Sheet5

Cell Formulas
Range	Formula
E2:G5	E2	=DROP(REDUCE("",TOCOL(A2:A3&"|"&B1:C1&"|"&B2:C3),LAMBDA(x,y,VSTACK(x,TEXTSPLIT(y,"|")))),1)
Dynamic array formulas.

 

[Fluff]

Another option

=LET(d,B2:C3,HSTACK(TOCOL(IF(d<>"",A2:A3,1/0),2),TOCOL(IF(d<>"",B1:C1,1/0),2),TOCOL(d,1)))

 

[Peter_SSs]

Another option

25 01 25.xlsm
	A	B	C	D	E	F	G
1	Name	Fixed	Mobile		Name	Service	Cost
2	AAA	123	122		AAA	Fixed	123
3	BBB	234	345		AAA	Mobile	122
4					BBB	Fixed	234
5					BBB	Mobile	345
2 to 1

Cell Formulas
Range	Formula
E2:G5	E2	=TEXTSPLIT(TEXTJOIN(":",,A2:A3&"|"&B1:C1&"|"&B2:C3),"|",":")
Dynamic array formulas.

The above formula returns text values in column G (as does the suggestion in post2). If those values need to be numerical then

25 01 25.xlsm
	A	B	C	D	E	F	G
1	Name	Fixed	Mobile		Name	Service	Cost
2	AAA	123	122		AAA	Fixed	123
3	BBB	234	345		AAA	Mobile	122
4					BBB	Fixed	234
5					BBB	Mobile	345
2 to 1 (2)

Cell Formulas
Range	Formula
E2:G5	E2	=LET(t,TEXTSPLIT(TEXTJOIN(":",,A2:A3&"|"&B1:C1&"|"&B2:C3),"|",":"),IFERROR(--t,t))
Dynamic array formulas.

.. both tables ... like if I have table2 how do I get table1

25 01 25.xlsm
	A	B	C	D	E	F	G
1	Name	Fixed	Mobile		Name	Service	Cost
2	AAA	123	122		AAA	Fixed	123
3	BBB	234	345		AAA	Mobile	122
4					BBB	Fixed	234
5					BBB	Mobile	345
1 to 2

Cell Formulas
Range	Formula
A2:C3	A2	=HSTACK(UNIQUE(E2:E5),WRAPROWS(G2:G5,2))
Dynamic array formulas.

 

[Cubist]

TEXTJOIN method has character limitation so I stopped suggesting it.

 

[alansidman]

and yet one more option with power query. Unpivot your data

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Name"}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"