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.

 

[Michael M]

keep in mind it only triggers when column "A" changes, not any other column....just thought I'd mention that !!!

 

[Fazza]

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!

I only gave the kernel of the answer. Try this in the worksheet's code module. cheers

Private Sub Worksheet_Change(ByVal Target As Range)


Application.EnableEvents = False


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


Application.EnableEvents = True


End Sub

 

[brother_reynolds]

That's perfect buddy. The numbers don't change until something is entered into column B. Exactly what I was looking for!

It must be nice to know how to do all this stuff. I'll keep learning bit by bit

 

[Fazza]

If you want to learn it, you can. Easiest beginning is using the macro recorder - ALT-T-M-R

Search on line for help. Google finds unlimited information.

A good book is invaluable. I recommend John Walkenbach's 'Power Programming With VBA' series.

Dive in.

All the best