Excel if count duplicate output unique sequential number

I watched this video to count duplicates.

https://www.youtube.com/watch?v=PCk6pYQirJg

and if used the formula below.

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

Open in new window


However I want to take it a step further if you can please help...

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.
petewinterAsked:
Who is Participating?
 
Ejgil HedegaardCommented:
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
 
tomfarrarCommented:
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
 
Michael FowlerSolutions 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
            dict.Add Range(INPUT_COL & i).Value, 1
        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

Open in new window

Example.xlsm
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
aikimarkCommented:
Sort by column A and use subtotaling or use pivot table
0
 
Ejgil HedegaardCommented:
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)))

Open in new window

ID-duplicates.xlsx
0
 
petewinterAuthor 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
 
petewinterAuthor Commented:
Perfect. Many thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.