Automatically numbering rows in a large sheet of data

[brother_reynolds]

Hi everyone,

I've been learning a lot about excel recently from this site while working on a large database of comparative linguistics.

I am basically writing out the IPA (phonetic spelling) of a number of different words, for a number of different languages. My spreadsheet looks like this (but a lot bigger, with a lot more languages):

[TABLE="width: 500"]
<tbody>[TR]
[TD]Number[/TD]
[TD]English[/TD]
[TD]Language[/TD]
[TD]IPA[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ant[/TD]
[TD]a[/TD]
[TD]moːc[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ant[/TD]
[TD]b[/TD]
[TD]hmoc[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ant[/TD]
[TD]c[/TD]
[TD]mɔːc[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ant[/TD]
[TD]d[/TD]
[TD]moːc[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]arrow[/TD]
[TD]a[/TD]
[TD]kam[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]arrow[/TD]
[TD]b[/TD]
[TD]kam[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]arrow[/TD]
[TD]c[/TD]
[TD]kaːm[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]arrow[/TD]
[TD]d[/TD]
[TD]gam[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]bamboo[/TD]
[TD]a[/TD]
[TD]tuh[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]bamboo[/TD]
[TD]b[/TD]
[TD]dʔɔh[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]bamboo[/TD]
[TD]c[/TD]
[TD]blɔː[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]bamboo[/TD]
[TD]d[/TD]
[TD]ɟɛh[/TD]
[/TR]
</tbody>[/TABLE]

However, I have to add in new words as I go along from time to time. Let's say I want to add in "ashes". What I want is for the spreadsheet to automatically pick up that I've added a new word, and adjust the number column accordingly. For example:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Number[/TD]
[TD]English[/TD]
[TD]Language[/TD]
[TD]IPA[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ant[/TD]
[TD]a[/TD]
[TD]moːc[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ant[/TD]
[TD]b[/TD]
[TD]hmoc[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ant[/TD]
[TD]c[/TD]
[TD]mɔːc[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ant[/TD]
[TD]d[/TD]
[TD]moːc[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]arrow[/TD]
[TD]a[/TD]
[TD]kam[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]arrow[/TD]
[TD]b[/TD]
[TD]kam[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]arrow[/TD]
[TD]c[/TD]
[TD]kaːm[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]arrow[/TD]
[TD]d[/TD]
[TD]gam[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]ashes[/TD]
[TD]a[/TD]
[TD]buh[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]ashes[/TD]
[TD]b[/TD]
[TD]pʰʊh[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]ashes[/TD]
[TD]c[/TD]
[TD]bɔh[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]ashes[/TD]
[TD]d[/TD]
[TD]bɔh[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]bamboo[/TD]
[TD]a[/TD]
[TD]tuh[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]bamboo[/TD]
[TD]b[/TD]
[TD]dʔɔh[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]bamboo[/TD]
[TD]c[/TD]
[TD]blɔː[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]bamboo[/TD]
[TD]d[/TD]
[TD]ɟɛh[/TD]
[/TR]
</tbody>[/TABLE]

I have dabbled a bit with macros (adding new rows for new languages en masse), and I'm guessing the easiest way to do it would be by using a macro. Ideally, I'd like to be able to cut and paste words from different parts of the document as well (if I think the order needs changing), and for the numbers to simply update. Maybe something that says that if the word in the "English" column changes, then the number should also change, and repeat itself, until the word in the "English" column changes again, and so on.

Any help would be appreciated! I hope this all makes sense.

 

[Fazza]

maybe a formula like this in row 2 & copy down


=IF(ROW()=2,1,A1+(B2<>B1))

 

[brother_reynolds]

That's awesome! It works great, but it will require me to expand each time I add a new word. I'll open in up to the floor for any automatic approaches (am I being greedy?).

Thanks

 

[Fazza]

VBA then,

Range("A2").Value2 = 1
With Range("A1").CurrentRegion.Columns(1).Offset(2)
  .Resize(.Rows.Count - 2).FormulaR1C1 = "=R[-1]C+(R[-1]C2<>RC2)"
End With

 

[Michael M]

Darn...too quick Fazza
I was going with non formula approach
If your inserting the rows manually, Copy / paste from another source, into say column "B", then try

Sub MM1()
Dim lr As Long, r As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
For r = 2 To lr - 1
    If Range("B" & r).Value <> Range("B" & r + 1).Value Then
        Range("A" & r + 1).Value = Range("A" & r).Value + 1
    Else:
        Range("A" & r + 1).Value = Range("A" & r).Value
    End If
Next r
End Sub

 

[brother_reynolds]

Thanks Fazza - it works great. It's still not 100% automatic (I have to run the macro each time I want to update it), but I'm super happy to do that (only takes 5 seconds). I don't even know if an automatic and instant solution exists, but in any case, I'm happy with it now!

Michael - thanks for your solution. Even though I don't understand all of it, it looks like what I would've been looking to do. What do you mean when you say you were going for the "non-formula" approach? And how does yours differ from Fazza's in a practical sense?

Thanks guys

 

[Michael M]

Fazza has used a formula to insert the next number....mine doesn't...thats about it !
Maybe this pasted into the Sheet module will make it auto for you.

Private Sub worksheet_Change(ByVal target As Range)
Dim lr As Long, r As Long
Application.ScreenUpdating = False
lr = Cells(Rows.Count, "A").End(xlUp).Row
If Intersect(target, Range("A2:A" & lr)) Is Nothing Then Exit Sub
Application.EnableEvents = False
For r = 2 To lr - 1
    If Range("B" & r).Value <> Range("B" & r + 1).Value Then
        Range("A" & r + 1).Value = Range("A" & r).Value + 1
    Else:
        Range("A" & r + 1).Value = Range("A" & r).Value
    End If
Next r
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub

 

[brother_reynolds]

Thanks Michael.
(Stupid question time) How can I actually get that to run? It won't show up as a macro (because of "private" I'm guessing). I pasted it into the Sheet module, and don't know what to do after that. I'm a bit new to this stuff - sorry!

 

[Michael M]

Simply Insert new rows into the sheet in question...as long as they are within the current range of cells
So, for example, if you have 500 rows of data and you need to insert 4 rows at say row 100..
Right click on the row 100 (row header) where it says 100...and then select Insert copied cells.....the code will do the rest automatically

 

[brother_reynolds]

Haha obviously it doesn't need running (as it's automatic). I surprise myself sometimes :/

Your code works great for what I've described. However, I won't use it because sometimes I just have to insert a single row (when one language has two words), and then copy/paste the surrounding information. When I use your code it live changes the numbers, which means I can't do a quick copy/paste of the rows below to duplicate the information.

I think I'll just stick with Fazza's at the moment.
Thanks so much for that though!