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.

Share and Enjoy:
  • Share/Bookmark

Related posts:

  1. Split data across multiple sheets in excel (vba)
  2. Categorize values into multiple columns (excel formulas)
  3. Return unique and duplicate numerical data entries from multiple sheets (3D range) in excel
  4. Automate net asset value (NAV) calculation on your stock portfolio (vba) in excel
  5. Count unique distinct numbers across multiple sheets (3D range) in excel
  6. Consolidate sheets in excel (vba)
  7. Count unique and duplicates text values in a closed workbook in excel (formula)
  8. Create dependent drop down lists containing unique distinct values in excel
  9. Lookup two index columns returning multiple matches in excel
  10. Scan stock markets in excel