2

I want each row in the table to have unique number.

Here is my story:
1. I create table and give IDs to rows - 1, 2, 3, 4, 5.
2. I sort and filter rows by values in other columns.
3. I add new row and want to give number to it. I do not see that next number is 6.

Is there any way to automate the process?

pnuts
  • 58,317
  • 11
  • 87
  • 139
polina-c
  • 6,245
  • 5
  • 25
  • 36

4 Answers4

1

With =ROW() - but if you want the results to remain attached to the rows even with sorting it would be necessary to select Copy and Paste Special Values the IDs that have already been 'taken'.

pnuts
  • 58,317
  • 11
  • 87
  • 139
1

As implied in other answers, if you use any formula to generate an ID, it will be re-evaluated whenever you modify the list (e.g. using sort or inserting a new row in the middle). To assign an ID that persists, it needs to somehow become a raw value in a cell rather than the result of a formula. Two approaches for achieving that are:

  1. Put a formula in one column to generate IDs, but then use Copy and Paste Values to put the results of the formula into another column. Use that second column as the persistent ID rather than the first.
  2. Write some VBA to generate an ID and place the value into the ID cell. This could then be linked to a button, perhaps labelled "Add new row".

The actual generation of a unique ID can be as simple as getting the row number or as sophisticated as an RFC4122 Universally Unique ID (see this other SO topic).

Community
  • 1
  • 1
MattClarke
  • 1,647
  • 1
  • 11
  • 32
1

Take any empty column, for example column L is empty. Write 1 in L1 and extend it to the end of your file. Then copy and paste that column wherever you need it.

Raj Rajput
  • 37
  • 1
  • 8
0

if your data looks like this:

    A     B
__________________________________
1 | 1    
2 | 3
3 | 5
4 | 4
5 | 2
6 | ???

and you want a formula to put in A6, that would automatically be 6?

=MAX($A$1:$A5)+1

user3616725
  • 3,485
  • 1
  • 18
  • 27