Re-arrange data from vertical to horizontal

Can someone point me to the right direction please?

Attached is my data. I need to show a product and the all the region in the same row. Is there a way to do that? I even consider to concatenate all regions in a single cell.

My workbook has a few thousand line. This is only an example.


Thanks,

I suggest you use a helper column for this, so put this formula in C3:

=IF(A3="","-",A3&"_"&COUNTIF(A$3:A3,A3))

then copy it down beyond your data (the hyphens will indicate this). Then, with your products listed from F3 downwards, you can put this formula in G3:

=IFERROR(INDEX($B:$B,MATCH($F3&"_"&COLUMNS($G:G),$C:$C,0)),"")

Copy this across and down as required. The attached file (Sheet1) demonstrates this.

If it is not easy for you to supply the list of products, then you can derive them with the use of another helper column - put this formula in D3:

=IF(A3="","-",IF(RIGHT(C3,2)="_1",MAX(D$2:D2)+1,""))

and copy this down beyond your data, as indicated by the hyphens. Then you can use this formula in F3:

=IFERROR(INDEX(A:A,MATCH(ROWS($1:1),D:D,0)),"")

Which can be copied down as far as you need it (or beyond, to accommodate more data in future). This approach is shown in Sheet2 of the attached file.

Hope this helps.

Pete

Brilliant!
Many thanks for you help.