Solved

Excel if count duplicate output unique sequential number

Posted on 2014-12-28
248 Views
I watched this video to count duplicates.

and if used the formula below.

``````=IF(COUNTIF(\$A\$2:A1,D1)=1,1,0)
``````

Instead of outputting "1" if the cell is unique and "0" if it's not I want to create a unique id for each group of duplicate rows.

i.e. If row A1 and A6 I want the output to be the same "unique" number. So can group duplicate rows together.

Hope this makes sense.
0
Question by:petewinter

LVL 7

Expert Comment

ID: 40520942
Not sure how to do that as requested, but you could filter on "0", and then sort on the duplicate field to get the duplicate records together.  - Tom
0

LVL 23

Assisted Solution

Michael74 earned 75 total points
ID: 40521030
I would use VBA for this

``````Sub UniqueWithID()
Dim i As Long
Dim dict As Object
Const INPUT_COL As String = "A"
Const OUTPUT_COL As String = "C"

Set dict = CreateObject("Scripting.Dictionary")

For i = 2 To Range(INPUT_COL & Rows.Count).End(xlUp).row
If dict.exists(Range(INPUT_COL & i).Value) Then
If dict(Range(INPUT_COL & i).Value) = 1 Then
dict(Range(INPUT_COL & i).Value) = CInt(1000 * Rnd())
End If
Else
End If
Next

For i = 2 To Range(INPUT_COL & Rows.Count).End(xlUp).row
Range(OUTPUT_COL & i).Value = dict(Range(INPUT_COL & i).Value)
Next

End Sub
``````
Example.xlsm
0

LVL 45

Assisted Solution

aikimark earned 75 total points
ID: 40521082
Sort by column A and use subtotaling or use pivot table
0

LVL 21

Expert Comment

ID: 40521085
Check if this is what you want.

Values in A2 down.
Formula in B2, copy down
Single values will get a 0, and the duplicates numbers from 1 up.
``````=IF(COUNTIF(\$A:\$A,\$A2)=1,0,IF(COUNTIF(\$A\$1:\$A2,\$A2)=1,MAX(\$B\$1:B1)+1,INDEX(\$B\$1:B1,MATCH(\$A2,\$A\$1:\$A1,0),1)))
``````
ID-duplicates.xlsx
0

Author Comment

ID: 40522535
Michael74 - Thanks, but I am using mac excel 2008 which does not allow a VBA.

Ejgil Hedegaard - Thanks nearly there, but I also want the single values to have a unique id too. What needs to be changed in your formula?
0

LVL 21

Accepted Solution

Ejgil Hedegaard earned 350 total points
ID: 40522611
Use this
=IF(COUNTIF(\$A\$1:\$A2,\$A2)=1,MAX(\$B\$1:B1)+1,INDEX(\$B\$1:B1,MATCH(\$A2,\$A\$1:\$A1,0),1))
0

Author Closing Comment

ID: 40522754
Perfect. Many thanks
0