Restrict Excel data in two columns VBA Skills Required

[snapyshubham]

Hello Everyone Can someone help me convert below data from:

[TABLE="width: 1216"]
<colgroup><col width="64" span="19" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl66, width: 64"]JR[/TD]
[TD="class: xl66, width: 64"]TR1[/TD]
[TD="class: xl66, width: 64"]TR2[/TD]
[TD="class: xl66, width: 64"]TR3[/TD]
[TD="class: xl66, width: 64"]TR4[/TD]
[TD="class: xl66, width: 64"]TR5[/TD]
[TD="class: xl66, width: 64"]TR6[/TD]
[TD="class: xl66, width: 64"]TR7[/TD]
[TD="class: xl66, width: 64"]TR8[/TD]
[TD="class: xl66, width: 64"]TR9[/TD]
[TD="class: xl66, width: 64"]TR10[/TD]
[TD="class: xl66, width: 64"]TR11[/TD]
[TD="class: xl66, width: 64"]TR12[/TD]
[TD="class: xl66, width: 64"]TR13[/TD]
[TD="class: xl66, width: 64"]TR14[/TD]
[TD="class: xl66, width: 64"]TR15[/TD]
[TD="class: xl66, width: 64"]TR16[/TD]
[TD="class: xl66, width: 64"]TR17[/TD]
[TD="class: xl66, width: 64"]TR18[/TD]
[/TR]
[TR]
[TD="class: xl66"]1[/TD]
[TD="class: xl66"]a1[/TD]
[TD="class: xl66"]a2[/TD]
[TD="class: xl66"]a3[/TD]
[TD="class: xl66"]a4[/TD]
[TD="class: xl66"]a5[/TD]
[TD="class: xl66"]a6[/TD]
[TD="class: xl66"]a7[/TD]
[TD="class: xl66"]a8[/TD]
[TD="class: xl66"]a9[/TD]
[TD="class: xl66"]a10[/TD]
[TD="class: xl66"]a11[/TD]
[TD="class: xl66"]a12[/TD]
[TD="class: xl66"]a13[/TD]
[TD="class: xl66"]a14[/TD]
[TD="class: xl66"]a15[/TD]
[TD="class: xl66"]a16[/TD]
[TD="class: xl66"]a17[/TD]
[TD="class: xl66"]a18[/TD]
[/TR]
[TR]
[TD="class: xl66"]2[/TD]
[TD="class: xl66"]b1[/TD]
[TD="class: xl66"]b2[/TD]
[TD="class: xl66"]b3[/TD]
[TD="class: xl66"]b4[/TD]
[TD="class: xl66"]b5[/TD]
[TD="class: xl66"]b6[/TD]
[TD="class: xl66"]b7[/TD]
[TD="class: xl66"]b8[/TD]
[TD="class: xl66"]b9[/TD]
[TD="class: xl66"]b10[/TD]
[TD="class: xl66"]b11[/TD]
[TD="class: xl66"]b12[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
</tbody>[/TABLE]

to

[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl66, width: 64"]JR[/TD]
[TD="class: xl66, width: 64"]TR[/TD]
[/TR]
[TR]
[TD="class: xl67"]1[/TD]
[TD="class: xl66"]a1[/TD]
[/TR]
[TR]
[TD="class: xl67"]1[/TD]
[TD="class: xl66"]a2[/TD]
[/TR]
[TR]
[TD="class: xl67"]1[/TD]
[TD="class: xl66"]a3[/TD]
[/TR]
[TR]
[TD="class: xl67"]1[/TD]
[TD="class: xl66"]a4[/TD]
[/TR]
[TR]
[TD="class: xl67"]1[/TD]
[TD="class: xl66"]a5[/TD]
[/TR]
[TR]
[TD="class: xl67"]1[/TD]
[TD="class: xl66"]a6[/TD]
[/TR]
[TR]
[TD="class: xl67"]1[/TD]
[TD="class: xl66"]a7[/TD]
[/TR]
[TR]
[TD="class: xl67"]1[/TD]
[TD="class: xl66"]a8[/TD]
[/TR]
[TR]
[TD="class: xl67"]1[/TD]
[TD="class: xl66"]a9[/TD]
[/TR]
[TR]
[TD="class: xl67"]1[/TD]
[TD="class: xl66"]a10[/TD]
[/TR]
[TR]
[TD="class: xl67"]1[/TD]
[TD="class: xl66"]a11[/TD]
[/TR]
[TR]
[TD="class: xl67"]1[/TD]
[TD="class: xl66"]a12[/TD]
[/TR]
[TR]
[TD="class: xl67"]1[/TD]
[TD="class: xl66"]a13[/TD]
[/TR]
[TR]
[TD="class: xl67"]1[/TD]
[TD="class: xl66"]a14[/TD]
[/TR]
[TR]
[TD="class: xl67"]1[/TD]
[TD="class: xl66"]a15[/TD]
[/TR]
[TR]
[TD="class: xl67"]1[/TD]
[TD="class: xl66"]a16[/TD]
[/TR]
[TR]
[TD="class: xl67"]1[/TD]
[TD="class: xl66"]a17[/TD]
[/TR]
[TR]
[TD="class: xl67"]1[/TD]
[TD="class: xl66"]a18[/TD]
[/TR]
[TR]
[TD="class: xl67"]2[/TD]
[TD="class: xl66"]b1[/TD]
[/TR]
[TR]
[TD="class: xl67"]2[/TD]
[TD="class: xl66"]b2[/TD]
[/TR]
[TR]
[TD="class: xl67"]2[/TD]
[TD="class: xl66"]b3[/TD]
[/TR]
[TR]
[TD="class: xl67"]2[/TD]
[TD="class: xl66"]b4[/TD]
[/TR]
[TR]
[TD="class: xl67"]2[/TD]
[TD="class: xl66"]b5[/TD]
[/TR]
[TR]
[TD="class: xl67"]2[/TD]
[TD="class: xl66"]b6[/TD]
[/TR]
[TR]
[TD="class: xl67"]2[/TD]
[TD="class: xl66"]b7[/TD]
[/TR]
[TR]
[TD="class: xl67"]2[/TD]
[TD="class: xl66"]b8[/TD]
[/TR]
[TR]
[TD="class: xl67"]2[/TD]
[TD="class: xl66"]b9[/TD]
[/TR]
[TR]
[TD="class: xl67"]2[/TD]
[TD="class: xl66"]b10[/TD]
[/TR]
[TR]
[TD="class: xl67"]2[/TD]
[TD="class: xl66"]b11[/TD]
[/TR]
[TR]
[TD="class: xl67"]

​

[/TD]
[TD="class: xl66"]

[/TD]
[/TR]
</tbody>[/TABLE]




This is just a sample data but actual sheet has like 100's of JR and 100's of TR and doing it manually is very painful.

Thanks in advance.

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG02Dec03
[COLOR="Navy"]Dim[/COLOR] Lst [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] nRng [COLOR="Navy"]As[/COLOR] Range
c = 2
Application.ScreenUpdating = False
Sheets("Sheet2").Range("A1:B1") = Array("JR", "TR")
[COLOR="Navy"]Set[/COLOR] Rng = Range("A2", Range("A" & Rows.Count).End(xlUp))

[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    Lst = Cells(Dn.Row, Columns.Count).End(xlToLeft).Column
        [COLOR="Navy"]Set[/COLOR] nRng = Cells(Dn.Row, 2).Resize(, Lst - 1)
  [COLOR="Navy"]With[/COLOR] Sheets("Sheet2")
    .Cells(c, 2).Resize(nRng.Count).Value = Application.Transpose(nRng)
    .Cells(c, 1).Resize(nRng.Count).Value = Dn.Value
    c = c + Lst - 1
 [COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]Next[/COLOR] Dn

Application.ScreenUpdating = True
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[snapyshubham]

It worked Mick.. Thanks a lot for the help...

 

[MickG]

You're welcome