Need a formula to transpose data from a Column to a Row

Hi,

I can do this the easy way by using Paste Special and using Transpose, only my list is so long I'm hoping there is a formula that can do it for me.
I've already filtered out the data, the list is actually much longer but I've cut it down for the purpose of this post.

You can see in Column D that I have filtered out unique Part No's from Column A. I've provided an example on Row 2 of what I hope to achieve, which is to transpose the data from Column B against the relevant Part No in Column D.

I just don't know how to automate this process. Any help very much appreciated on this.

Regards,
Adam.

Many thanks,

I tried as you said but it only seems to convert one row and then comes up with the following error -

Run-time error '1004':
Application-defined or object-defined error

I've attached a screenshot of when I click De-bug.

Otherwise it worked fine on that first line.

I just ran it as you uploaded, saved a copy to my PC and tried running it but got the same result.

No data added, just as is.

Thanks.

In E2: =INDEX($B:$B,COLUMNS($A:B))

copy across

Originally Posted by Teethless mama

In E2: =INDEX($B:$B,COLUMNS($A:B))

copy across

Thanks.

That works going across but how about going down for the other part numbers? I seem to get the same results as the first line.

Mike, that amendment works perfectly! Many many thanks.

I have this half working/understood but could anyone just check my spreadsheet please and explain a little more. I have uploaded a small test version and I am looking to add all colour options of the same product code (col D) to be transposed in to col G corresponding cells, I will then filter and remove duplicate data so I am just left with 1 product code for all colours but will have all colours in a 1 cell in column g (or if after concatenating anyways). you will see there are only 4 product codes and each has a different number of colours in rows.
you will see in the jpeg the data that I want to appear in just 1 cell, or transposed across a number of cells
Thanks for any help, john

john

hey if you need to transpose data use below formular.(its dynamic)
=TRANSPOSE(D1:D199)
you need to press Ctl + Shirt + Enter since its a array
and then you can remove duplicate
hope this will help

Capture.JPG