Normalize data [VBA]
To be able to use a Pivot Table the source data you have must be arranged in way that a Pivot Table can handle. For example, the data set above in cell range B2:F5 has headers both horizontally and vertically. In other words, the data set is a two-dimensional table.
Months are arranged horizontally in row 2 and items are arranged vertically in column B, a Pivot Table can't work with data arranged in this way. The data must be arranged row by row meaning values on the same row belong together. Debra has a great post and video about normalizing data for excel pivot table.
This article describes and demonstrates a macro that normalizes data so you can use it in an Excel Pivot Table, meaning it rearranges a dataset layout from two dimensional to row by row. The following animated picture demonstrates the macro.
To view macros in your workbook simply press Alt + F8 to open the Macro dialog box, then press with left mouse button on NormalizeData macro and press with left mouse button on "OK" button to start the macro.
Now you will be prompted for a cell range that the macro can work with and rearrange the data, press with left mouse button on OK button when finished.
The macro creates a new worksheet and populates it with values from the cell range you selected.
Where to put the code
- Copy VBA code below.
- Press Alt+ F11 to open the Visual Basic Editor.
- Press with right mouse button on on your workbook in the Project Explorer.
- Press with left mouse button on Insert.
- Press with left mouse button on Module to insert a code module to your workbook.
- Paste code to module.
- Exit VB Editor and return to Excel.
VBA code
The VBA code below contains comments so you know what each line does. A comment begins with a ' which is a single quote or apostrophe, you can safely remove the comments if you don't want them in your code.
'Name macro Sub NormalizeData() 'Dimension variables and declare data types Dim Rng As Range Dim WS As Worksheet 'Enable error handling On Error Resume Next 'Show inputbox so the user can select a cell range and save result to object Rng Set Rng = Application.InputBox(Prompt:="Select a range to normalize data" _ , Title:="Select a range", Default:=ActiveCell.Address, Type:=8) 'Disable error handling On Error GoTo 0 'Check if object variable Rng is empty If Rng Is Nothing Then 'If object variable Rng is not empty then continue with the following lines Else 'Don't show changes on screen Application.ScreenUpdating = False 'Add a worksheet and save it to object variable WS Set WS = Sheets.Add 'Save value 0 (zero) to variable i i = 0 'Iterate through 1 to the number of rows in the cell range the user selected For r = 1 To Rng.Rows.Count - 1 'Go through 1 to the number of columns in the cell range the user selected For c = 1 To Rng.Columns.Count - 1 'Save values from cell range to worksheet WS.Range("A1").Offset(i, 0) = Rng.Offset(0, c).Value WS.Range("A1").Offset(i, 1) = Rng.Offset(r, 0).Value WS.Range("A1").Offset(i, 2) = Rng.Offset(r, c).Value 'Add 1 to variable i i = i + 1 'Continue with next column Next c 'Continue with next row Next r 'Adjust column widths WS.Range("A:C").EntireColumn.AutoFit 'Show changes to the user Application.ScreenUpdating = True End If 'Stop macro End Sub
If your data is concatenated to a cell then this article may interest you:
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 […]
In this blog article, I will demonstrate basic file copying techniques using VBA (Visual Basic for Applications). I will also […]
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 […]
This article explains how to hide a specific image in Excel using a shape as a button. If the user […]
Today I would like to share with you these small event handler procedures that make it easier for you to […]
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
This article demonstrates a macro that allows you to rearrange and distribute concatenated values across multiple rows in order to […]
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 […]
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 […]
This article demonstrates a macro that allows you to rearrange and distribute concatenated values across multiple rows in order to […]
Anura asks: I have a list of credit card transactions showing the name of the cardholder, their Branch and the […]
Rearrange values category
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 a macro that allows you to rearrange and distribute concatenated values across multiple rows in order to […]
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
12 Responses to “Normalize data [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.
Hi,
I have a worksheet with following columns, namely,
Trans Date Issue Date Deposit Withdrawal Balance
01-Jan-12 01-Jan-12 50,000,000 0 50,000,000
02-Jan-12 01-Jan-12 80,000,000 0 130,000,000
10-Jan-12 01-Jan-12 0 90,000,000 40,000,000
15-Jan-12 01-Jan-12 0 40,000,000 0
I want to calculate running balance for deposit / withdrawal based on particular issue date, as shown in the example
worksheet.
Please tell me how to achieve this task.
Your help in this regard shall be highly appreciated.
Cheers!
Muhammad Nadeem
Hi,
The add-in here :
https://kb.tableausoftware.com/articles/knowledgebase/addin-reshaping-data-excel
does the same, with more powerful tricks included.
No need to have Tableau installed to run it.
Thanks for sharing.
Please see link below for an unpivot add-in, which can process large, more complex data sets fast and/or multiple similar data sets sequentially e.g. convert 100s balance sheets.
https://www.spreadsheet1.com/unpivot-data.html
Muhammad Nadeem Bhatti,
See attached file:
running-balance-on-a-issue-date.xlsx
Edouard and Petros,
thanks for sharing!
Hi Oscar,
Thanks for sharing the excel file for running balance on a issue date.
Can you please suggest a way for keeping the running balance in the table instead of making a separate issue date search and show result.
Look forward for your reply.
Grateful for your help.
Cheers!
Muhammad Nadeem Bhatti.
Muhammad Nadeem Bhatti,
See attached file:
running-balance-table.xlsx
Hi Oscar,
i was wondering to know, what software do you use creating animated .gif?
thanks.
Jam,
Camstudio (record screen)
VirtualDub (convert movie to gif)
GIMP (gif compression)
Hi there,
This is a fantastic example. I am new to the excel VBA function and i was wondering if you would be able to provide insight on how to structure the code so that the primary order is based on the months in your example. Such as:
January Warehouse 1 9194
January Warehouse 2 9992
January Warehouse 3 3840
and working it's way through before changing to February.
Thanks!!
Never mind i was able to figure out the code! My next question - is there a way to keep running the macro but keep writing the new data to the same defined sheet but not overriding the existing data on that sheet? the code i have so far is:
Thanks!
J
[…] have made a macro/udf that can help you rearrange your data, see this post: Vba macro: Normalize data and this: Normalize data, part […]
Great macro.