Data Arranging

[Karan001]

Hi Experts,
I have huge data as given below in TABLE-1.I want a way so that the data can be arrange as shown in TABLE-2.

TABLE-1
<table border="0" cellpadding="0" cellspacing="0" width="417"><col style="width: 123pt;" width="164"> <col style="width: 190pt;" width="253"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl68" style="height: 12.75pt; width: 123pt;" height="17" width="164">DATA1</td> <td class="xl68" style="border-left: medium none; width: 190pt;" width="253">DATA2</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl67" style="height: 12.75pt; border-top: medium none;" height="17">SUB-1</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">BLACK</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl67" style="height: 12.75pt; border-top: medium none;" height="17">SUB-1</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">GREEN</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl67" style="height: 12.75pt; border-top: medium none;" height="17">SUB-1</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">RED</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt; border-top: medium none;" height="17">SUB-2</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">BLACK</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt; border-top: medium none;" height="17">SUB-2</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">GREEN</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt; border-top: medium none;" height="17">SUB-2</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">RED</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt; border-top: medium none;" height="17">SUB-2</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">YELLOW</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl67" style="height: 12.75pt; border-top: medium none;" height="17">SUB-3</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">BLACK</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl67" style="height: 12.75pt; border-top: medium none;" height="17">SUB-3</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">GREEN</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl67" style="height: 12.75pt; border-top: medium none;" height="17">SUB-3</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">RED</td> </tr> </tbody></table>

TABLE-2

<table border="0" cellpadding="0" cellspacing="0" width="653"><col style="width: 123pt;" width="164"> <col style="width: 190pt;" width="253"> <col style="width: 83pt;" width="110"> <col style="width: 47pt;" width="62"> <col style="width: 48pt;" width="64"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl69" style="height: 12.75pt; width: 123pt;" height="17" width="164">DATA1</td> <td class="xl69" style="border-left: medium none; width: 190pt;" width="253">DATA2</td> <td class="xl69" style="border-left: medium none; width: 83pt;" width="110">DATA3</td> <td class="xl69" style="border-left: medium none; width: 47pt;" width="62">DATA4</td> <td class="xl69" style="border-left: medium none; width: 48pt;" width="64"> DATA5</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl68" style="height: 12.75pt; border-top: medium none;" height="17">SUB-1</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">BLACK</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">GREEN</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">RED</td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl67" style="height: 12.75pt; border-top: medium none;" height="17">SUB-2</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">BLACK</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">GREEN</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">RED</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">YELLOW</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl68" style="height: 12.75pt; border-top: medium none;" height="17">SUB-3</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">BLACK</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">GREEN</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">RED</td> <td>
</td> </tr> </tbody></table>

Here in want whatever repeat data of table 1 in data-1 column should come in Tabular form as shown in table-2


Regards,
Karan

 

[VoG]

Try with a copy of your table

Sub MakeTable()
Dim LastRow As Long, i As Long, Area As Range
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = LastRow To 2 Step -1
    If Range("A" & i).Value <> Range("A" & i - 1).Value Then
        Rows(i).Insert
    End If
Next i
For Each Area In Columns("A").SpecialCells(xlCellTypeConstants).Areas
    Area(1).Offset(, 1).Resize(, Area.Rows.Count).Value = Application.Transpose(Area.Offset(, 1))
Next Area
Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Columns("C").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

 

[Jared_8878]

Here is an excellent how-to by Aladin Akyurek
http://www.mrexcel.com/forum/showthread.php?t=231882

 

[Karan001]

Hi Peter,
May i know how to use the code provided by you.I dont have knowledge to use that please guide me.



Regards,
Karan

 

[VoG]

First make a copy of your table.

Then press ALT + F11 to open the Visual Basic Editor, select Module from the Insert menu, copy the code and paste into the white space on the right. Press ALT + Q to close the code window.

Press ALT + F8, click on MakeTable then click the Run button.

 

[Karan001]

Hi Peter,
Thank you very much for your magical solution.This really working fine to my problem.

Once again thank you very much.


Regards,
Karan