A Happy Halloween to everyone out there in the blogosphere!
Sometimes you have to work with a data set that is not in a state conducive to, well, anything. I’m thinking specifically about crosstabbed data sets. More than once I’ve had to deal with a data set that looks like this:
Oh, if only there were a way to unwind this file so that it was in a more normalized state for querying, lookups, and general analysis.
Well, now there is.
I created this routine a number of years ago because I had a monthly process that required taking a dataset like the one above and reformatting it into a layout that looks like this:
Needless to say, the data I was dealing with was a good deal larger than the sample above. I’ve used this routine on datasets that ended up outputting half a million rows. Yeah, it takes a few minutes, but it works. On smaller datasets, it works pretty fast.
Sub UnWindCrosstabData() ' This takes a cross tabbed data and "unwinds" it to a flat file. ' Variable declarations. Dim rng As Range Dim shNew As Worksheet Dim lCols As Long Dim lRows As Long Dim i As Long Dim j As Long Dim r As Long Dim vTemp As Variant ' If the selection count is greater than one, ' assume a range to uwind has been selected. If Selection.Count > 1 Then Set rng = Selection Else ' Otherwise, use a range input box to get the range via the user. On Error Resume Next Set rng = Application.InputBox("Select the range to be unwound:", , , , , , , 8) ' If we get an error, the user did not select a range, and we exit the sub. If Err.Number <> 0 Then MsgBox "You need to select a range to use this utility.", vbExclamation, "Selection Error" Exit Sub End If On Error GoTo 0 End If ' Get the total number of columns we will be dealing with. lCols = rng.Columns.Count ' Get the total number of rows we'll be dealing with, taking the use ' of headers into account. lRows = rng.Rows.Count - 1 ' Speed up processing by shutting off screen flicker. Application.ScreenUpdating = False ' Set the new worksheet that will house the data. Set shNew = ActiveWorkbook.Worksheets.Add ' Reactivate the source sheet. rng.Parent.Activate ' Select and copy the source range, and paste it into the new sheet. rng.Select rng.Copy shNew.Activate ActiveSheet.Cells(1, 1).PasteSpecial xlPasteValues ' Reset the range to the pasted selection. Set rng = Selection ' Insert a column for the data header. rng.Cells(1, 2).Select Selection.EntireColumn.Insert ' Copy the record identifiers down, taking headers into account. For i = 1 To lCols - 2 rng.Cells(2, 1).Select Range(Selection, Selection.Offset(lRows - 1, 0)).Select Selection.Copy rng.Cells(1, 1).Select Selection.End(xlDown).Select Selection.Offset(1, 0).Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False Next i ' Fill in the data header column. rng.Cells(1, 2).Value = "Data Header" r = 2 For i = 1 To lCols - 1 vTemp = rng.Cells(1, i + 2).Value For j = 1 To lRows rng.Cells(r, 2).Value = vTemp r = r + 1 Next j Next i ' Insert a column for the data. rng.Cells(1, 3).EntireColumn.Select Selection.Insert Shift:=xlToRight ' Fill in the data. rng.Cells(1, 3).Value = "Data" r = 2 For i = 0 To lCols - 2 rng.Cells(r, i + 4).Select Range(Selection, Selection.Offset(lRows - 1, 0)).Select Selection.Copy rng.Cells(r + (i * lRows), 3).Select Selection.PasteSpecial Paste:=xlPasteValues Next i ' Delete the unneeded columns rng.Cells(1, 4).EntireColumn.Select Range(Selection, Selection.End(xlToRight)).Select Selection.Delete Shift:=xlToLeft ' Turn screen updating back on. Application.ScreenUpdating = True End Sub
And having posted this, I’m already posting a hack. This routine only deals with one column of data to the left of your values. But what if you have more than one column? What if, for example, in the dataset above, you have name and address and city and state and zip code and email and phone number and…well, you get the idea.
What I do is to concatenate all of these fields together into a single column. I always use a delimiter, and for me, two good delimiters are the pipe (“|”) and the tilde (“~”). Once the field is concatenated and converted to values, I’ll run the routine above, then I’ll use Text To Columns to break all the columns back out again.
Use the above freely* and enjoy. If you have suggestion or find bugs, post in the comments section.
*and at your own risk, I assume no responsibility, legalese legalese legalese