How to to fill a single column of cells with a number that increments when it finds a cell that spans multiple columns? (Screenshot)

Question

I'm trying to organize some data that was dumped out of a proprietary system into something I can use. In column A, I want to put a 'group by' number.

It dumps this long, spanning row out that separates the different duplicates. Whenever it hits a spanning row, I want it to increment the counter, and delete the spanning row.

The before data is this (I manually keyed A3, A4, A6, A7, A8, A10, A11):

I'd like the after data to look like this:

Any ideas how to accomplish this?

Final solution made possible by comment from below:

Public Sub CleanMeImFilthy()

    Dim intCounter As Integer
    Dim i As Integer

    i = 1

    For intCounter = ActiveSheet.UsedRange.Rows.Count To 1 Step -1
       If Trim(LCase(Left(Cells(intCounter, 1).Value, 14))) = "duplicate key:" Then
         Rows(intCounter).Delete
         i = i + 1
       Else
         Rows.Cells(intCounter, 1) = i
       End If
    Next

End Sub

Answer 1

Try the following piece of VBA:

Public Sub CleanMeImFilthy

  Dim intCounter as Integer

  For intCounter = ActiveSheet.UsedRange.Rows.Count to 1 Step -1
    If Trim(Lcase(Left(Cells(intCounter,1).Value,14))) = "duplicate key:" Then
      Rows(intCounter).Delete
    End If
  Next

End Sub 

All the code does is:

1. Starts at the bottom of column A - where 'bottom' is the last row that has been used.
2. Checks to see if the lower case version of the first 14 characters of text in the current cell, which has been stripped of spaces, equals 'duplicate key:'
3. On the assumption that all rows you need to remove contain this, it then deletes the row

I started from the bottom and worked up as it's cleaner due to not deleting a row, which means the row below you is now you.

As you posted this on Stack Overflow originally, I'm fairly confident you can add this into Excel. If not:

1. Copy the code from here
2. Press ALT+F11 to go into the VB Editor
3. Click Insert | Module on the Menu Bar
4. Click Edit | Paste (CTRL+V)
5. Close the VB Editor
6. On the sheet that contains your dirty data, press ALT+F8 to get a list of available macros
7. Select 'CleanMeImFilthy' and choose Run.