Add Rows/Headings for Groups of Data

[kpierce]

Everybody –
I have had great success with my status report everyone helped me put together. Thank you, Thank you.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I do have one item that I need your help with.
Column C has the company name in it.
<o></o>
I need to be able to insert headings at the top of every company name.
<o></o>
C1=”ABC”
C2=”ABC”
C3=”XYZ”
C4=”XYZ”
C5=”123”
C6=”123”
<o></o>
I need to read through this and then in Row 1 insert a Row, then read until C changes, then insert another Row, etc.
Output would be:
C1=”Company -ABC”
C2=”ABC”
C3=”ABC”
C4=”Company-XYZ”
C5=”XYZ”
C6=”XYZ”
C7=”Company-123”
C8=”123”
C9=”123”
<o></o>
I have looked at group by and it does not seem to do the trip. Is there a built in way to do this? If not, can someone please help me with a code segment for a Macro/VBA?
<o></o>
Thanks again to everyone for their help.

 

[MickG]

Try this:-

[COLOR=navy]Sub[/COLOR] MG17Aug38
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] nRng [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Set[/COLOR] Rng = Range(Range("C1"), Range("C" & Rows.Count).End(xlUp))
[COLOR=navy]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
    [COLOR=navy]If[/COLOR] Not .Exists(Dn.Value) [COLOR=navy]Then[/COLOR]
        .Add Dn.Value, Dn
        [COLOR=navy]If[/COLOR] nRng [COLOR=navy]Is[/COLOR] Nothing [COLOR=navy]Then[/COLOR]
            [COLOR=navy]Set[/COLOR] nRng = Dn
        [COLOR=navy]Else[/COLOR]
            [COLOR=navy]Set[/COLOR] nRng = Union(nRng, Dn)
        [COLOR=navy]End[/COLOR] If
  [COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR]
nRng.EntireRow.Insert
[COLOR=navy]Dim[/COLOR] K [COLOR=navy]As[/COLOR] Variant
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] K [COLOR=navy]In[/COLOR] .Keys
    .Item(K).Offset(-1) = "Company -" & K
[COLOR=navy]Next[/COLOR] K
[COLOR=navy]End[/COLOR] [COLOR=navy]With[/COLOR]
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[kpierce]

Sweet-
I will give it a try.

thanks for the help.......

 

[kpierce]

Hello Mick -
This is awesome however there is one catch that I can't figure out...
Actually I have very little insight into what that is all doing, but I appreciate it very much.

If there is only 1 row for a given company it seems to insert a blank above, then insert the 'Company-' without moving the other columns down, thus throwing it off.

This scenario works flawlessly!!!
C1=”ABC”
C2="ABC"
C3=”XYZ”
C4=”XYZ”
C5=”123”
C6=”123”
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

However, this does not... (this puts a blank line in Row 4, wiping out the data for XYZ and then in "Company -XYZ" in Row 5 with the data from Row 4..)
C1=”ABC”
C2="ABC"
C4=”XYZ”
C5=”123”
C6=”123”
<o></o>

Anyway you can help me?

Appreciate all the support I can get...

thanks.

 

[MickG]

I think I was trying to be too clever, Try this:-

[COLOR="Navy"]Sub[/COLOR] MG18Aug52
[COLOR="Navy"]Dim[/COLOR] Lst [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Lst = Range("A" & Rows.Count).End(xlUp).Row
    [COLOR="Navy"]For[/COLOR] n = Lst To 2 [COLOR="Navy"]Step[/COLOR] -1
         [COLOR="Navy"]With[/COLOR] Range("A" & n)
            [COLOR="Navy"]If[/COLOR] Not .Value = .Offset(-1).Value [COLOR="Navy"]Then[/COLOR]
                .EntireRow.Insert
                 .Offset(-1).Value = "Company -" & .Value
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]End[/COLOR] With
  [COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]With[/COLOR] Range("A1")
    .EntireRow.Insert
    .Offset(-1).Value = "Company -" & .Value
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[kpierce]

thanks for the help...

Don't know where I would be without this forums help...

Thanks to ALL.