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"

 

[datastudent]

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.

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.

Thank you for your response on my second question. But it seems like it doesn't work on the data that I have. Hope you can still help me.

This is how my actual data looks like.

Template_v1.xlsm
	A	B	C	D
1	Country	Service Plan	Origin type	OUT FIX/OUT MOB
2	AUSTRALIA	Fixed	National	0.0116
3	AUSTRALIA	Fixed	National	0.0116
4	AUSTRALIA	Fixed	National	0.0116
5	AUSTRALIA	Fixed	National	0.0116
6	AUSTRALIA	Mobile	National	0.0306
7	AUSTRALIA	Mobile	National	0.0306
8	AUSTRIA	Fixed	National	0.0082
9	AUSTRIA	Fixed	National	0.0082
10	AUSTRIA	Mobile	National	0.0092
VoxOut

 

[Fluff]

What is your expected outcome?

 

[datastudent]

I want to put Fixed ang Mobile in a separate column for each country.

This is the expected outcome.

Template_v1.xlsm
	L	M	N	O
1	Country	Origin Type	Fixed	Mobile
2	AUSTRALIA	National	0.0116	0.0306
3	AUSTRIA	National	0.0082	0.0092
VoxOut

 

[Fluff]

Ok, how about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I
1	Country	Service Plan	Origin type	OUT FIX/OUT MOB
2	AUSTRALIA	Fixed	National	0.0116				Fixed	Mobile
3	AUSTRALIA	Fixed	National	0.0116		AUSTRALIA	National	0.0116	0.0306
4	AUSTRALIA	Fixed	National	0.0116		AUSTRIA	National	0.0082	0.0092
5	AUSTRALIA	Fixed	National	0.0116
6	AUSTRALIA	Mobile	National	0.0306
7	AUSTRALIA	Mobile	National	0.0306
8	AUSTRIA	Fixed	National	0.0082
9	AUSTRIA	Fixed	National	0.0082
10	AUSTRIA	Mobile	National	0.0092
11
Data

Cell Formulas
Range	Formula
F2:I4	F2	=PIVOTBY(CHOOSECOLS(A2:C100,1,3),B2:B100,D2:D100,MAX,0,0,,0,,A2:A100<>"")
Dynamic array formulas.