Return all non duplicates from a range

[Pumper]

Hi All,

I would like to return all values from a range that are not duplicated (not unique values just those appearing once in the range).

The length of the columns my vary and each column can be deferent lengths.

Either a formula or VBA would work, have seen some other VBA but they delete the entire row.
Random example here:
Before: Sheet1

Desired Result : Sheet2

Any assistance on this would be much appreciated

Thanks

 

[jkpieterse]

This might help: Get a list of unique values in Excel & extract unique rows

 

[Peter_SSs]

Are you still using Excel 2016? If you happen to have 365 then you can do it like this.

24 10 03.xlsm
	A	B	C	D
1	Apple	Orange		Apple
2	Orange	Pear		Banana
3	Pear	Banana
4	Orange	Orange
5	Pear
Non-dupes

Cell Formulas
Range	Formula
D1:D2	D1	=UNIQUE(TOCOL(A1:B5,1),,1)
Dynamic array formulas.

 

[Alex Blakenburg]

And if you really want a VBA Option give this a try.

Sub GetUniqueValues()

    Dim shtSrc As Worksheet, shtDest As Worksheet
    Dim rngSrc As Range, rngDest As Range
    Dim arrSrc As Variant, arrOut As Variant
    Dim dictSrc As Object, dictKey As String, vKey As Variant
    Dim i As Long, j As Long, iOut As Long
    
    Set shtSrc = Worksheets("Sheet1")
    Set shtDest = Worksheets("Sheet2")
    
    Set rngSrc = shtSrc.Range("A1").CurrentRegion
    arrSrc = rngSrc.Value
    
    Set rngDest = shtDest.Range("A1")
    rngDest.CurrentRegion.ClearContents
    
    Set dictSrc = CreateObject("Scripting.dictionary")
    dictSrc.CompareMode = vbTextCompare
    
    ' Load details range into Dictionary
    For j = 1 To UBound(arrSrc, 2)
        For i = 1 To UBound(arrSrc, 1)
            dictKey = arrSrc(i, j)
            
            If dictKey <> "" Then
                If Not dictSrc.exists(dictKey) Then
                    dictSrc(dictKey) = 1
                Else
                    dictSrc(dictKey) = dictSrc(dictKey) + 1
                End If
            End If
        Next i
    Next j
        
    ' Load distinct unique values into output array
    ReDim arrOut(1 To dictSrc.Count, 1 To 1)
        
    For Each vKey In dictSrc.keys
        If dictSrc(vKey) = 1 Then
            iOut = iOut + 1
            arrOut(iOut, 1) = vKey
        End If
    Next vKey
    
    ' Output results
    rngDest.Resize(iOut).Value = arrOut
End Sub

 

[HighAndWilder]

Hi All,

I would like to return all values from a range that are not duplicated (not unique values just those appearing once in the range).

The length of the columns my vary and each column can be deferent lengths.

Either a formula or VBA would work, have seen some other VBA but they delete the entire row.
Random example here:
Before: Sheet1
View attachment 117629

Desired Result : Sheet2
View attachment 117630

Any assistance on this would be much appreciated

Thanks

Another option that assumes that there is space to the right of the block of values.

Public Sub subGetUniqueValues()
Dim rng As Range
Dim i As Integer
Dim rngFor As Range
  
  Sheets("Sheet2").Range("A:A").Cells.ClearContents

  With Sheets("Sheet1").Range("A1").CurrentRegion
    
    Set rngFor = .Offset(0, .Columns.Count + 2)
    
    rngFor.Formula2 = "=COUNTIF(" & .Address & "," & _
      .Cells(1).Address(0, 0) & ")"
     
    For Each rng In rngFor
      If rng.Value = 1 Then
        Sheets("Sheet2").Cells(i + 1, 1).Value = _
          rng.Offset(0, -2 + (-.Columns.Count)).Value
        i = i + 1
      End If
    Next rng
    
  End With
  
  rngFor.Clear
      
End Sub

 

[Pumper]

Thanks all for taking the time to look at this! (sorry late reply, assume different time zones)

Peter that worked perfectly, even though I am still on 2016, so thank you very much.

Think in this instance I will go with the formula as I have a bunch of other macros proceeding this but will definitely save those for future reference.

 

[Peter_SSs]

Peter that worked perfectly, even though I am still on 2016, so thank you very much.

If my formula worked for you then I don't believe that you are using Excel 2016 since the TOCOL function is only available in Microsoft 365 and excel for the web ...

.. and the UNIQUE function is also not available in Excel 2016:

If I go to File -> Account this is what I get. What is it for you? .. or are you using Excel for the web?

 

[Pumper]

If my formula worked for you then I don't believe that you are using Excel 2016 since the TOCOL function is only available in Microsoft 365 and excel for the web ...

View attachment 117679


.. and the UNIQUE function is also not available in Excel 2016:

View attachment 117680

If I go to File -> Account this is what I get. What is it for you? .. or are you using Excel for the web?

View attachment 117681

Well there you go...I just asked our IT department and we switched over about a month ago so as per every other time you have helped Peter you are correct same as what you have above.

Thanks again!

 

[Peter_SSs]

Well there you go...I just asked our IT department and we switched over about a month ago so as per every other time you have helped Peter you are correct same as what you have above.

Thanks again!

Cheers.
.. and I note that you have updated your profile to reflect your actual current version.