Autonumber based on another column

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
-
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
-
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))
-
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
Categories
Check out the Formula Handbook template!