Rearrange values based on category [VBA]
In this post I am going to rearrange values from a list into unique columns.
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
Get 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)
Weekly Blog EMAIL
[newsletter_signup_form id=1]
Welcome! I am Oscar and here to help you out.Feel free to comment and ask Excel questions.
Make sure you subscribe to my newsletter so you don't miss new blog articles.
Related articles
Split data across multiple sheets [VBA]
In this post I am going to show how to create a new sheet for each airplane using vba. The […]
Merge Ranges is an add-in for Excel that lets you easily merge multiple ranges into one master sheet. The Master […]
Question: It's easy to sum a list by multiple criteria, you just use array formula a la: =SUM((column_plane=TRUE)*(column_countries="USA")*(column_producer="Boeing")*(column_to_sum)) But all […]
Count a specific text string in a cell
Table of Contents Count a specific text string in a cell Count text string in a range (case sensitive) Count […]
This article demonstrates a formula that calculates a running total. A running total is a sum that adds new numbers […]
List files in folder and create hyperlinks (VBA)
Table of Contents List files in a folder and create hyperlinks (VBA) How to navigate quickly in a complex workbook […]
This article demonstrates a macro that copies values between sheets. I am using the invoice template workbook. This macro copies […]
This tutorial shows you how to list excel files in a specific folder and create adjacent checkboxes, using VBA. The […]
This blog post demonstrates how to create, populate and change comboboxes (form control) programmatically. Form controls are not as flexible […]
The image above shows an array formula in cell D6 that extracts missing numbers i cell range B3:B7, the lower […]
In this post I am going to show how to create a new sheet for each airplane using vba. The […]
This blog article describes how to split strings in a cell with space as a delimiting character, like Text to […]
Question: How do I divide values equally into groups (3 lists or less)? This post shows you two different approaches, […]
This post describes how to split words in a cell range into a cell each using a custom function. I […]
One Response to “Rearrange values based on category [VBA]”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
i am new with vba , i want to modify above mentioned code so that it Categorize Unique values into multiple columns