Module for Pivoting a Tabular Spreadsheet for Database Import

  • Thread starter Thread starter DoctorV
  • Start date Start date
D

DoctorV

I have a spreadsheet that is in effect the finished product of
crosstab. The first column is date which would stay fixed and i
contains records from rows A2:A367. Then we have coulmn headings fro
Column B to Column CU which should in effect become field values.
need to pivot this data to look like what is showing up below th
asterisks. How can I do this?



Date Animal Mortality{Connie Brierly Animal Mortality{Virgini
Covington 1/1/2004
1/2/2004 83 77
etc

******************************************
What data needs to end up like
******************************************
Date Type Number Processed
1/1/2004 Animal Mortality{Connie Brierly
1/2/2004 Animal Mortality{Connie Brierly 83
etc for Animal Mortality{Connie Brierly

1/2/2004 Animal Mortality{Virginia Covington 7
 
DoctorV,

Try the macro below. Select a single cell in your table and then run it.

HTH,
Bernie
MS Excel MVP

Sub MakeTable2()
Dim myCell As Range
Dim newSheet As Worksheet
Dim mySheet As Worksheet
Dim i As Long
Dim j As Integer
Dim k As Long
Dim l As Integer
Dim mySelection As Range
Dim RowFields As Integer

Set mySheet = ActiveSheet
Set mySelection = ActiveCell.CurrentRegion
RowFields = 1
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("New Database").Delete
Application.DisplayAlerts = True
Set newSheet = Worksheets.Add
newSheet.Name = "New Database"
mySheet.Activate
i = 1
For j = mySelection(1).Row + 1 To _
mySelection(mySelection.Cells.Count).Row
For k = mySelection(1).Column + RowFields To _
mySelection(mySelection.Cells.Count).Column
If mySheet.Cells(j, k).Value <> "" Then
For l = 1 To RowFields
newSheet.Cells(i, l).Value = _
Cells(j, mySelection(l).Column).Value
Next l
newSheet.Cells(i, RowFields + 1).Value = _
Cells(mySelection(1).Row, k).Value
newSheet.Cells(i, RowFields + 2).Value = _
Cells(j, k).Value
i = i + 1
End If
Next k
Next j

End Sub
 
Good morning, Doctor V!

There sure seem to be a lot of people from Virginia in here ( I am from
Roanoke! ).

Cary
 
Debra,

Thanks for the link to that technique. Can that method be used to "Unpivot"
a data table with more than one column of labels, or does that still require
a macro?

Bernie
MS Excel MVP
 
Bernie,

You can concatenate the label columns, "Unpivot" the data using the new
column, then split the concatenated labels.

Debra
 
Back
Top