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

[Neel_07ee55]

Hello All,

Below is my input table:

Compound Folder Name	File name
electrical documents	a.pdf
electrical documents	b.dwg
electrical documents	c.pdf
electrical documents	d.xyz
mechanical documents	a1.pdf
mechanical documents	b1.dwg
instrumentation documents	a2.pdf
instrumentation documents	b2.dwg

<colgroup><col><col></colgroup><tbody>
</tbody>

I want result as stated below:

Compound Folder Name	File name1	File name2	File name3	File name4
electrical documents	a.pdf	b.dwg	c.pdf	d.xyz
mechanical documents	a1.pdf	b1.dwg
instrumentation documents	a2.pdf	b2.dwg

<colgroup><col><col><col span="3"></colgroup><tbody>
</tbody>

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