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

O

oldbrewer

Well-known Member

Joined

Apr 11, 2010

Messages

11,012

• Apr 10, 2018

• #2

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 ?

 

Upvote 0

Fluff

MrExcel MVP, Moderator

Joined

Jun 12, 2014

Messages

90,556

Office Version

1. 365

Platform

1. Windows

• Apr 10, 2018

• #3

You can use remove duplicates on the Data tab

 

Upvote 0

B

bkaehny

Board Regular

Joined

Jun 11, 2009

Messages

127

• Apr 10, 2018

• #4

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.

 

Upvote 0

B

bkaehny

Board Regular

Joined

Jun 11, 2009

Messages

127

• Apr 10, 2018

• #5

Fluff said:

You can use remove duplicates on the Data tab

Click to expand...

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.

 

Upvote 0

Fluff

MrExcel MVP, Moderator

Joined

Jun 12, 2014

Messages

90,556

Office Version

1. 365

Platform

1. Windows

• Apr 10, 2018

• #6

How about

Code:

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

 

Upvote 0

B

bkaehny

Board Regular

Joined

Jun 11, 2009

Messages

127

• Apr 10, 2018

• #7

Fluff said:

How about

Code:

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

Click to expand...

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.

 

Last edited: Apr 10, 2018

Upvote 0

Fluff

MrExcel MVP, Moderator

Joined

Jun 12, 2014

Messages

90,556

Office Version

1. 365

Platform

1. Windows

• Apr 10, 2018

• #8

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

 

Upvote 0

B

bkaehny

Board Regular

Joined

Jun 11, 2009

Messages

127

• Apr 10, 2018

• #9

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

 

Upvote 0

Fluff

MrExcel MVP, Moderator

Joined

Jun 12, 2014

Messages

90,556

Office Version

1. 365

Platform

1. Windows

• Apr 10, 2018

• #10

Glad we could help & thanks for the feedback

 

Upvote 0