Transpose a group of data with spaces

[murexcel]

Hi, I have some data that needs to be transposed. I have attached pictures of how the data is currently & what format I need it in (Left image is current format, right image is required format). I have tried the transpose formula, but it doesn't provide the required result.




Thanks.

 

[alansidman]

cannot manipulate data in a picture. Please upload your source data using XL2BB

 

[murexcel]

Hi, below is the mini sheet

Book1.xlsx
	A	B	C	D	E	F	G
1	CURRENT DATA		REQUIRED DATA
2
3	Swash		Swash	Hand Wash Blue Aura	500 ML	UAE
4	Hand Wash Blue Aura		Swash	Laundry Liquid Tidy Blue	2 Ltr	UAE
5	500 ML		Swash	Floor Cleaner Lemon	5 Ltr	UAE
6	UAE
7
8	Swash
9	Laundry Liquid Tidy Blue
10	2 Ltr
11	UAE
12
13	Swash
14	Floor Cleaner Lemon
15	5 Ltr
16	UAE
17
Sheet1

 

[vladimiratanasiu]

Hi, below is the mini sheet

Book1.xlsx
	A	B	C	D	E	F	G
1	CURRENT DATA		REQUIRED DATA
2
3	Swash		Swash	Hand Wash Blue Aura	500 ML	UAE
4	Hand Wash Blue Aura		Swash	Laundry Liquid Tidy Blue	2 Ltr	UAE
5	500 ML		Swash	Floor Cleaner Lemon	5 Ltr	UAE
6	UAE
7
8	Swash
9	Laundry Liquid Tidy Blue
10	2 Ltr
11	UAE
12
13	Swash
14	Floor Cleaner Lemon
15	5 Ltr
16	UAE
17
Sheet1

Is it what you need?

Book1
	A	B	C	D	E	F	G
1	Swash			Swash	Face & Body Scrub Turmeric	500 ML	UAE
2	Face & Body Scrub Turmeric			Swash	Face & Body Scrub Coffee	500 ML	UAE
3	500 ML			Swash	Face & Body Scrub Aloevera	500 ML	UAE
4	UAE
5
6	Swash
7	Face & Body Scrub Coffee
8	500 ML
9	UAE
10
11	Swash
12	Face & Body Scrub Aloevera
13	500 ML
14	UAE
Sheet1

Cell Formulas
Range	Formula
D1:G3	D1	=INDEX($A:$A,ROW(A1)*5-5+COLUMN(A1))

 

[murexcel]

Thanks a lot. That solved my problem.

 

[vladimiratanasiu]

You're welcome. Thank you for feedback.