Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# Duplicates

Posted on 2011-09-27
Medium Priority
237 Views
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
Question by:cpatte7372
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 12
• 7
• 3
• +3

LVL 17

Expert Comment

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 33

Expert Comment

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 33

Expert Comment

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

Thanks
Rob H
0

LVL 17

Expert Comment

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),"")
``````

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

ID: 36708471

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?

0

LVL 5

Expert Comment

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
``````
0

LVL 2

Expert Comment

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
``````

Regards
0

Author Comment

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

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

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

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

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

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
'       If not in list, add the item to unique list
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
``````

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
``````

Hope this helps.

Nero
0

LVL 5

Expert Comment

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
``````
0

Author Comment

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

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

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.

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

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
``````
0

LVL 2

Expert Comment

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

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

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

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

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

GirardAndrew earned 2000 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
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
``````
Solution-27343074.xlsm
0

LVL 5

Expert Comment

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

ID: 36814363
Girard,

Its working brilliantly now.

Cheers mate.
0

Author Closing Comment

ID: 36814365
Excellent.

Cheers
0

LVL 5

Expert Comment

ID: 36895437
Thank you.
0

## Featured Post

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custoâ€¦
###### Suggested Courses
Course of the Month10 days, 23 hours left to enroll