Excel if count duplicate output unique sequential number

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.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
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
Solutions ConsultantCommented:
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
Commented:
Sort by column A and use subtotaling or use pivot table
0
Commented:
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 Commented:
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
Commented:
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

Experts Exchange Solution brought to you by