Can a macro reduce a dataset to only the unique values per column?

[bkaehny]

Hello all,

I have a data set with about 200 columns and 10,000 rows. Here is a sample:
ColA ColB ColC
Apple 1 Fruit
Apple 2 Fruit
Cuke 1 Veg
Cuke 8 Green

The result set I would like is:
ColA ColB ColC
Apple 1 Fruit
Cuke 2 Veg
8 Green

I want to do this because I'm going to take each resulting column of distinct values and use them to populate a different sheet's data validation lists. Can a macro (or some other built-in function) do this? If so, could someone please guide me in the right direction? Thanks in advance!

 

[bkaehny]

One last note to close this out: I found some code to convert the table to a range. https://www.mrexcel.com/forum/excel-questions/367870-vba-convert-table-range.html

So my end result is that I have code to import data, convert it to a range, then remove duplicates column by column.

Sub ImportConvertRemoveDupes()
'
' ImportConvertRemoveDupes Macro
'


'
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
        "OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=SERVER;Use Procedure for Prepare=1;Auto Tra" _
        , _
        "nslate=True;Packet Size=4096;Workstation ID=NAME;Use Encryption for Data=False;Tag with column collation when possible=False;In" _
        , "itial Catalog=DataWarehouse"), Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdTable
        .CommandText = Array( _
        """DataWarehouse"".""dbo"".""TABLE""")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .SourceConnectionFile = _
        "C:\ODC connection files\FILE.odc"
        .ListObject.DisplayName = _
        "TableNAME"
        .Refresh BackgroundQuery:=False
    End With
    
Dim rList As Range
 
With Worksheets("Sheet1").ListObjects("TableNAME")
    Set rList = .Range
    .Unlist                           ' convert the table back to a range
End With


With rList
    .Interior.ColorIndex = xlColorIndexNone
    .Font.ColorIndex = xlColorIndexAutomatic
    .Borders.LineStyle = xlLineStyleNone
End With


    Dim UsdCols As Long
    Dim i As Long
    
    UsdCols = Cells(1, Columns.Count).End(xlToLeft).Column
    For i = 1 To UsdCols
      Columns(i).RemoveDuplicates 1, xlYes
    Next i
    
End Sub