Fixing Excel Data – Text to Rows and Fill Down

Pivot table

Image via Wikipedia

Excel spreadsheets are used at almost every level of business.  As such spreadsheets are created by people with different goals and different levels of familiarity.  What many don’t realize is that excel is primarily a data processing and information storage tool.  Excel is much like a database and is most effective when data is stored as one would store information in a database.  The problem is that data in a database isn’t necessarily that presentable.  So many people input information into excel and attempt to format it for presentation.  Many people create groups and merge cells or insert multiple entries into a single cell and then apply special formatting.  The down side is when someone wants to use the graphing and report building features of excel the combined or grouped data isn’t accessible because it is either missing from cells due to cell merging or each entry isn’t accessible on its own because it has been entered into a single cell.  The best method is to treat the data as a database and ensure that each cell in a column has data in it relative to the cells in the row so that a complete record is available.  Then use the grouping and pivot table features to create reports that are presentable and much more functional because the data can be grouped and categorized in two dimensions.

But what do you do if you get  a spreadsheet in which data has been either entered into one cell but not in subsequent rows in some cases or has been merged through rows?  The first thing to do is to select the column with merged data and unmerge it. But then what now.  You can either go through manually massage the data adding rows and extracting the entries entered in a single cell and use a fill down copy where an entry was merged through rows.  But if you have hundreds of rows this can be time consuming.   I have run into this problem frequently enough that I have taken some time out to write a couple functions that spead up the process.

Text To Rows

If you have ever imported data into excel you may be familiar with the text to columns function under the data menu.  If you didn’t specify delimiters or missed one while importing you can select the column with multiple fields of data and tell it what to split the data on and it will automatically split the data across columns.  But what about rows.  The procedure below may be run on any column where multiple entries have been combined into single cells.  The default delimiter is a comma but this may be modified to meet your particular needs.

Sub TxtToRows()

‘ Procedure TxtToRow
‘ Author : Devin Adint
‘ Purpose: This procedure extracts multiple entries in a single cell into
‘ newly inserted subsequent rows, preserving the contents of the
‘ surrounding cells in that row. This procedure helps to address
‘ times when excel isn’t treated as a database and splits out the
‘ unique data into its own row as one would expect in a data set.

Dim ThisRow As Long
Dim vCellData As Variant
ORIGSHEET = ActiveSheet.Name
ORIGADDR = ActiveCell.Address
CrntRow = ActiveCell.Row
CrntCol = ActiveCell.Column
ThisRow = CrntRow

With ActiveSheet

Do While .Cells(ThisRow, CrntCol) <> “”
‘ Extract cell data into array
vCellData = Split(Cells(ThisRow, CrntCol), “,”)
vCellEntryCount = UBound(vCellData)
‘ loop through each delimited value from cell stored in the array
‘ and copy the current line inserting a copy below
‘ then over-write the columns cell with the delimited value
‘ If there was only 1 entry move to the next row
If vCellEntryCount = 0 Then

ThisRow = ThisRow + 1

Else ‘ else loop through the entries adding a new row

For vCellCount = 0 To vCellEntryCount

vCellEntry = vCellData(vCellCount)
‘ If this is not the last entry insert a copy of the current line
‘ for the next entry
If vCellCount < vCellEntryCount Then

.Rows(ThisRow).Copy
.Rows(ThisRow).Insert Shift:=xlDown

End If

‘ Update the current line of the multi-entry cell being processed
‘ with the current entry
.Cells(ThisRow, CrntCol) = vCellEntry
‘ Move to the next row
ThisRow = ThisRow + 1

Next

End If
Loop

End With
End Function

 

Fill Down Copy

If you have data in one cell which is either merged through rows or in a single cell with subsequent rows blank you will not be able to group by this column until each record has the corresponding data in each cell in the column.  You could page down and select each populated cell and then double click the little corner box to perform a fill down copy or you can execute a procedure.  The procedure below may be run to copy down data into unpopulated cells.

Sub FillDownCopy()

‘ Procedure FillDownCopy
‘ Author : Devin Adint
‘ Purpose: This procedure moves down a column copying down data
‘ into unpopulated columns

While Selection.End(xlDown).Row < 1048575

If (ActiveCell.Offset(1, 0).Value2 = “”) Then

Selection.Copy
Range(ActiveCell.Offset(1, 0).Address).Select
Selection.End(xlDown).Select
Range(ActiveCell.Offset(-1, 0).Address).Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.End(xlDown).Select

Else

Range(ActiveCell.Offset(1, 0).Address).Select

End If

Wend
End Function