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.
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.
i am new with vba , i want to modify above mentioned code so that it Categorize Unique values into multiple columns