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!

 

[oldbrewer]

try and format it better - sometimes putting borders on the cells helps - or use dots eg apple.....1......fruit

how do you get cuke 2 veg ?

 

[Fluff]

You can use remove duplicates on the Data tab

 

[bkaehny]

How is this?
Apple...1...Fruit
Apple...2...Fruit
Cuke...1...Veg
Cuke...8...Green

Becomes:
Apple...1...Fruit
Cuke...2...Veg
...8...Green

In this case, the first column only has two distinct values, so the third row in the first column ends up being blank. The second and third columns both have three distinct values, so those both end up with three rows of data.

 

[bkaehny]

You can use remove duplicates on the Data tab

I've thought about that, but I would have to use Remove Duplicates on each column independently, and my sheet has 200 columns in it. I'm assuming a macro could call the Remove Duplicates function and loop through each column until it's done, but I have no idea how to do that.

 

[Fluff]

How about

Sub removedupes()
    
    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

 

[bkaehny]

How about

Sub removedupes()
    
    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

That works well if my data is not inside a table, but I realize I forgot to mention that my data is inside a table. It errors on the line "Columns(i).RemoveDuplicates 1, xlYes." Sorry for forgetting to mention this in my original post.

 

[Fluff]

In that case I suspect that you will need to convert the table to a range.
AFAIK you can't delete individual cells in a table, only entire rows

 

[bkaehny]

...yep, you're right. I don't know why I didn't think of that. Thank you for your help!

 

[Fluff]

Glad we could help & thanks for the feedback