Transpose rows of data to column, with 1st cell in row repeated

Hi,

Difficult one to explain so I have mocked up an example attached. I found a formula online that works but only for a maximum of 3 cells in the row. Each row can contain more or less than 3 cells of data, please could you help modify this formula or suggest a new formula to achieve how I would like the final columns of data to look in the example.

Appreciate your help, been scratching my head for ages!

Thanks,
Adam.

Hi,

With a helper in columns M-N


N2 =SUM(--(ISTEXT((IF((M2=$A$2:$A$5),$B$2:$K$5)))))+COUNT(1/(IF((M2=$A$2:$A$5),$B$2:$K$5)))

O2 =A2
O3 =IFERROR(IF(VLOOKUP(O2,$M$2:$N$19,2,0)<=COUNTIF($O$1:O2,O2),INDEX($M$2:$M$19,MATCH(O2,$M$2:$M$19)+1),O2),"")

P2 =IFERROR(INDEX($B$2:$K$5,MATCH(O2,$A$2:$A$5,0),SMALL(IF((O2=$A$2:$A$5)*($B$2:$K$5<>""),COLUMN($B$2:$K$5)),IF(O2="",VLOOKUP(O2,$M$2:$N$5,2,0),VLOOKUP(O2,$M$2:$N$5,2,0)-COUNTIF(O3:$O$83,O2)))-1),"")

Thank you all very much for a looking at this so quickly.

I went with the below, for my limited understanding of these formula's I was able to get this one to work for a much larger volume of data.

Originally Posted by belinda200

Hi,

With a helper in columns M-N


N2 =SUM(--(ISTEXT((IF((M2=$A$2:$A$5),$B$2:$K$5)))))+COUNT(1/(IF((M2=$A$2:$A$5),$B$2:$K$5)))

O2 =A2
O3 =IFERROR(IF(VLOOKUP(O2,$M$2:$N$19,2,0)<=COUNTIF($O$1:O2,O2),INDEX($M$2:$M$19,MATCH(O2,$M$2:$M$19)+1),O2),"")

P2 =IFERROR(INDEX($B$2:$K$5,MATCH(O2,$A$2:$A$5,0),SMALL(IF((O2=$A$2:$A$5)*($B$2:$K$5<>""),COLUMN($B$2:$K$5)),IF(O2="",VLOOKUP(O2,$M$2:$N$5,2,0),VLOOKUP(O2,$M$2:$N$5,2,0)-COUNTIF(O3:$O$83,O2)))-1),"")

Thank you all again.

Regards,
Adam.

You're welcome ads111ads

Originally Posted by alansidman

With Get and Transform found on the Data Tab
Highlihgt your table
Click on From Table or Range
Highliht the Stock Number Column
Click on Unpivot --> Unpivot other columns

Hi Alan,
can you please explain where is the unpivot? Is it in the Powerquery editor or a stage before the page opens?

Thanks.