Duplicate Values only on ListBox

[splinter288]

Bit left field perhaps,
Basically we have a table containing some 960 lines of information, only one column contains 'unique ID'. Information is updated via a simple UserForm.

Same userform contains a couple of reports (interesting stats more than anything). What I would like to do is have a dynamic(?) ListBox that shows any duplicates contained in my one unique ID column (column A). Or perhaps a table on another sheet that the ListBox refers back to?
Unusually in our operation it is ok to have duplicated unique values, provided we review these regularly. Hence the need to have a view showing these records (taking no action) so an appropriate employee can review and make a decision from there.

Hopefully that makes sense!

[georgiboy]

Welcome to the forum, Here is one idea:

Private Sub UserForm_Initialize()    
    Dim srchRng As Range, rCell As Range
    Dim nCol As New Collection
    
    Set srchRng = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)


    For Each rCell In srchRng.Cells
        If Application.CountIf(Range("A:A"), rCell.Value) > 1 Then
            On Error Resume Next
            nCol.Add rCell.Value, CStr(rCell.Value)
            On Error GoTo 0
        End If
    Next rCell
    
    Me.ListBox1.Clear
    For x = 1 To nCol.Count
        Me.ListBox1.AddItem nCol(x)
    Next x


End Sub

Hope this helps

[splinter288]

Hope this helps

Thank you for your suggestion,
Appears to work but it does mean that my primary data in ListBox1 is no longer appearing. (This new code is to populate ListBox4). Any suggestion to avoid this?

[georgiboy]

In my code change every instance of ListBox1 to ListBox4.

If you already have a "UserForm_Initialize" sub that populates ListBox1 then you may want to work the above code into your current code.

Hope this helps

[splinter288]

Thank you georgiboy, should have been obvious to change the reference.. thank you!
Is there a way to have the ListBox return all of the line data, not just the column A data?

[abrar.1p3]

Hi everyone,
I too has a similar request, but a little different, in my case i want to take out the Unique values from the list of duplicates jobs# & copy the unique jobs# into another column, & then copy the respective status into another column, as shown in the attached Excel ,"Example" sheet


Note: Unique status will be updated only if all the duplicate jobs# has the respective status , else it will be blank


i would appreciate if you could provide me some help on this
pls. find the excel file in the attachment!


Thanking you in anticipation
Abrar

[abrar.1p3]

Hi there,
I have a request where i want to take out the Unique values from the list of duplicates jobs# & copy the unique jobs# into another column, & then copy the respective status into another column, as shown in the attached Excel ,"Example" sheet

Note: Unique status will be updated only if all the duplicate jobs# has the respective status , else it will be blank

i would appreciate if you could provide me some help(VBA) on this
pls. find the excel file in the attachment!

Thanking you in anticipation
Abrar