How to find sequence of a column with duplicates
Category: [Excel] Demo Available
How to find sequence of a column with duplicates
Requirement:
The user has a list of trips with duplicate sequence numbers and am trying to calculated the sequential order based on 1,2,3. etc. The user has tried countif and sumproduct and other formulas but am stumped. The user is trying to calculate the "Desired result". The user can't sort the data (unfortunately) and also can't use a pivot table, The user needs this to be a formula or even VBA function.
| Trip | Seq | Revised Seq (Desired Result) |
| 100 | 1 | 1 |
| 101 | 1 | 1 |
| 101 | 2 | 2 |
| 101 | 6 | 3 |
| 101 | 8 | 4 |
| 101 | 2 | 2 |
| 102 | 1 | 1 |
| 103 | 2 | 1 |
| 103 | 4 | 2 |
| 103 | 7 | 3 |
| 103 | 4 | 2 |
| 103 | 2 | 1 |
Solution:
The formula on "C2" (first cell in count of unique column): "{=SUM(--(FREQUENCY(IF($A$2:A2=A2,$B$2:B2),$B$2:B2)>0))}" (NOTE THAT THIS IS AN ARRAY FORMULA) This will give you how many unique entries exist based on trip AND seq cell values.
| TRIP | SEQ | COUNT OF UNIQUE |
| 100 | 1 | 1 |
| 101 | 1 | 1 |
| 101 | 2 | 2 |
| 101 | 6 | 3 |
| 101 | 8 | 4 |
| 101 | 2 | 4 |
| 102 | 1 | 1 |
| 103 | 2 | 1 |
| 103 | 4 | 2 |
| 103 | 7 | 3 |
| 103 | 4 | 3 |
| 103 | 2 | 3 |
Next, add the following columns:
Insert an INDEX column before COUNT OF UNIQUE column and merge TRIP&SEQ values to this column [=(A2&B2)]
Add a last column "DESIRED COUNT" and use the following formula in that column's cells: "=VLOOKUP(C2,$C$2:$D$13,2,0)" (basically look up for the values in "count of unique" column that corresponds to "index" column values. Because vlookup is designed to bring the first corresponding value in the event of a duplicate entries, you will be able to retrieve the "original sequence".
| TRIP | SEQ | INDEX | COUNT OF UNIQUE | DESIRED COUNT |
| 100 | 1 | 1001 | 1 | 1 |
| 101 | 1 | 1011 | 1 | 1 |
| 101 | 2 | 1012 | 2 | 2 |
| 101 | 6 | 1016 | 3 | 3 |
| 101 | 8 | 1018 | 4 | 4 |
| 101 | 2 | 1012 | 4 | 2 |
| 102 | 1 | 1021 | 1 | 1 |
| 103 | 2 | 1032 | 1 | 1 |
| 103 | 4 | 1034 | 2 | 2 |
| 103 | 7 | 1037 | 3 | 3 |
| 103 | 4 | 1034 | 3 | 2 |
| 103 | 2 | 1032 | 3 | 1 |
Obtained from the OzGrid Help Forum.
Solution provided by antifragile.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to VBA code to count duplicates FAST |
| How to remove duplicates from dropdown list combobox |
| How to create a macro to move duplicates |
| How to copy a sheet and rename from a list, ignore duplicates |
| How to use IndexMatch formula that ignores duplicates |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.