Excel VBA Macro Code for Dealing with Cross-Tab Data

From doing a lot of data analysis, I have found that often spreadsheets come already in a cross-tab format, with categories across the top and side, with the intersection being the value that matches both criteria.  While this format is very intuitive for most people to understand and quickly get information, it is not normalized by the standards of databases, and thus it is much harder to work with using data-analysis tools, like pivot tables, etc.   I could not find any good code on the Internet to solve this problem, so I wrote the following functions for Excel VBA.  This code is being released under the GNU Lesser General Public License, as it is basically a library, and I don’t want to stop proprietary spreadsheets (software) from using it.  But, I would appreciate a comment to this posting here, if you use these functions, so I can see that my work is actually being used by others

I currently have not written a macro that combines together all of these functions, although it should be pretty easy to do.  Instead, at this point, I usually just manually build a table that has 4 columns, one for the row headings, one for the column headings of the cross-tab, one for the intersecting data, and one that just contains an incremental index, from 1 to however many items are in the cross-tab.  I then use the appropriate custom formulas below for each cell:

Determining the Cross-Tab Value for either a Single Index, or Coordinates

[code language=”vb”]
Function CrossTabValue(MyRange As Range, index1 As Integer, Optional ByVal index2 As Variant)
‘Returns the value for a cell in a crosstab style range

‘Defines Variables to make it easier to handle the range
HeaderColumn = MyRange.Column
HeaderRow = MyRange.Row
FirstColumn = HeaderColumn + 1
FirstRow = HeaderRow + 1
LastColumn = MyRange.Columns(MyRange.Columns.Count).Column
LastRow = MyRange.Rows(MyRange.Rows.Count).Row
NumColumns = MyRange.Columns.Count – 1
NumRows = MyRange.Rows.Count – 1

If IsMissing(index2) Then
‘ In this case, index1 is a one dimensional input, that needs to be converted to 2-dimensions
x = HeaderColumn + ((index1 – 1) Mod NumColumns) + 1
y = HeaderRow + ((index1 – 1) / NumColumns) + 1
Else
‘ In this case, index1 and index2 both exist, and can quickly be converted
x = HeaderColumn + index1
y = HeaderRow + index2
End If

CrossTabValue = MyRange.Worksheet.Cells(y, x).Value

End Function
[/code]

Determining the Cross-Tab Column Heading for either a Single Index, or Coordinates

 

[code language=”vb”]
Function CrossTabColumnHeader(MyRange As Range, index1 As Integer, Optional ByVal index2 As Variant)
‘Returns the column header for a cell in a table or range

‘Defines Variables to make it easier to handle the range
HeaderColumn = MyRange.Column
HeaderRow = MyRange.Row
FirstColumn = HeaderColumn + 1
FirstRow = HeaderRow + 1
LastColumn = MyRange.Columns(MyRange.Columns.Count).Column
LastRow = MyRange.Rows(MyRange.Rows.Count).Row
NumColumns = MyRange.Columns.Count – 1
NumRows = MyRange.Rows.Count – 1

If IsMissing(index2) Then
‘ In this case, index1 is a one dimensional input, that needs to be converted to 2-dimensions
x = HeaderColumn + ((index1 – 1) Mod NumColumns) + 1
y = HeaderRow + ((index1 – 1) / NumColumns) + 1
Else
‘ In this case, index1 and index2 both exist, and can quickly be converted
x = HeaderColumn + index1
y = HeaderRow + index2
End If

CrossTabColumnHeader = MyRange.Worksheet.Cells(HeaderRow, x).Value

End Function
[/code]

Determining the Cross-Tab Row Heading for either a Single Index, or Coordinates

[code language=”vb”]
Function CrossTabRowHeader(MyRange As Range, index1 As Integer, Optional ByVal index2 As Variant)
‘Returns the row header for a cell in a table or range

‘Defines Variables to make it easier to handle the range
HeaderColumn = MyRange.Column
HeaderRow = MyRange.Row
FirstColumn = HeaderColumn + 1
FirstRow = HeaderRow + 1
LastColumn = MyRange.Columns(MyRange.Columns.Count).Column
LastRow = MyRange.Rows(MyRange.Rows.Count).Row
NumColumns = MyRange.Columns.Count – 1
NumRows = MyRange.Rows.Count – 1

If IsMissing(index2) Then
‘ In this case, index1 is a one dimensional input, that needs to be converted to 2-dimensions
x = HeaderColumn + ((index1 – 1) Mod NumColumns) + 1
y = HeaderRow + ((index1 – 1) / NumColumns) + 1
Else
‘ In this case, index1 and index2 both exist, and can quickly be converted
x = HeaderColumn + index1
y = HeaderRow + index2
End If

CrossTabRowHeader = MyRange.Worksheet.Cells(y, HeaderColumn).Value

End Function
[/code]

Getting the size of a Cross-Tab (This would be useful in a For-Loop)

[code language=”vb”]
Function CrossTabSize(MyRange As Range)
‘Returns the number of items in a cross tab

‘Defines Variables to make it easier to handle the range
HeaderColumn = MyRange.Column
HeaderRow = MyRange.Row
FirstColumn = HeaderColumn + 1
FirstRow = HeaderRow + 1
LastColumn = MyRange.Columns(MyRange.Columns.Count).Column
LastRow = MyRange.Rows(MyRange.Rows.Count).Row
NumColumns = MyRange.Columns.Count – 1
NumRows = MyRange.Rows.Count – 1

CrossTabSize = NumColumns * NumRows

End Function
[/code]

Leave a Comment

Your email address will not be published. Required fields are marked *