Two columns unique distinct values to listbox

[diegoasm11]

I have two columns and I need to get the unique value, the columns are in different sheets.

I attach an example, but there is an error, it shows me all the values ​​and only the number 7 and 5 should appear in the list box

thanks a lot

[jindon]

Are sure about what you are asking?
1)

I have two columns and I need to get the unique value, the columns are in different sheets.

There's only one sheet.

2)You are asking
Two columns unique distinct values to listbox

I attach an example, but there is an error, it shows me all the values ​​and only the number 7 and 5 should appear in the list box

What do you mean by unique distinct values?

[diegoasm11]

1 IS AN EXAMPLE, ASSUME COLUMN A IS ON SHEET 1 AND COLUMN B IS ON SHEET 2

1	1
2	2
3	3
4	4
5
6	6
7
8	8
9	9
10	10

2 in the userform1 , in the list box appears all the numbers, but I need Appears the Number 5 and 7, they are the only unduplicated numbers

[Trebor76]

Hi diegoasm11,

Based on your posted workbook this will do the job:

Option Explicit
Private Sub UserForm_Initialize()

    Dim rngDataRange As Range, rngMyCell As Range
    Dim lngLastRow As Long
    
    Application.ScreenUpdating = False
    
    With Sheets("Hoja1")
        lngLastRow = .Range("A:B").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        Set rngDataRange = .Range("A1:B" & lngLastRow)
        For Each rngMyCell In rngDataRange
            If Len(rngMyCell) > 0 Then
                If Application.WorksheetFunction.CountIf(rngDataRange, rngMyCell) = 1 Then
                    Me.ListBox1.AddItem rngMyCell
                End If
            End If
        Next rngMyCell
    End With
    
    Application.ScreenUpdating = True

End Sub

Though my solution works, if jindon does post a solution I would use his

Regards,

Robert

[diegoasm11]

Hi diegoasm11,

Based on your posted workbook this will do the job:

Option Explicit
Private Sub UserForm_Initialize()

    Dim rngDataRange As Range, rngMyCell As Range
    Dim lngLastRow As Long
    
    Application.ScreenUpdating = False
    
    With Sheets("Hoja1")
        lngLastRow = .Range("A:B").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        Set rngDataRange = .Range("A1:B" & lngLastRow)
        For Each rngMyCell In rngDataRange
            If Len(rngMyCell) > 0 Then
                If Application.WorksheetFunction.CountIf(rngDataRange, rngMyCell) = 1 Then
                    Me.ListBox1.AddItem rngMyCell
                End If
            End If
        Next rngMyCell
    End With
    
    Application.ScreenUpdating = True

End Sub

Though my solution works, if jindon does post a solution I would use his

Regards,

Robert

thanks, but if the range are in different sheets suppose sheet1 sheet2

[jindon]

What if there is another 5 in sheet1?

Do you need 5,5,7 or 5, 7?

[diegoasm11]

What if there is another 5 in sheet1?

Do you need 5,5,7 or 5, 7?

example
1. Sheet1 numbers 1 2 3 4 5 6 7 8 9 10
sheet2 numbers 1 2 3 4 6 8 9 10

the userform in the list box show numbersAttachment 724995

[mohadin]

Hi
As I understand

Private Sub UserForm_Initialize()
    Dim v, v2, e
    With Sheets("Hoja1")
        v = Application.Transpose(.Range("A1:a" & .Cells(Rows.Count, 1).End(xlUp).Row))
    End With
    With Sheets("Sheet1")
        v2 = Application.Transpose(.Range("A1:a" & .Cells(Rows.Count, 1).End(xlUp).Row))
    End With
    v = Split(Join(v, "#") & "#" & Join(v2, "#"), "#")
    With CreateObject("scripting.dictionary")
        .comparemode = 1
        For Each e In v
            If Not .exists(e) Then
                .Add e, 1
            Else
                .Remove e
            End If
        Next
        If .Count Then Me.ListBox1.List = Application.Transpose(.keys)
    End With
End Sub

[diegoasm11]

Hi
As I understand

Private Sub UserForm_Initialize()
    Dim v, v2, e
    With Sheets("Hoja1")
        v = Application.Transpose(.Range("A1:a" & .Cells(Rows.Count, 1).End(xlUp).Row))
    End With
    With Sheets("Sheet1")
        v2 = Application.Transpose(.Range("A1:a" & .Cells(Rows.Count, 1).End(xlUp).Row))
    End With
    v = Split(Join(v, "#") & "#" & Join(v2, "#"), "#")
    With CreateObject("scripting.dictionary")
        .comparemode = 1
        For Each e In v
            If Not .exists(e) Then
                .Add e, 1
            Else
                .Remove e
            End If
        Next
        If .Count Then Me.ListBox1.List = Application.Transpose(.keys)
    End With
End Sub

thank you very much the code works

[diegoasm11]

Hi
As I understand

Private Sub UserForm_Initialize()
    Dim v, v2, e
    With Sheets("Hoja1")
        v = Application.Transpose(.Range("A1:a" & .Cells(Rows.Count, 1).End(xlUp).Row))
    End With
    With Sheets("Sheet1")
        v2 = Application.Transpose(.Range("A1:a" & .Cells(Rows.Count, 1).End(xlUp).Row))
    End With
    v = Split(Join(v, "#") & "#" & Join(v2, "#"), "#")
    With CreateObject("scripting.dictionary")
        .comparemode = 1
        For Each e In v
            If Not .exists(e) Then
                .Add e, 1
            Else
                .Remove e
            End If
        Next
        If .Count Then Me.ListBox1.List = Application.Transpose(.keys)
    End With
End Sub

Hi again, if I want sheet one to be the parent list and sheet two to change

example

hoja1	hoja2
1	1
2	2
3	7
4	4
5	9
6	6

In column 1 of sheet 2, the number 3 and 5 are needed, but in list 1 the values ​​3 5 7 9 appear, since 7 9 are what is missing in column 1 of sheet 1, I need them to appear only what is missing from column 1 sheet 1 in column 1 sheet 2

Captura.PNG

should be

Captura.PNG

thanks

[jindon]

My understanding.

Private Sub UserForm_Initialize()
    Dim x, LR As Long
    With Sheets("sheet1")
        LR = .Range("a" & Rows.Count).End(xlUp).Row
        x = .Evaluate("transpose(unique(filter(a1:a" & LR & ",iserror(match(a1:a" & LR & ",'sheet2'!a:a,0)),"""")))")
    End With
    If UBound(x) > -1 Then Me.ListBox1.List = x
End Sub

[mohadin]

You are welcome
And thank you for the feedback
Be happy & safe

[kvsrinivasamurthy]

In E2 then copy down.

=IFERROR(AGGREGATE(15,6,Hoja1!$A$1:$A$10/((Hoja1!$A$1:$A$10<> "")*(COUNTIF(Sheet1!$C$1:$C$11,Hoja1!$A$1:$A$10)=0)),ROWS($E$2:$E2)),IFERROR(AGGREGATE(15,6,Sheet1!$C$1:$C$11/((Sheet1!$C$1:$C$11<> "")*(COUNTIF(Hoja1!$A$1:$A$10,Sheet1!$C$1:$C$11)=0)),ROWS($E$2:$E2)-SUMPRODUCT(1*(COUNTIF(Sheet1!$C$1:$C$11,Hoja1!$A$1:$A$10)=0))),""))

[jindon]

Private Sub UserForm_Initialize()
    Dim x, LR As Long
    With Sheets("hoja1")
        LR = .Range("a" & Rows.Count).End(xlUp).Row
        x = .Evaluate("transpose(unique(filter(a1:a" & LR & _
        ",iserror(match(a1:a" & LR & ",'hoja2'!a:a,0)),"""")))")
    End With
    If UBound(x) > -1 Then Me.ListBox1.List = x
End Sub

[diegoasm11]

Private Sub UserForm_Initialize()
    Dim x, LR As Long
    With Sheets("hoja1")
        LR = .Range("a" & Rows.Count).End(xlUp).Row
        x = .Evaluate("transpose(unique(filter(a1:a" & LR & _
        ",iserror(match(a1:a" & LR & ",'hoja2'!a:a,0)),"""")))")
    End With
    If UBound(x) > -1 Then Me.ListBox1.List = x
End Sub

dont work , types fail

[mohadin]

Private Sub UserForm_Initialize()
    Dim v, v2, e
    With Sheets("Hoja1")
        v = Application.Transpose(.Range("A1:a" & .Cells(Rows.Count, 1).End(xlUp).Row))
    End With
    With Sheets("Hoja2")
        v2 = Application.Transpose(.Range("A1:a" & .Cells(Rows.Count, 1).End(xlUp).Row))
    End With
    With CreateObject("scripting.dictionary")
        .comparemode = 1
        For Each e In v
            If Not .exists(e) Then
                .Add e, 1
             End If
        Next
        For Each e In v2
            If .exists(e) Then
                .Remove e
             End If
        Next
        If .Count Then Me.ListBox1.List = Application.Transpose(.keys)
    End With
End Sub

[diegoasm11]

Private Sub UserForm_Initialize()
    Dim v, v2, e
    With Sheets("Hoja1")
        v = Application.Transpose(.Range("A1:a" & .Cells(Rows.Count, 1).End(xlUp).Row))
    End With
    With Sheets("Hoja2")
        v2 = Application.Transpose(.Range("A1:a" & .Cells(Rows.Count, 1).End(xlUp).Row))
    End With
    With CreateObject("scripting.dictionary")
        .comparemode = 1
        For Each e In v
            If Not .exists(e) Then
                .Add e, 1
             End If
        Next
        For Each e In v2
            If .exists(e) Then
                .Remove e
             End If
        Next
        If .Count Then Me.ListBox1.List = Application.Transpose(.keys)
    End With
End Sub

amaizing code thanks

[mohadin]

Welcome any time

[jindon]

Then you must change your profile telling exact excel version that you are using.

It is working on O365.