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

A	B	C	D
Comics & Magz <tbody> </tbody>
	Accessories <tbody> </tbody>
		Book Covers <tbody> </tbody>
		Book Plates <tbody> </tbody>
		Bookmarks <tbody> </tbody>
	Collectable <tbody> </tbody>
	Audio Books <tbody> </tbody>
	Calendars <tbody> </tbody>
	Children's & Young Adults <tbody> </tbody>
		Children's Annuals <tbody> </tbody>
		Fiction <tbody> </tbody>
			Children's Fiction <tbody> </tbody>
			Children's Picture Books <tbody> </tbody>
			Comics Graphic Novels <tbody> </tbody>

<tbody>
</tbody>

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.

A	B	C	D
Comics & Magz
Comics & Magz	Accessories
Comics & Magz	Accessories	Book Covers
Comics & Magz	Accessories	Book Plates <tbody> </tbody>
Comics & Magz	Accessories	Bookmarks
Comics & Magz	Collectable
Comics & Magz	Audio Books
Comics & Magz	Calendars
Comics & Magz	Children's & Young Adults	Children's Annuals
Comics & Magz	Children's & Young Adults	Fiction <tbody> </tbody>
Comics & Magz	Children's & Young Adults	Fiction <tbody> </tbody>	Children's Fiction
Comics & Magz	Children's & Young Adults	Fiction <tbody> </tbody>	Children's Picture Books
Comics & Magz	Children's & Young Adults	Fiction <tbody> </tbody>	Comics Graphic Novels
Cars
Cars	BMW
Cars	BMW	Red
Cars	BMW	Blue
Cars	BMW	Black
Cars	BMW	Black	Manual
Cars	BMW	Black	Automatic
Cars	Audi
Cars	Audi	Red
Cars	Audi	Red	Manual
Cars	Audi	Red	Automatic
Van
Van	Red

<tbody>
</tbody>

​

 

[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