split data in one row multiple columns to multiple rows and columns.

[dimpana]

Hi,
I want the data in one row to split to multiple rows and multiple columns
like for ex: my transposed data appear like this:

I want the product number 2 and its related data to come below product number 1 like a table. for ex: I want a out like how it is in the image.

I have given example for two products, but there could be many pdts around 200-300. I want pdts to come below the other. I am in need to solve this ASAP. Aany help is highly appreciated. any excel function or even VBA is also ok.

 

[dimpana]

Hi,
I want the data in one row to split to multiple rows and multiple columns
like for ex: my transposed data appear like this: View attachment 87199
I want the product number 2 and its related data to come below product number 1 like a table. for ex: I want a out like how it is in the image. View attachment 87200
I have given example for two products, but there could be many pdts around 200-300. I want pdts to come below the other. I am in need to solve this ASAP. Aany help is highly appreciated. any excel function or even VBA is also ok.

 

[52779]

Hi,
As in the picture you attached,
if the same group of data repeats every 6 columns

Maybe
(solution by formula 365)

 

[dimpana]

Hi,
As in the picture you attached,
if the same group of data repeats every 6 columns

Maybe
(solution by formula 365)
View attachment 87216

Hi , thanks for the solution, my data repeats after every 14 columns, I tried changing the 6 to 14 in the formula, I am getting spill error.
can u pls help me to resolve this

 

[dimpana]

Hi , thanks for the solution, my data repeats after every 14 columns, I tried changing the 6 to 14 in the formula, I am getting spill error.
can u pls help me to resolve this

and I am not able to drag the formula to other cells

 

[Georgiboy]

The formula will not need to be dragged as it is a spill formula.

You will get the spill error if there is not enough room (blank cells) for the formula to spill into. Make sure there is space for the spill range.

 

[dimpana]

and I am not able to drag the formula to other cells

Hey thank u so much it worked. Your awesome , you solved my problem . Thanks a ton