Return of Duplicate sets with number of times their are Duplicated.

[serge]

Hi everyone,

I looking for a formula or UDF that will extract from D6:M100 all the Duplicate and how many time each set have been duplicated, ( very important ),Starting in cell : D102 Number of time duplicated and in cell E102 the set number, then below in cell D103 Number of time duplicated and in cell E103 the set number and so on.

Thank you very much, Serge.

See example portion below :

Excel Workbook
	D	E	F	G
6	02 06 11 16 39	01 06 12 21 39	01 02 09 19 36	01 06 13 21 39
7	02 06 13 21 39	01 03 12 16 39	02 03 09 19 36	01 12 21 36 39
8	01 02 09 21 36	02 12 21 36 39	02 03 11 19 36	03 06 12 21 39
9	01 09 12 19 36	03 06 12 21 39	02 06 11 19 36	01 06 12 19 39
10	02 09 12 21 36	02 06 13 21 39	02 09 11 16 36	02 09 11 16 39
11	03 09 12 21 36	02 06 09 21 39	01 09 12 16 39	02 11 16 36 39
12	01 03 09 21 36	01 03 09 21 36	02 03 12 16 39	02 09 11 21 36
13	02 06 09 21 36	01 09 12 21 36	03 06 12 16 39	02 03 09 21 36
14	02 03 09 21 36	02 03 09 16 39	03 12 16 19 39	02 09 13 21 36
15	01 09 12 21 36	03 09 12 16 36	01 02 09 21 36	02 03 09 19 36
16	03 06 09 21 36		02 06 09 21 36	01 02 09 21 36
17				02 06 09 21 36
18				02 09 11 16 36
19				02 09 19 21 36
20
21	2	02 06 13 21 39
22	3	01 02 09 21 36
23	4	Etc
24
Sheet

 

[Peter_SSs]

Not a formula or UDF, but I think does what you want.

Sub Count_Duplicate_Sets()
  Dim d As Object
  Dim a As Variant, itm As Variant
  
  Set d = CreateObject("Scripting.Dictionary")
  a = Range("D6:M100").Value
  For Each itm In a
    If Not IsEmpty(itm) Then d(itm) = d(itm) + 1
  Next itm
  With Range("D102").Resize(d.Count, 2)
    .Value = Application.Transpose(Array(d.Items, d.keys))
    .Columns(1).Replace What:=1, Replacement:="", LookAt:=xlWhole
    On Error Resume Next
    .SpecialCells(xlBlanks).EntireRow.Delete
    On Error GoTo 0
  End With
End Sub

 

[serge]

Thank you Peter for the code, I really appreciate your help, but can you explain how to put it in excel and what formula to use, sorry I'm really bad at it.

 

[Peter_SSs]

1. With your workbook active press Alt+F11 to bring up the vba window.
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Press Alt+F8 to bring up the Macro dialog
6. Select the 'Count_Duplicate_Sets' macro & click ‘Run’
7. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm) if using Excel 2007 or later.

 

[serge]

Thank you very much Peter, it works perfect.
Serge.

 

[Peter_SSs]

No problem.