Prepare data for Pivot Table – How to split concatenated values?
This article demonstrates a macro that allows you to rearrange and distribute concatenated values across multiple rows in order to make it possible to use the dataset in a Pivot Table.
The animated image above shows how the macro works. Here are the steps to run and use the macro.
- Press Alt+F8 to open the Macro dialog box.
- Press with mouse on "NormalizeData" to select it.
- Press with mouse on "Run" button to run the macro.
- An input box appears asking for a cell range.
- Select the cell range you want to rearrange.
- Press with left mouse button on OK button on dialog box.
- An input box appears asking for a delimiting character, the example shown above has a comma as a delimiting character.
- Press with left mouse button on OK button on dialog box.
- A new sheet is inserted and is populated with values from the cell range you selected, however, each concatenated value has now a row or record on its own. This is necessary if you want to analyze the data in a pivot table.
VBA macros
There are two macros that make this possible, NormalizeData() and Recursive().
'Name macro Sub NormalizeData() 'Dimension variables and declare data types Dim WS As Worksheet Dim DelCh As String Dim r As Single, c As Single Dim rng As Range 'Enable error handling On Error Resume Next 'Show input box and ask for a cell range Set rng = Application.InputBox(Prompt:="Select cell range:", _ Title:="Normalize data", _ Default:=Selection.Address, Type:=8) 'Disable errror handling On Error GoTo 0 'Ask for a delimiting character DelCh = InputBox("Delimiting character:") 'Insert a new worksheet to your workbook Set WS = Sheets.Add 'Don't show changes on screen Application.ScreenUpdating = False 'Save 1 to variable c c = 1 'Iterate from 1 to the number of rows in the selected cell range For r = 1 To rng.Rows.CountLarge 'Start macro Recursive with variables r, c, rng, DelCh and WS Call Recursive(r, c, rng, DelCh, WS) 'Continue with next row Next r 'Change column width to fit content WS.Range("1:" & Rows.CountLarge).EntireColumn.AutoFit 'Show changes to Excel user Application.ScreenUpdating = True End Sub
The second macro is displayed below.
'Name macro as dimension arguments and declare data types Sub Recursive(r As Single, c As Single, rng As Range, DelCh As String, WS As Object) 'Dimension variables and declare data types Dim str As Variant Dim cc As Single, ccc As Single 'Split cell using delimiting character saved to variable DelCh str = Split(rng.Cells(r, c).Value, DelCh) 'Iterate through values in array variable str For i = 0 To UBound(str) 'Check if variable c is equal to 1 If c = 1 Then 'Iterate from 1 to the number of columns in selected cell range For ccc = 1 To rng.Columns.CountLarge 'Check if row number of first empty value in column ccc is larger than variable j If WS.Cells(Rows.Count, ccc).End(xlUp).Row + 1 > j Then 'Save row number of first empty cell in column ccc to variable j j = WS.Cells(Rows.Count, ccc).End(xlUp).Row + 1 End If 'Continue with next column Next ccc 'If c is not equal to 1 then do the following. Else 'Save row number of first empty cell in column c to variable j j = WS.Cells(Rows.Count, c).End(xlUp).Row + 1 End If 'Save value in array variable str to worksheet based on row variable j and column variable c WS.Range("A1").Offset(j - 1, c - 1).Value = str(i) 'Check if c - 1 is greater than 0 (zero) If c - 1 > 0 Then 'Check if cell is empty based on variable j - 1 and c - 2 If WS.Range("A1").Offset(j - 1, c - 2).Value = "" Then 'Save value one row above to cell WS.Range("A1").Offset(j - 1, c - 2).Value = _ WS.Range("A1").Offset(j - 2, c - 2).Value End If End If 'Check if c is equal to the number of columns in the selected cell range If c = rng.Columns.CountLarge Then 'Check if variable i is not equal to 0 (zero) If i <> 0 Then 'Iterate from 1 to the number of columns in the selected cell range. For cc = 1 To rng.Columns.CountLarge - 1 'Save value in cell above to cell based on variable j - 2 and cc - 1 WS.Range("A1").Offset(j - 1, cc - 1).Value = _ WS.Range("A1").Offset(j - 2, cc - 1).Value 'Continue with next column Next cc End If Else 'Run another instance of macro Recursive this time with the next column number Call Recursive(r, c + 1, rng, DelCh, WS) End If Next i End Sub
Where to put the code?
- Copy both macros above.
- Press Alt+F11 to open the Visual Basic Editor.
- Select your workbook in the Project Explorer.
- Press with mouse on "Insert" on the menu.
- Press with mouse on "Module" to create a code module.
- Paste code to code module.
Macro category
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 […]
In this post I am going to show how to create a new sheet for each airplane 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 […]
This article demonstrates how to run a VBA macro using a Drop Down list. The Drop Down list contains two […]
This workbook contains two worksheets, one worksheet shows a calendar and the other worksheet is used to store events. The […]
In this article I will demonstrate how to quickly change chart data range utilizing a combobox (drop-down list). The above […]
What's on this page Copy a file Copy and rename a file Rename a file List files in a folder […]
Excel does not resize columns as you type by default as the image above demonstrates. You can easily resize all […]
This article describes how to create an interactive chart, the user may press with left mouse button on a button […]
Rahul asks: I want to know how to create a vlookup sheet, and when we enter a name in a […]
This article describes how to create a button and place it on an Excel worksheet then assign a macro to […]
Question: hi all, thanks for the great formula/array formula. it works great. lately, i noticed that the array formula will […]
This article demonstrates a macro that inserts new worksheets based on names in a cell range. The cell range may […]
In this article, I am going to demonstrate a simple workbook where you can create or delete projects and add […]
Today I would like to share with you these small event handler procedures that make it easier for you to […]
This article explains how to hide a specific image in Excel using a shape as a button. If the user […]
This article describes different ways to locate literal or hardcoded values in formulas. The image above shows the result from […]
This article demonstrates macros that save worksheets to a single pdf file. What's on this webpage Export all worksheets in […]
Normalize data category
To be able to use a Pivot Table the source data you have must be arranged in way that a […]
Pivot table category
In a previous post: How to create a dynamic pivot table and refresh automatically I demonstrated how to refresh a pivot […]
Sean asks: Basically, when I do a refresh of the data in the "pivotdata" worksheet, I need it to recognise […]
In this article, I am going to show you how to quickly change Pivot Table data source using a drop-down […]
This article shows you how to refresh a pivot table automatically using a small VBA macro. If you add or delete […]
To be able to use a Pivot Table the source data you have must be arranged in way that a […]
Excel 2013 allows you to count unique distinct values in a pivot table, this article explains how to count unique […]
This article demonstrates how to build a calendar in Excel. The calendar is created as a Pivot Table which makes […]
ExcelBeginner asks: I have a small problem that I am not sure on how to solve. I now have a […]
Did you know that you can use a pivot table to summarize portfolio holdings at any point in time? If you trade […]
I read this interesting article Quick Trick: Resizing column widths in pivot tables on the Microsoft Excel blog. It is […]
A pivot table allows you to examine data more efficiently, it can summarize large amounts of data very quickly and is very easy to use.
Rodney Schmidt asks: I am a convenience store owner that is looking to make a spreadsheet formula. I want this […]
Table of Contents Introduction to pivot tables Create pivot table Group data Analyze data (pivot table) Compare performance, year to […]
Today I am going to demonstrate how amazing pivot tables are! Take a look at this time sheet. You can […]
Anura asks: I have a list of credit card transactions showing the name of the cardholder, their Branch and the […]
Rearrange values category
To be able to use a Pivot Table the source data you have must be arranged in way that a […]
The User Defined Function (UDF) demonstrated in this article, resizes a given range to columns or rows you specify. This […]
The picture above shows data presented in only one column (column B), this happens sometimes when you get an undesired […]
This article demonstrates formulas that rearrange values in a cell range to a single column. Table of Contents Rearrange cells […]
Excel formula categories
Excel categories
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.