Transpose matching data from columns to rows

[Godders199]

Hello, I have tried searching for the answer, but dont really no what to search.

Effectively i have sheet 1 and Sheet 2, sheet 1 has all the products , sale and dispatch dates, sheet 2 just lists all the products, for example A-Z

Sheet 1

[TABLE="width: 261"]
<colgroup><col width="135" style="width: 101pt; mso-width-source: userset; mso-width-alt: 4937;"> <col width="64" style="width: 48pt;"> <col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;" span="2"> <tbody>[TR]
[TD="width: 135, bgcolor: transparent"]Product[/TD]
[TD="width: 64, bgcolor: transparent"]Name[/TD]
[TD="width: 75, bgcolor: transparent"]Sale Date[/TD]
[TD="width: 75, bgcolor: transparent"]Dispact date[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]a[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]20/12/2018[/TD]
[TD="bgcolor: transparent, align: right"]24/12/2018[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]a[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]20/12/2018[/TD]
[TD="bgcolor: transparent, align: right"]24/12/2018[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]a[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]20/12/2018[/TD]
[TD="bgcolor: transparent, align: right"]24/12/2018[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]b[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]20/12/2018[/TD]
[TD="bgcolor: transparent, align: right"]24/12/2018[/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2
[TABLE="width: 317"]
<colgroup><col width="55" style="width: 41pt; mso-width-source: userset; mso-width-alt: 2011;"> <col width="92" style="width: 69pt; mso-width-source: userset; mso-width-alt: 3364;"> <col width="92" style="width: 69pt; mso-width-source: userset; mso-width-alt: 3364;" span="2"> <col width="92" style="width: 69pt; mso-width-source: userset; mso-width-alt: 3364;"> <tbody>[TR]
[TD="width: 55, bgcolor: transparent"]Product[/TD]
[TD="width: 92, bgcolor: transparent"]Dispatch date[/TD]
[TD="width: 92, bgcolor: transparent"]Dispatch date[/TD]
[TD="width: 92, bgcolor: transparent"]Dispatch date[/TD]
[TD="width: 92, bgcolor: transparent"]Dispatch date[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]a[/TD]
[TD="bgcolor: transparent, align: right"]24/12/2018[/TD]
[TD="bgcolor: transparent, align: right"]24/12/2018[/TD]
[TD="bgcolor: transparent, align: right"]24/12/2018[/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]b[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]c[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]d[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]

effectively what i want to do is, on sheet2, detail all the dispatch dates or each product in a row.

If some one could tell me how to do it, i will then beable to write te vba

thanks

 

[sadboy309]

I use formula array, so you need click Ctrl+Shift+Enter end of line formula.
Formula:

{=IFERROR(INDEX(Sheet1!$D$2:$D$5,SMALL(IF(Sheet1!$A$2:$A$5=Sheet2!$A2,ROW(Sheet1!$A$2:$A$5)-1,""),COLUMN()-1)),"")}

 

[Godders199]

thanks, i was just comng back to ihad found the answer, exactly the sameas yours

 

[bwaaack]

Hi! I have a similar 2 sheet transpose from columns to rows issue. Can you help with a formula that results in sheet 2?

Sheet1
[TABLE="width: 810"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]
Item#[/TD]
[TD]Image[/TD]
[TD]URL[/TD]
[/TR]
[TR]
[TD]10011-BKBK[/TD]
[TD]10011-BKBK_01.jpg[/TD]
[TD]assets/images/MEM_MAIN/10011-BKBK_01.jpg[/TD]
[/TR]
[TR]
[TD]10011-BKBK[/TD]
[TD]10011-BKBK_02_TABLETOP.jpg[/TD]
[TD]assets/images/MEM_ALT/10011-BKBK_02_TABLETOP.jpg[/TD]
[/TR]
[TR]
[TD]10011-GRGR[/TD]
[TD]10011-GRGR_01.jpg[/TD]
[TD]assets/images/MEM_MAIN/10011-GRGR_01.jpg[/TD]
[/TR]
[TR]
[TD]10011-GRGR[/TD]
[TD]10011-GRGR_02_FACE.jpg[/TD]
[TD]assets/images/MEM_ALT/10011-GRGR_02_FACE.jpg[/TD]
[/TR]
[TR]
[TD]10011-GRGR[/TD]
[TD]10011-GRGR_03_GIRL.jpg[/TD]
[TD]assets/images/MEM_ALT/10011-GRGR_03_GIRL.jpg[/TD]
[/TR]
[TR]
[TD]10011-RDBL[/TD]
[TD]10011-RDBL_01.jpg[/TD]
[TD]assets/images/MEM_MAIN/10011-RDBL_01.jpg
[/TD]
[/TR]
</tbody>[/TABLE]


Sheet 2

[TABLE="width: 1280"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Item#[/TD]
[TD]image[/TD]
[TD]image[/TD]
[TD]image[/TD]
[TD]image[/TD]
[TD]image[/TD]
[/TR]
[TR]
[TD]10011-BKBK[/TD]
[TD]assets/images/MEM_MAIN/10011-BKBK_01.jpg[/TD]
[TD]assets/images/MEM_ALT/10011-BKBK_02_TABLETOP.jpg[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10011-GRGR[/TD]
[TD]assets/images/MEM_MAIN/10011-GRGR_01.jpg[/TD]
[TD]assets/images/MEM_ALT/10011-GRGR_02_FACE.jpg[/TD]
[TD]assets/images/MEM_ALT/10011-GRGR_03_GIRL.jpg[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10011-RDBL[/TD]
[TD]assets/images/MEM_MAIN/10011-RDBL_01.jpg[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]