Transpose and Repeat Question

[anouhelp]

Hi, I looked across the board and was unable to find a solution. I was wondering if there was a formula or code that I code to use to transpose and repeat data, just like below.

Thanks for you time and help!

Current

Wish

Customer

Name

Address

Email

Email

Email

Customer

Name

Address

Email

1​

Jane

123​

1@3

2@3

3@3

1​

Jane

123​

1@3

2​

Jim

345​

1@4

2@4

4@4

1​

Jane

123​

2@3

3​

John

678​

1@5

2@5

5@5

1​

Jane

123​

3@3

2​

Jim

345​

1@4

2​

Jim

345​

2@4

2​

Jim

345​

4@4

3​

John

678​

1@5

3​

John

678​

2@5

3​

John

678​

5@5

 

[Fluff]

Hi & welcome to MrExcel.
How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Customer	Name	Address	Email	Email	Email		Customer	Name	Address	Email
2	1	Jane	123	1@3	2@3	3@3		1	Jane	123	1@3
3	2	Jim	345	1@4	2@4	4@4		1	Jane	123	2@3
4	3	John	678	1@5	2@5	5@5		1	Jane	123	3@3
5								2	Jim	345	1@4
6								2	Jim	345	2@4
7								2	Jim	345	4@4
8								3	John	678	1@5
9								3	John	678	2@5
10								3	John	678	5@5
11
Data

Cell Formulas
Range	Formula
H2:K10	H2	=HSTACK(TOCOL(IF(D2:F4="",x,A2:A4),2),TOCOL(IF(D2:F4="",x,B2:B4),2),TOCOL(IF(D2:F4="",x,C2:C4),2),TOCOL(D2:F4,1))
Dynamic array formulas.

 

[anouhelp]

Hi & welcome to MrExcel.
How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Customer	Name	Address	Email	Email	Email		Customer	Name	Address	Email
2	1	Jane	123	1@3	2@3	3@3		1	Jane	123	1@3
3	2	Jim	345	1@4	2@4	4@4		1	Jane	123	2@3
4	3	John	678	1@5	2@5	5@5		1	Jane	123	3@3
5								2	Jim	345	1@4
6								2	Jim	345	2@4
7								2	Jim	345	4@4
8								3	John	678	1@5
9								3	John	678	2@5
10								3	John	678	5@5
11
Data

Cell Formulas
Range	Formula
H2:K10	H2	=HSTACK(TOCOL(IF(D2:F4="",x,A2:A4),2),TOCOL(IF(D2:F4="",x,B2:B4),2),TOCOL(IF(D2:F4="",x,C2:C4),2),TOCOL(D2:F4,1))
Dynamic array formulas.

Fluff, thank you so much! That was exactly the solution I was looking for.

I hope I can be a contributor just like you!

Thanks again for your help!

 

[alansidman]

Here is an alternative solution using Power Query

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

 

[Fluff]

Glad to help & thanks for the feedback.