## Categorize values into multiple columns using vba in excel

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.

Text to columns: Split words in a cell (excel array formula)

This blog article describes how to split strings in a cell with space as a delimiting character, like Text to […]User defined function to split words in a cell range into a cell each in excel

This post describes how to split words in a cell range into a cell each using a custom function. I […]### One Response to “Categorize values into multiple columns using vba in excel”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

**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.

**Contact Oscar**

You can contact me through this webpage

i am new with vba , i want to modify above mentioned code so that it Categorize Unique values into multiple columns