Autonumber based on another column

Options

Hi,


I'd like to create a stable autonumbering system to do the following in column 2:

column 1 Column 2

Apple Apple 1

Pear Pear 1

Apple Apple 2

I need the sheet to be sorted by Column 2, and I also need column 2 to not change if the sorting is accidentally changed. I will be using a form to enter 'Apple and Pear' and other 'fruit'.

Any ideas?

Best Answer

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Answer ✓
    Options

    @Ian Waites

    Create an auto number column called Row ID.

    Use the below formula for Column 2:

    =[column 1]@row + " " + COUNT(COLLECT([column 1]:[column 1], [column 1]:[column 1], [column 1]@row, [Row ID]:[Row ID], <=[Row ID]@row))

Answers

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Answer ✓
    Options

    @Ian Waites

    Create an auto number column called Row ID.

    Use the below formula for Column 2:

    =[column 1]@row + " " + COUNT(COLLECT([column 1]:[column 1], [column 1]:[column 1], [column 1]@row, [Row ID]:[Row ID], <=[Row ID]@row))

  • Ian Waites
    Ian Waites ✭✭✭
    Options

    Leibel,


    Thank you so much. That worked perfectly. I have spent an hour trying to figure this. It would have taken me several years of trial and error to arrive at this solution!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!