Solved

Excel if count duplicate output unique sequential number

Posted on 2014-12-28
8
248 Views
Last Modified: 2014-12-29
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.
0
Comment
Question by:petewinter
8 Comments
 
LVL 7

Expert Comment

by:tomfarrar
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

by:Michael74
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
            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
 
LVL 45

Assisted Solution

by:aikimark
aikimark earned 75 total points
ID: 40521082
Sort by column A and use subtotaling or use pivot table
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 21

Expert Comment

by:Ejgil Hedegaard
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)))

Open in new window

ID-duplicates.xlsx
0
 

Author Comment

by:petewinter
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

by:
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

by:petewinter
ID: 40522754
Perfect. Many thanks
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now