Excel: Automatically numbering rows in a large sheet of data


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):

Number English Language IPA
1 ant a moːc
1 ant b hmoc
1 ant c mɔːc
1 ant d moːc
2 arrow a kam
2 arrow b kam
2 arrow c kaːm
2 arrow d gam
3 bamboo a tuh
3 bamboo b dʔɔh
3 bamboo c blɔː
3 bamboo d ɟɛh
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:
Number English Language IPA
1 ant a moːc
1 ant b hmoc
1 ant c mɔːc
1 ant d moːc
2 arrow a kam
2 arrow b kam
2 arrow c kaːm
2 arrow d gam
3 ashes a buh
3 ashes b pʰʊh
3 ashes c bɔh
3 ashes d bɔh
4 bamboo a tuh
4 bamboo b dʔɔh
4 bamboo c blɔː
4 bamboo d ɟɛh
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.


This question generated 14 answers. To proceed to the answers, click here.

This thread is current as of August 20, 2014.


For more resources for Microsoft Excel