Solved

Duplicates

Posted on 2011-09-27
28
222 Views
Last Modified: 2012-06-27
Hello Experts,

Fairly simple request for you Excel experts out there.

Can someone please show me how to compile a formula that will place the duplicates in cell B2 if there are two duplicates, and if there are more than two duplicates put that value in B3

For example, in the attached spreadsheet there are two duplicates 63. I would like a formula that would put 63 in cell B2, however if there were three 63's then put that value in B3.

I hope that makes sense.

Cheers
EE-Illustration.xlsm
0
Comment
Question by:cpatte7372
  • 12
  • 7
  • 3
  • +3
28 Comments
 
LVL 17

Expert Comment

by:andrewssd3
ID: 36708341
What if more than one number in the range was duplicated - would you want both/all numbers to be put somewhere?
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 36708348
In B2 put this formula:

=MAX(COUNTIF($A$2:$A$15,$A2:$A15))

Confirm with Shift + Ctrl + Enter which will add curly brackets at beginning and end.

In your sample returns 3 as there are 3 duplicates of 63.

Thanks
Rob H
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 36708359
Sorry, just re-read; you want the 63 not the number of 63's.

Thanks
Rob H
0
 
LVL 17

Expert Comment

by:andrewssd3
ID: 36708365
To do what I think you're asking (although I can't imagine why you would want to do it), put these two formulas in cells B2 and B3 respectively:
=IF(COUNTIF($A$2:$A$15, "="&MODE($A$2:$A$15))=2,MODE($A$2:$A$15),"")
=IF(COUNTIF($A$2:$A$15, "="&MODE($A$2:$A$15))>2,MODE($A$2:$A$15),"")

Open in new window


This uses MODE, so it will catch the most repeated number, although if the is more than one mode you may not get sensible results.  
0
 
LVL 50

Expert Comment

by:teylyn
ID: 36708471
Please clarify:

in your data sample, the numbers 74, 78 and 98 are duplicates. What would you want to see in B2?
in your data sample, the numbers 25 and 63 are triplicates. What would you want to see in B3?

Your question is not clear.
0
 
LVL 5

Expert Comment

by:GirardAndrew
ID: 36708488
Please check if this is what you need.

If so, the code used is already in the file and can be found below too: Solution-27343074.xlsm
Sub compile_duplicates()
    For Each c In Range("A2:A" & (Cells.SpecialCells(xlCellTypeLastCell).Row)).Cells
        With WorksheetFunction
            colnum = .CountIf(Range("A2:A" & (Cells.SpecialCells(xlCellTypeLastCell).Row)), c)
            yaxis = .Substitute(Left(Cells(1, colnum + 1).Address, .Find("$", Cells(1, colnum + 1).Address, 2)), "$", "")
            xaxis = .CountA(Range(yaxis & ":" & yaxis)) + 1
            Range(yaxis & xaxis) = c
        End With
    Next c
End Sub

Open in new window

0
 
LVL 2

Expert Comment

by:Nero74
ID: 36708563
Hi, this function will give you a list of the duplicates; then you just need to loop through the results and put them in the range you want:

Function DupArray(Src_Range As Range)
Dim Dup() As Variant

i = 0

For Each c In Src_Range
    If WorksheetFunction.CountIf(Src_Range, c) > 1 Then
        ReDim Preserve Dup(i)
        Dup(i) = c
        i = i + 1
    End If
Next c

DupArray = Dup

End Function

Open in new window


Regards
0
 

Author Comment

by:cpatte7372
ID: 36708774
GirardAndrew,

This looks ideal.

Can you please show me how to apply it to the attached spreadsheet.

I've inserted the columns x1 x2 x3 x4

Cheers
EE-DowDuplicates.xlsm
0
 

Author Comment

by:cpatte7372
ID: 36708828
Actually, GirardAndrew, would it be a possible to just have the values that have appeared more once just listed once in the columns? For example, the value 74 appears twice, however I would just like it appear once in the x2 column.

Cheers

Carlton
0
 

Author Comment

by:cpatte7372
ID: 36709326
Hi andrewssd3,

Would it be possible to have your fomula modified to include other numbers that have more than 2 duplicates? For example your formula:

=IF(COUNTIF($A$2:$A$15, "="&MODE($A$2:$A$15))>3,MODE($A$2:$A$15),"") which identify one set of duplicates. In the illustration, there are over two values with 25. The formula will correctly put 25 in the column but there may be other numbers such as 63 which also have more than two 63's. Can the formula include those as well?

Cheers
0
 
LVL 17

Expert Comment

by:andrewssd3
ID: 36709494
Hi - not using the MODE function, because it can only return the most frequently duplicated value in the range.  If there is more than one mode, it seems to return just the first, although I can't find this confirmed in the Microsoft documentation.

A function can only return  more than one value if it's capable of being used as an array function, so I think to do this you will need a VBA macro (as others have suggested here) or a user-defined function that can return an array
0
 

Author Comment

by:cpatte7372
ID: 36709529
ok, Andrew

I will run with GirardAndrew, suggestion. I'm just waiting for him to help modify it as I suggested above

Cheers
0
 
LVL 2

Expert Comment

by:Nero74
ID: 36711480
Hi,
The following code has 2 functions UniqueItems and FindDuplicates.  The FindDuplicates calls the UniqueItems function but you can switch it off using the parameters.  There is also a parameter for sorting the list.

Function FindDuplicates(Src_Range As Range, Optional Unique_list As Boolean = True, Optional Sort_Dups As Boolean = True)
Dim Dup() As Variant
Dim DupsFound As Boolean

i = 0

'Loop through each cell and if the count is greater than 1 there are duplicates
For Each c In Src_Range
    If WorksheetFunction.CountIf(Src_Range, c) > 1 Then
        DupsFound = True
        ReDim Preserve Dup(i)
        Dup(i) = c
        i = i + 1
    End If
Next c

'If there are no duplicates exit function
If DupsFound = False Then
    Exit Function
End If

'Sort array
If Sort_Dups Then
    For lLoop = 0 To UBound(Dup)
        For lLoop2 = lLoop To UBound(Dup)
            If UCase(Dup(lLoop2)) < UCase(Dup(lLoop)) Then
                str1 = Dup(lLoop)
                str2 = Dup(lLoop2)
                Dup(lLoop) = str2
                Dup(lLoop2) = str1
            End If
        Next lLoop2
    Next lLoop
End If

'Reduce to unique list
If Unique_list Then
    FindDuplicates = UniqueItems(Dup, False)
Else
    FindDuplicates = Dup
End If

End Function

Function UniqueItems(ArrayIn, Optional Count As Boolean = True) As Variant
'   Accepts an array or range as input
'   If Count = True or is missing, the function returns the number of unique elements
'   If Count = False, the function returns a variant array of unique elements
    Dim Unique() As Variant ' array that holds the unique items
    Dim Element As Variant
    Dim i As Integer
    Dim FoundMatch As Boolean

'   Counter for number of unique elements
    NumUnique = 0
'   Loop thru the input array
    For Each Element In ArrayIn
        FoundMatch = False
'       Has item been added yet?
        For i = 1 To NumUnique
            If Element = Unique(i - 1) Then
                FoundMatch = True
                Exit For '(exit loop)
            End If
        Next i
AddItem:
'       If not in list, add the item to unique list
        If Not FoundMatch And Not IsEmpty(Element) Then
            ReDim Preserve Unique(NumUnique)
            Unique(NumUnique) = Element
            NumUnique = NumUnique + 1
            
        End If
    Next Element
'   Assign a value to the function
    If Count Then UniqueItems = NumUnique Else UniqueItems = Unique
    
End Function

Open in new window


You can call the FindDuplicates function using the following subroutine, or of course just slot it into your code.  You will need to update the 2 constants at the begin that  tell the code where to output the list of dupliactes and the range of the list to search.  The code at the end shows how you can have the output in the rows or columns:

Sub Get_Duplicates()
Const Output_Dups As String = "F26"
Const Search_Range As String = "c6:c24"

DupArray = FindDuplicates(Range(Search_Range ))

If IsEmpty(DupArray) Then
    Exit Sub
End If

c = Range(Output_Dups ).Column
r = Range(Output_Dups ).Row
 
'rows
Range(Cells(r, c), Cells(r + UBound(DupArray), c)) = WorksheetFunction.Transpose(DupArray)
    
'columns
Range(Cells(r, c), Cells(r, c + UBound(DupArray))) = DupArray

End Sub

Open in new window


Hope this helps.

Nero
0
 
LVL 5

Expert Comment

by:GirardAndrew
ID: 36712956
Hi Carlton,

Sorry for the late reply. Please refer to the file attached or the code below. Basically, I added an IF condition that checks if the value is already in the target column.

Thanks,
GirardAndrew Solution-27343074.xlsm
Sub compile_duplicates()
    For Each c In Range("A2:A" & (Cells.SpecialCells(xlCellTypeLastCell).Row)).Cells
        With WorksheetFunction
            colnum = .CountIf(Range("A2:A" & (Cells.SpecialCells(xlCellTypeLastCell).Row)), c)
            yaxis = .Substitute(Left(Cells(1, colnum + 1).Address, .Find("$", Cells(1, colnum + 1).Address, 2)), "$", "")
            If .CountIf(Range(yaxis & ":" & yaxis), c) < 1 Then
                xaxis = .CountA(Range(yaxis & ":" & yaxis)) + 1
                Range(yaxis & xaxis) = c
            Else
            End If
        End With
    Next c
End Sub

Open in new window

0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:cpatte7372
ID: 36713248
Hi GirardAndrew,

Thanks for the update mate....

Nero, thanks for responding will check it the formula when I get back to my desk. Its looks very interesting..

Cheers guys
0
 

Author Comment

by:cpatte7372
ID: 36713314
Hi Nero,

I'm probably being really dense, but I cut and paste the formula in the illustration spreadsheet but nothing happens.

What am I doing wrong??

Cheers
0
 

Author Comment

by:cpatte7372
ID: 36713421
GirardAndrew,

I tried tweaking your solution to fit my master spreadsheet( please see attached). I change the value from A2 to AW5, but I couldnt get it to work.

Can you please help?


Sub compile_duplicates()
    For Each c In Range("AW5:AW" & (Cells.SpecialCells(xlCellTypeLastCell).Row)).Cells
        With WorksheetFunction
            colnum = .CountIf(Range("A2:A" & (Cells.SpecialCells(xlCellTypeLastCell).Row)), c)
            yaxis = .Substitute(Left(Cells(1, colnum + 1).Address, .Find("$", Cells(1, colnum + 1).Address, 2)), "$", "")
            If .CountIf(Range(yaxis & ":" & yaxis), c) < 1 Then
                xaxis = .CountA(Range(yaxis & ":" & yaxis)) + 1
                Range(yaxis & xaxis) = c
            Else
            End If
        End With
    Next c
End Sub
duplicateUpdate.xlsm
0
 
LVL 5

Expert Comment

by:GirardAndrew
ID: 36715650
Hi Carlton,

You need to update "A2:A" to "AW5:AW" in this line:

 colnum = .CountIf(Range("A2:A" & (Cells.SpecialCells(xlCellTypeLastCell).Row)), c)

colnum is the number of columns from the range of data where you will be putting the value of c.
yaxis determines the column name.
IF condition checks if c value already exists in that target column.
If it does not, then xaxis determines the row number.

But looking at your format, you will still have problems. I asked xaxis to count the number of values in the said yaxis column. This will not work if you have spaces before the said row.

I have modified the code. Please refer to the attached file or the code below. You only need to set values for colStart and rowStart.

Thanks,
Girard Andrew Solution-27343074.xlsm
Sub compile_duplicates()
    colStart = "B"
    rowStart = 9
    cellStart = colStart & rowStart
    For Each c In Range(cellStart & ":" & colStart & (Cells.SpecialCells(xlCellTypeLastCell).Row)).Cells
        With WorksheetFunction
            colnum = .CountIf(Range(cellStart & ":" & colStart & (Cells.SpecialCells(xlCellTypeLastCell).Row)), c)
            If colnum > 0 Then
                yaxis = .Substitute(Left(Cells(1, colnum + Range(colStart & 1).Column).Address, .Find("$", Cells(1, colnum + 1).Address, 2)), "$", "")
                If .CountIf(Range(yaxis & ":" & yaxis), c) < 1 Then
                    xaxis = .CountA(Range(yaxis & rowStart & ":" & yaxis & Cells.SpecialCells(xlCellTypeLastCell).Row)) + rowStart
                    Range(yaxis & xaxis) = c
                Else
                End If
            Else
            End If
        End With
    Next c
End Sub

Open in new window

0
 
LVL 2

Expert Comment

by:Nero74
ID: 36715674
Hi,

Do you have a copy of the file after copying the functions?

Did you change the constants in the Get_Duplicates subroutine that calls the FindDuplicates function?  in your workbook this would need to be:

Const Output_Dups As String = "AX5"
Const Search_Range As String = "AW5:AW17"

I only have Excel 2003 on the machine I'm using at the moment but I have attached an example file.

Regards
Nero
Duplicates-example.xls
0
 

Author Comment

by:cpatte7372
ID: 36715933
GirardAndrew,

I think I managed to get it to work. Can you please show me how make it dynamic, so that I runs automatically without me having to hit F5 or run?

Cheers mate.
0
 

Author Comment

by:cpatte7372
ID: 36715972
GirardAndrew,

As well making the numbers dynamic, there appears to be an issue with column x4 in my spreadsheet. The number appears way down (please see attachment)

Cheers
snapshot.png
0
 

Author Comment

by:cpatte7372
ID: 36716327
Hi Experts,

In Girards absence, I wonder if someone could show me how make the following work dynamically, without hitting F5 or clicking run?

Sub compile_duplicates()
    colStart = "AW"
    rowStart = 5
    cellStart = colStart & rowStart
    For Each c In Range(cellStart & ":" & colStart & (Cells.SpecialCells(xlCellTypeLastCell).Row)).Cells
        With WorksheetFunction
            colnum = .CountIf(Range(cellStart & ":" & colStart & (Cells.SpecialCells(xlCellTypeLastCell).Row)), c)
            If colnum > 0 Then
                yaxis = .Substitute(Left(Cells(1, colnum + Range(colStart & 1).Column).Address, .Find("$", Cells(1, colnum + 1).Address, 2)), "$", "")
                If .CountIf(Range(yaxis & ":" & yaxis), c) < 1 Then
                    xaxis = .CountA(Range(yaxis & rowStart & ":" & yaxis & Cells.SpecialCells(xlCellTypeLastCell).Row)) + rowStart
                    Range(yaxis & xaxis) = c
                Else
                End If
            Else
            End If
        End With
    Next c
End Sub


Cheers
0
 
LVL 5

Expert Comment

by:GirardAndrew
ID: 36753768
Hi Carlton,

I will try to replicate the issue with the x4.

As for making it dynamic, we can move the said procedure and attach it to an event (such as keypress, open workbook, select worksheet, etc). That way we won't be hogging the resources like in the case of a formula.

Thanks,
Girard Andrew
0
 
LVL 5

Accepted Solution

by:
GirardAndrew earned 500 total points
ID: 36763634
Hi Carlton,

I cannot replicate your issue. In fact, I think there is no issue there. As you can see, the number 11222 is already in column x3. Therefore, there is no way for it to be in column x4.

I also attached the code to the Worksheet_Change event. Please refer to the file attached or the code below.

Thanks,
Girard Andrew

 
Private Sub Worksheet_Change(ByVal Target As Range)
    colStart = "B"
    rowstart = 9
    cellStart = colStart & rowstart
    Range(Cells(rowstart, (Range(cellStart).Column + 1)).Address & ":" & Cells.SpecialCells(xlCellTypeLastCell).Address).Clear
    For Each c In Range(cellStart & ":" & colStart & (Cells.SpecialCells(xlCellTypeLastCell).Row)).Cells
        With WorksheetFunction
            colnum = .CountIf(Range(cellStart & ":" & colStart & (Cells.SpecialCells(xlCellTypeLastCell).Row)), c)
            If colnum > 0 Then
                yaxis = .Substitute(Left(Cells(1, colnum + Range(colStart & 1).Column).Address, .Find("$", Cells(1, colnum + 1).Address, 2)), "$", "")
                If .CountIf(Range(yaxis & ":" & yaxis), c) < 1 Then
                    xaxis = .CountA(Range(yaxis & rowstart & ":" & yaxis & Cells.SpecialCells(xlCellTypeLastCell).Row)) + rowstart
                    Range(yaxis & xaxis) = c
                Else
                End If
            Else
            End If
        End With
    Next c
End Sub

Open in new window

Solution-27343074.xlsm
0
 
LVL 5

Expert Comment

by:GirardAndrew
ID: 36764576
Hi Carlton,

I added a line that clears out values in the target data range area, which is determined as:

Start Column = colStart + 1
Start Row = rowStart
End Column and Row = address of xlCellTypeLastCell

Thanks,
Girard Andrew
0
 

Author Comment

by:cpatte7372
ID: 36814363
Girard,

Its working brilliantly now.

Cheers mate.
0
 

Author Closing Comment

by:cpatte7372
ID: 36814365
Excellent.

Cheers
0
 
LVL 5

Expert Comment

by:GirardAndrew
ID: 36895437
Thank you.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
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 …
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…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

746 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

14 Experts available now in Live!

Get 1:1 Help Now