Align And Append Data

[ranjith2523]

Hi Friends,

I need to copy each EX1, EX2, and EX3 value from cells A2, B2, and C2 then I need to transpose and paste it in column F2.

But the issue here is, when I paste I need to replicate the value three times .

The below table is just an example with 2 rows, but actually, I have around 500+ rows that I need to copy transpose paste three times in column H.

Please let me know how can we do this using some Excel formula. Any help or advice is much appreciated.

EX1	EX2	EX3		Client	ABC	ORDER
0F8	8F0	8F8			0F8	HS
0G8	8G0	8G8			0F8	SS
					0F8	ES
					8F0	HS
					8F0	SS
					8F0	ES
					8F8	HS
					8F8	SS
					8F8	ES
					0G8	HS
					0G8	SS
					0G8	ES
					8G0	HS
					8G0	SS
					8G0	ES
					8G8	HS
					8G8	SS
					8G8	ES

 

[alansidman]

Book2
	A	B	C	D	E	F	G
1	EX1	EX2	EX3		Client	Value	Custom
2	0F8	8F0	8F8			0F8	HS
3	0G8	8G0	8G8			0F8	SS
4						0F8	ES
5						8F0	HS
6						8F0	SS
7						8F0	ES
8						8F8	HS
9						8F8	SS
10						8F8	ES
11						0G8	HS
12						0G8	SS
13						0G8	ES
14						8G0	HS
15						8G0	SS
16						8G0	ES
17						8G8	HS
18						8G8	SS
19						8G8	ES
Sheet1

With Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each "HS,SS,ES"),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Added Custom", {{"Custom", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Custom")
in
    #"Split Column by Delimiter"

 

[Fluff]

How about

=TOCOL(IF(SEQUENCE(,3),TOCOL(A2:C10,1)))

 

[ranjith2523]

Hi Fluff., Good to see your response after a long time. As usual, this formula works as expected, and sorry there is one more requirement.

In G Column, need to repeat the values HS SS ES as shown in the initial post table. I tried something like REPT formula but as usual it didn't work out for me

Can you please guide me on this.

Material Group 4
HS
SS
ES
HS
SS
ES

Book2
	A	B	C	D	E	F	G
1	EX1	EX2	EX3		Client	Value	Custom
2	0F8	8F0	8F8			0F8	HS
3	0G8	8G0	8G8			0F8	SS
4						0F8	ES
5						8F0	HS
6						8F0	SS
7						8F0	ES
8						8F8	HS
9						8F8	SS
10						8F8	ES
11						0G8	HS
12						0G8	SS
13						0G8	ES
14						8G0	HS
15						8G0	SS
16						8G0	ES
17						8G8	HS
18						8G8	SS
19						8G8	ES
Sheet1

With Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each "HS,SS,ES"),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Added Custom", {{"Custom", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Custom")
in
    #"Split Column by Delimiter"

 

[ranjith2523]

Book2
	A	B	C	D	E	F	G
1	EX1	EX2	EX3		Client	Value	Custom
2	0F8	8F0	8F8			0F8	HS
3	0G8	8G0	8G8			0F8	SS
4						0F8	ES
5						8F0	HS
6						8F0	SS
7						8F0	ES
8						8F8	HS
9						8F8	SS
10						8F8	ES
11						0G8	HS
12						0G8	SS
13						0G8	ES
14						8G0	HS
15						8G0	SS
16						8G0	ES
17						8G8	HS
18						8G8	SS
19						8G8	ES
Sheet1

With Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each "HS,SS,ES"),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Added Custom", {{"Custom", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Custom")
in
    #"Split Column by Delimiter"

Thank you so much for your response and thanks for your effort to find out the solution. Yes, it works as expected.

 

[Fluff]

How about

=LET(a,TOCOL(IF(SEQUENCE(,3),TOCOL(A2:C10,1))),HSTACK(a,TOCOL(IF(SEQUENCE(,ROWS(a)/3),{"HS";"SS";"ES"}),,1)))

 

[ranjith2523]

How about

=LET(a,TOCOL(IF(SEQUENCE(,3),TOCOL(A2:C10,1))),HSTACK(a,TOCOL(IF(SEQUENCE(,ROWS(a)/3),{"HS";"SS";"ES"}),,1)))

OMG !!! Unbelievable, my head is just started rotating after seeing this formula and not sure from where you learned all those things Genius..

 

[Fluff]

Glad we could help & thanks for the feedback.