I think the best way to show the problem is with an example. Column A is what i have now, and column B is what I would want.
| A | B |
|---|---|
| 1 | 1 |
| 1 | 1 |
| 2 | 2 |
| 2 | 2 |
| 5 | 3 |
| 5 | 3 |
| 5 | 3 |
| 8 | 4 |
| 8 | 4 |
| 9 | 5 |
| 9 | 5 |
| 14 | 6 |
| 14 | 6 |
| 17 | 7 |
| 17 | 7 |
| 17 | 7 |
Sorted list of random repeated numbers to sorted list of repeated and continuos numbers in google sheets
Viewed 35
2 Answers
Update: Based on your comment, use this formula
=ArrayFormula(IF(ISNUMBER(A1:A), VLOOKUP(A1:A, {UNIQUE(A1:A), ArrayFormula(RANK(UNIQUE(A1:A), UNIQUE(A1:A), 1))}, 2, 0), ""))
Previous answer: Have you already used the SORT formula?
Try =SORT(A1:A, 1, 1) in cell B1
Assuming your data starts at row 2 through row 10 column A. In B2 :
=arrayformula(1/COUNTIF($A$2:$A$10,$A$2:$A$10))
in C2
=sumproduct(($B$1:$B1)*($A$1:$A1<A2))+1
Related