Solved

Excel if count duplicate output unique sequential number

Posted on 2014-12-28
8
271 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

810 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