List numeric duplicates (including permutations) of data in column

B

britwill

New Member
Joined
Mar 31, 2014
Messages
5
I have a column that has duplicate information in its rows. The rows contain a list of 4 digit numbers in each cell.

An example of three of the cells is:

B1: 1970,1971,1972,1974
B2: 1970,1917,1972,1975
B3: 1970,1971,1927,1976

I want a function OR <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help;">vba</acronym> macro to go thru the column and find then list the duplicates (including permutations).

So output of this will be:
1970, 1971, 1917, 1972, 1927

Thanks for any help
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Sort by date Sort by votes
britwill,

Welcome to the MrExcel forum.

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?


list the duplicates (including permutations)
Click to expand...

I was not sure what the results should look like?


You posted what your results should look like below in cell C1.

I read your above quote and thought that you wanted to list the numbers that had duplicates, cell D1.


list the duplicates (including permutations)
Click to expand...

Are you looking for the results in columns E and F?



Excel 2007
BCDEF
11970,1971,1972,19741970, 1971, 1917, 1972, 19271970, 1971, 197219703
21970,1917,1972,197519712
31970,1971,1927,197619722
419741
519171
619751
719271
819761
9
Sheet1
 
Upvote 0
hiker95 said:
britwill,

Welcome to the MrExcel forum.

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?




I was not sure what the results should look like?


You posted what your results should look like below in cell C1.

I read your above quote and thought that you wanted to list the numbers that had duplicates, cell D1.




Are you looking for the results in columns E and F?


Excel 2007
BCDEF
1970,1971,1972,19741970, 1971, 1917, 1972, 19271970, 1971, 1972
1970,1917,1972,1975
1970,1971,1927,1976

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]1970[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1971[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1972[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1974[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1917[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1975[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1927[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1976[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​
Click to expand...
Yes. I am. Thanks. That's awesome!
 
Upvote 0
britwill,

Are you looking for the results in columns E and F?
Click to expand...

Yes. I am. Thanks. That's awesome!


Sample raw data:


Excel 2007
BCD
11970,1971,1972,1974
21970,1917,1972,1975
31970,1971,1927,1976
4
5
6
7
8
9
Sheet1


After the macro:


Excel 2007
BCD
11970,1971,1972,197419703
21970,1917,1972,197519712
31970,1971,1927,197619722
419741
519171
619751
719271
819761
9
Sheet1


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code: 
Sub GetListOfDupes()
' hiker95, 03/31/2014, ME767954
Dim d As Object, s, i As Long, ii As Long
Dim o As Variant
With CreateObject("Scripting.Dictionary")
  .CompareMode = vbTextCompare
  For i = 1 To Range("B" & Rows.Count).End(xlUp).Row
    s = Split(Range("B" & i), ",")
    For ii = LBound(s) To UBound(s)
      If .Exists(s(ii)) Then
        .Item(s(ii)) = .Item(s(ii)) + 1
      Else
        .Add s(ii), 1
      End If
    Next ii
  Next i
  o = Application.Transpose(Array(.Keys, .Items))
End With
Range("C1").Resize(UBound(o, 1), UBound(o, 2)) = o
Columns("C:D").AutoFit
End Sub

You may have to add the Microsoft Scripting Runtime to the References - VBA Project.

With your workbook that contains the above:

Press the keys ALT + F11 to open the Visual Basic Editor

In the VBA Editor, click on:
Tools
References...

Put a checkmark in the box marked
Microsoft Scripting Runtime

Then click on the OK button.

And, exit out of the VBA Editor.

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the GetListOfDupes macro.
 
Upvote 0
Is it a one off? I would use the Text to Columns function splitting on a comma, then I would put all four columns in 1 columns, then I would use the remove duplicates function.

Voila, one distinct list in about 5 seconds.

Edit: ****it, why am I not reading properly today.

Is it a one off? I would use the Text to Columns function splitting on a comma, then I would put all four columns in 1 columns, then I would use a countif formula, anything with 2 or greater is a dupe .
 
Last edited:
Upvote 0
Hiker95-

Apologies are in order, I was not very specific.

Please see below what I am looking for.

I am trying to get duplicates and the permutations of said duplicates.

[TABLE="class: cms_table_outer_border, width: 500, align: center"]
<tbody>[TR]
[TD]Name[/TD]
[TD]4 digit integers[/TD]
[TD][TABLE="class: cms_table, width: 80"]
<tbody>[TR]
[TD="class: cms_table_xl65, width: 80"]Dupe_Perm[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD]Count[/TD]
[/TR]
[TR]
[TD]Bobby[/TD]
[TD]3837, 5801, 2222, 1234[/TD]
[TD]3837[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Susan[/TD]
[TD]3387, 2110, 2543, 4321[/TD]
[TD]5801[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Rick[/TD]
[TD]1805, 3453, 6521, 2011[/TD]
[TD]2222[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]George[/TD]
[TD]0112, 2222, 3333, 3214[/TD]
[TD]1234[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]2110[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

Regards
 
Upvote 0
britwill,

Our results do not match?

Are we missing data in column B?

Can you explain your logic for the results you posted?


Sample data after the new macro:


Excel 2007
ABCD
1Name4 digit integersDupe_PermCount
2Bobby3837, 5801, 2222, 123438370001
3Susan3387, 2110, 2543, 432158010001
4Rick1805, 3453, 6521, 201122220002
5George0112, 2222, 3333, 321412340001
633870001
721100001
825430001
943210001
1018050001
1134530001
1265210001
1320110001
1401120001
1533330001
1632140001
17
Sheet1


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Code: 
Sub GetListOfDupesV2()
' hiker95, 04/02/2014, ME767954
' GetListOfDupes ScriptingDictionary Split oarray - britwill - ME767954 - SDG17.xlsm
Dim d As Object, s, i As Long, ii As Long
Dim o As Variant
With CreateObject("Scripting.Dictionary")
  .CompareMode = vbTextCompare
  For i = 2 To Range("B" & Rows.Count).End(xlUp).Row
    s = Split(Range("B" & i), ",")
    For ii = LBound(s) To UBound(s)
      If .Exists(s(ii)) Then
        .Item(s(ii)) = .Item(s(ii)) + 1
      Else
        .Add s(ii), 1
      End If
    Next ii
  Next i
  o = Application.Transpose(Array(.Keys, .Items))
End With
Range("C2").Resize(UBound(o, 1), UBound(o, 2)) = o
Range("C2").Resize(UBound(o, 1)).NumberFormat = "0000"
Columns("C:D").AutoFit
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the GetListOfDupesV2 macro.
 
Upvote 0
hiker95 said:
britwill,

Our results do not match?

Are we missing data in column B?

Can you explain your logic for the results you posted?


Sample data after the new macro:


Excel 2007
ABCD
1Name4 digit integersDupe_PermCount
2Bobby3837, 5801, 2222, 123438370001
3Susan3387, 2110, 2543, 432158010001
4Rick1805, 3453, 6521, 201122220002
5George0112, 2222, 3333, 321412340001
633870001
721100001
825430001
943210001
1018050001
1134530001
1265210001
1320110001
1401120001
1533330001
1632140001
17
Sheet1


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Code: 
Sub GetListOfDupesV2()
' hiker95, 04/02/2014, ME767954
' GetListOfDupes ScriptingDictionary Split oarray - britwill - ME767954 - SDG17.xlsm
Dim d As Object, s, i As Long, ii As Long
Dim o As Variant
With CreateObject("Scripting.Dictionary")
  .CompareMode = vbTextCompare
  For i = 2 To Range("B" & Rows.Count).End(xlUp).Row
    s = Split(Range("B" & i), ",")
    For ii = LBound(s) To UBound(s)
      If .Exists(s(ii)) Then
        .Item(s(ii)) = .Item(s(ii)) + 1
      Else
        .Add s(ii), 1
      End If
    Next ii
  Next i
  o = Application.Transpose(Array(.Keys, .Items))
End With
Range("C2").Resize(UBound(o, 1), UBound(o, 2)) = o
Range("C2").Resize(UBound(o, 1)).NumberFormat = "0000"
Columns("C:D").AutoFit
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the GetListOfDupesV2 macro.
Click to expand...

Hiker95-

I'm treating the permutations as duplicates.

ex. 3837 is the same as 3387.
 
Upvote 0
britwill,

I'm treating the permutations as duplicates.

ex. 3837 is the same as 3387.
Click to expand...

I do not have a clue how to solve your request.


Click on the Reply to Thread button, and just put the word BUMP in the thread. Then, click on the Post Quick Reply button, and someone else will assist you.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
1K
AndyEd
A
L
  • Question Question
VBA works on work PC but not home PC
Replies
3
Views
2K
rollis13
rollis13
P
  • Question Question
Advice with VBA Code to Move Files
Replies
3
Views
1K
Fluff
Fluff
rediffusion
  • Question Question
How to pull data from a Sheet to create a Context Menu? How to combine two VBA codes?
Replies
0
Views
994
rediffusion
rediffusion
A
  • Question Question
Web Scraping using VBA
Replies
4
Views
9K
atr140
A

Forum statistics

Threads
1,223,907
Messages
6,175,301
Members
452,633
Latest member
DougMo

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top