transpose of data from columns to multiple row with reference to specific data

[Neel_07ee55]

Hello All,

Below is my input table:

[TABLE="class: grid, width: 301, align: left"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Compound Folder Name[/TD]
[TD]File name[/TD]
[/TR]
[TR]
[TD]electrical documents[/TD]
[TD]a.pdf[/TD]
[/TR]
[TR]
[TD]electrical documents[/TD]
[TD]b.dwg[/TD]
[/TR]
[TR]
[TD]electrical documents[/TD]
[TD]c.pdf[/TD]
[/TR]
[TR]
[TD]electrical documents[/TD]
[TD]d.xyz[/TD]
[/TR]
[TR]
[TD]mechanical documents[/TD]
[TD]a1.pdf[/TD]
[/TR]
[TR]
[TD]mechanical documents[/TD]
[TD]b1.dwg[/TD]
[/TR]
[TR]
[TD]instrumentation documents[/TD]
[TD]a2.pdf[/TD]
[/TR]
[TR]
[TD]instrumentation documents[/TD]
[TD]b2.dwg
[/TD]
[/TR]
</tbody>[/TABLE]

















I want result as stated below:

[TABLE="class: grid, width: 526"]
<colgroup><col><col><col span="3"></colgroup><tbody>[TR]
[TD]Compound Folder Name[/TD]
[TD]File name1[/TD]
[TD]File name2[/TD]
[TD]File name3[/TD]
[TD]File name4[/TD]
[/TR]
[TR]
[TD]electrical documents[/TD]
[TD]a.pdf[/TD]
[TD]b.dwg[/TD]
[TD]c.pdf[/TD]
[TD]d.xyz[/TD]
[/TR]
[TR]
[TD]mechanical documents[/TD]
[TD]a1.pdf[/TD]
[TD]b1.dwg[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]instrumentation documents[/TD]
[TD]a2.pdf[/TD]
[TD]b2.dwg[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Note:

Count of "Compound Folder Name"/"File Name" may vary.

Kindly provide macro/VBA program for the same.

Thank you & Regards,
Neelkumar Solanki
Electrical Engineer
India

 

[MickG]

Try this, for results on sheet2.

[COLOR="Navy"]Sub[/COLOR] MG21Sep19
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, Temp [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
[COLOR="Navy"]If[/COLOR] Not Dn.Value = Temp [COLOR="Navy"]Then[/COLOR] c = c + 1: Ac = 0
    [COLOR="Navy"]With[/COLOR] Sheets("Sheet2")
        .Range("A" & c) = Dn.Value
            Ac = Ac + 1
        .Range("A" & c).Offset(, Ac) = Dn.Offset(, 1).Value
        .Range("A1").Offset(, Ac) = "Name" & Ac
    [COLOR="Navy"]End[/COLOR] With
Temp = Dn.Value
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Neel_07ee55]

Thank you MickG it worked...fine....

Appreciate your quick response...

 

[MickG]

You're welcome