Sorting Category Hierachy

[Sharid]

Hi

I’m a really stuck, I have a category list in EXCEL. I have a 35 categories, each with sub categories and each sub categories has further sub categories, a total of 6 levels. The amount of data I have to input manually is in about 15164 lines, over 6 columns.

Level 1 = 35
Level 2 = 587
Level 3 = 4995
Level 4 = 7243
Level 5 = 2183
Level 6 = 121

I am really stuck on this one, So far I have been inputting them by hand and it is taking forever. Can this be done with a code.

In this EXAMPLE I have used ONLY 4 columns, code must run over 6 columns, as above Levels = Columns

Please ignore the box around the words, I copied and Pasted the information and it has created some boxes, only the text is important

I need it to go from this
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]

<tbody>
[TD="class: xl63, colspan: 3"]Comics & Magz[/TD]

</tbody>

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]

<tbody>
[TD="class: xl65"]Accessories[/TD]

</tbody>

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]

<tbody>
[TD="class: xl65"]Book Covers[/TD]

</tbody>

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]

<tbody>
[TD="class: xl65"]Book Plates[/TD]

</tbody>

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]

<tbody>
[TD="class: xl65"]Bookmarks[/TD]

</tbody>

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]

<tbody>
[TD="class: xl65"]Collectable[/TD]

</tbody>

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]

<tbody>
[TD="class: xl65"]Audio Books[/TD]

</tbody>

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]

<tbody>
[TD="class: xl65"]Calendars[/TD]

</tbody>

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]

<tbody>
[TD="class: xl65"]Children's & Young Adults[/TD]

</tbody>

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]

<tbody>
[TD="class: xl65"]Children's Annuals[/TD]

</tbody>

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]

<tbody>
[TD="class: xl65"]Fiction[/TD]

</tbody>

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

<tbody>
[TD="class: xl65"]Children's Fiction[/TD]

</tbody>

[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

<tbody>
[TD="class: xl65"]Children's Picture Books[/TD]

</tbody>

[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

<tbody>
[TD="class: xl65"]Comics Graphic Novels[/TD]

</tbody>

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


To This - I have added more catagory items on this one to give you a better picture, I have also coloured the changes so it show better to what I am after.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]Comics & Magz[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Comics & Magz
[/TD]
[TD]Accessories[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Comics & Magz[/TD]
[TD]Accessories
[/TD]
[TD]Book Covers[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Comics & Magz[/TD]
[TD]Accessories
[/TD]
[TD]

<tbody>
[TD="class: xl65"]Book Plates[/TD]

</tbody>

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Comics & Magz[/TD]
[TD]Accessories
[/TD]
[TD]Bookmarks[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Comics & Magz[/TD]
[TD]Collectable[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Comics & Magz[/TD]
[TD]Audio Books[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Comics & Magz[/TD]
[TD]Calendars[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Comics & Magz[/TD]
[TD]Children's & Young Adults[/TD]
[TD]Children's Annuals[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Comics & Magz[/TD]
[TD]Children's & Young Adults
[/TD]
[TD]

<tbody>
[TD="class: xl65"]Fiction[/TD]

</tbody>

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Comics & Magz[/TD]
[TD]Children's & Young Adults[/TD]
[TD]

<tbody>
[TD="class: xl65"] Fiction [/TD]

</tbody>

[/TD]
[TD]Children's Fiction[/TD]
[/TR]
[TR]
[TD]Comics & Magz[/TD]
[TD]Children's & Young Adults[/TD]
[TD]

<tbody>
[TD="class: xl65"] Fiction [/TD]

</tbody>

[/TD]
[TD]Children's Picture Books[/TD]
[/TR]
[TR]
[TD]Comics & Magz[/TD]
[TD]Children's & Young Adults[/TD]
[TD]

<tbody>
[TD="class: xl65"] Fiction [/TD]

</tbody>

[/TD]
[TD]Comics Graphic Novels[/TD]
[/TR]
[TR]
[TD]Cars[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cars
[/TD]
[TD]BMW[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cars
[/TD]
[TD]BMW
[/TD]
[TD]Red[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cars
[/TD]
[TD]BMW[/TD]
[TD]Blue[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cars
[/TD]
[TD]BMW[/TD]
[TD]Black[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cars
[/TD]
[TD]BMW[/TD]
[TD]Black
[/TD]
[TD]Manual[/TD]
[/TR]
[TR]
[TD]Cars
[/TD]
[TD]BMW[/TD]
[TD]Black
[/TD]
[TD]Automatic[/TD]
[/TR]
[TR]
[TD]Cars
[/TD]
[TD]Audi[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cars
[/TD]
[TD]Audi
[/TD]
[TD]Red[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cars
[/TD]
[TD]Audi[/TD]
[TD]Red
[/TD]
[TD]Manual[/TD]
[/TR]
[TR]
[TD]Cars
[/TD]
[TD]Audi[/TD]
[TD]Red
[/TD]
[TD]Automatic[/TD]
[/TR]
[TR]
[TD]Van[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Van[/TD]
[TD]Red[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

​

 

[MickG]

Try this:-
Data assumed to start in "A1".

[COLOR="Navy"]Sub[/COLOR] MG27May40
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, R [COLOR="Navy"]As[/COLOR] Range, Temp [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/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.SpecialCells(xlCellTypeBlanks).Areas
Dn.Value = Dn(1).Offset(-1).Value
    [COLOR="Navy"]For[/COLOR] Ac = 1 To 5
        Temp = ""
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] R [COLOR="Navy"]In[/COLOR] Dn.Offset(, Ac)
            [COLOR="Navy"]If[/COLOR] R.Value <> "" [COLOR="Navy"]Then[/COLOR] Temp = R.Value
                R.Value = Temp
        [COLOR="Navy"]Next[/COLOR] R
    [COLOR="Navy"]Next[/COLOR] Ac
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Sharid]

MickG

This is super, one again you have saved my life.

 

[MickG]

You're welcome