Categorize values into multiple columns [VBA]
I am fairly new to vba and I am amazed of how much you can automate in excel.
In this post I am going to categorize values from a list into unique columns.I am sure there are more efficent ways to accomplish this task using better written code. In a year from now I hope I´ll be laughing at this post.
Before:
After:
The code
Sub Categorizedatatocolumns()
Dim rng As Range
Dim dest As Range
Dim vrb As Boolean
Dim i As Integer
Set rng = Sheets("Sheet1").Range("A4")
vrb = False
Do While rng <> ""
Set dest = Sheets("Sheet1").Range("A20")
Do While dest <> ""
If rng.Value = dest.Value Then
vrb = True
End If
Set dest = dest.Offset(0, 1)
Loop
If vrb = False Then
dest.Value = rng.Value
dest.Font.bold = True
End If
vrb = False
Set rng = rng.Offset(1, 0)
Loop
Set rng = Sheets("Sheet1").Range("A4")
Do While rng <> ""
Set dest = Sheets("Sheet1").Range("A20")
Do While dest <> ""
If rng.Value = dest.Value Then
i = 0
Do While dest <> ""
Set dest = dest.Offset(1, 0)
i = i + 1
Loop
Set rng = rng.Offset(0, 1)
dest.Value = rng.Value
Set rng = rng.Offset(0, -1)
Set dest = dest.Offset(-i, 0)
End If
Set dest = dest.Offset(0, 1)
Loop
Set rng = rng.Offset(1, 0)
Loop
End Sub
Download excel tutorial file
Remember to backup your excel workbook, you can´t undo macros.
Categorize-data-into-multiple-columns.xls
(Excel 97-2003 Workbook *.xls)
You need to enable macros.
Recommended posts
Lookup multiple values in different columns and return multiple values
Jason C asks: I have a set of data, like the one you used in the original example that also […]
Lookup multiple values in different columns and return a single value
S.Babu asks: Dear Oscar, I m working on the below table. ORDER MODEL MATERIAL QTY STATUS BOM a s6 1 […]
Extract unique distinct values from a multi-column cell range
Question: I have cell values spanning over several columns and I want to create a unique list from that range. […]
One Response to “Categorize values into multiple columns [VBA]”
Leave a Reply
How to add a formula to your comment:
<code>your formula</code>
Remember to convert less than and larger than signs to html character entities before you post your comment.
How to add VBA code to your comment:
[vb 1="vbnet" language=","]
VBA code
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org
Add picture link to comment.
){kind=link}
i am new with vba , i want to modify above mentioned code so that it Categorize Unique values into multiple columns