Extract Duplicate Values from range and Display them

[Trev752]

We have a locker lottery at work, where locker numbers are drawn on a 10-week basis. Within those 10 weeks the same locker number can be drawn multiple times. But, those locker numbers which have been drawn more than once, in those 10 weeks, are not eligible for the next 10 weeks.



I need to extract those duplicate lockers by division and display them separately (Horizontally) so they can be printed.

How can I do this in excel.

 

[mumps]

It is hard to work with a picture. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[Trev752]

It is hard to work with a picture. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

Thanks, I'm not to familiar with BOX.com but I'll give it a try

 

[Trev752]

As mumps suggested here is the link

Locker Lotto Test.xlsx | Powered by Box

app.box.com

 

[mumps]

Try:

Sub findDups()
    Application.ScreenUpdating = False
    Dim v As Variant, e As Variant, arrL1 As Object, arrL2 As Object, col1 As Long, col2 As Long: col1 = 10: col2 = 10
    With Range("J5:O5,J9:O9")
        .ClearContents
        .Interior.ColorIndex = xlNone
    End With
    v = Range("D5:H14").Value
    Set arrL1 = CreateObject("System.Collections.ArrayList")
    Set arrL2 = CreateObject("System.Collections.ArrayList")
    For Each e In v
        If Not arrL1.Contains(e) Then
            arrL1.Add e
        Else
            If Not arrL2.Contains(e) Then arrL2.Add e
        End If
    Next
    arrL2.Sort
    For Each v In arrL2
        If v < 151 Then
            With Cells(5, col1)
                .Value = v
                .Interior.ColorIndex = 3
                .Font.Color = vbWhite
                .Font.Bold = True
            End With
            col1 = col1 + 1
        Else
            With Cells(9, col2)
                .Value = v
                .Interior.ColorIndex = 3
                .Font.Color = vbWhite
                .Font.Bold = True
            End With
            col2 = col2 + 1
        End If
    Next v
    Application.ScreenUpdating = True
End Sub

 

[Trev752]

Thanks Mumps I'll give it a try, much appreciated

 

[Peter_SSs]

What about a formula approach?

Trev752.xlsx
	C	D	E	F	G	H	I	J	K	L	M	N	O
3								North Division
4		Mon	Tue	Wed	Thu	Fri		Lockers		1	to	150
5	Week 1	277	196	98	139	14		14	84	127
6	Week 2	249	189	115	111	160
7	Week 3	123	71	84	145	237		South Division
8	Week 4	63	218	264	239	228		Lockers		151	to	300
9	Week 5	14	11	234	164	194		196	240	277
10	Week 6	84	174	240	74	242
11	Week 7	240	14	68	86	33
12	Week 8	221	69	263	127	254
13	Week 9	127	277	250	117	144
14	Week 10	136	196	278	180	18
Sheet1

Cell Formulas
Range	Formula
J5:L5	J5	=LET(r,D5:H14,v,TOROW(IF(r<151,IF(COUNTIF(r,r)>1,r,""),"")),SORT(UNIQUE(FILTER(v,v<>"",""),1),,,1))
J9:L9	J9	=LET(r,D5:H14,v,TOROW(IF(r>150,IF(COUNTIF(r,r)>1,r,""),"")),SORT(UNIQUE(FILTER(v,v<>"",""),1),,,1))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
N_1	=Sheet1!$D$5:$D$10	J9, J5

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D5:H14	Cell Value	duplicates	text	NO