Office Archives - Houston Computer Classes

If you work with databases and regular data tables you probably had a situation when someone gave you a regular Excel 2 dimensional spreadsheet that is not that useful if you need to use the data within a database and run some queries or just make PivotTables in Excel. The Macro code below will transform your CrossTab table to a regular database list table.

This script will create a new sheet and prompt you to enter a starting point for your converted data. Then it will render the data in 3 columns as a regular database data table.

Sub CrossTabToDatabase()
 Dim DataTable As Range, OutputRange As Range
 Dim RowOutput As Long
 Dim r As Long, c As Long
Dim WS As Worksheet

On Error Resume Next
 Set DataTable = ActiveCell.CurrentRegion
 If DataTable.Count = 1 Or DataTable.Rows.Count &lt; 3 Then
 MsgBox "Select a cell within the summary table", vbCritical
 Exit Sub
 End If
 DataTable.Select

 Set WS = Sheets.Add
 Set OutputRange = Application.InputBox(prompt:="Select a cell starting where you'd like to output the new datatable.", Type:=8)
' Convert the range
 RowOutput = 2
 Application.ScreenUpdating = False
 OutputRange.Range("A1:C3") = Array("Column1", "Column2", "Column3")
 For r = 2 To DataTable.Rows.Count
 For c = 2 To DataTable.Columns.Count
 OutputRange.Cells(RowOutput, 1) = DataTable.Cells(r, 1)
 OutputRange.Cells(RowOutput, 2) = DataTable.Cells(1, c)
 OutputRange.Cells(RowOutput, 3) = DataTable.Cells(r, c)
 OutputRange.Cells(RowOutput, 3).NumberFormat = DataTable.Cells(r, c).NumberFormat
 RowOutput = RowOutput + 1
 Next c
 Next r
End Sub

Sub CrossTabToDatabase()

Dim DataTable As Range, OutputRange As Range

Dim RowOutput As Long

Dim r As Long, c As Long

Dim WS As Worksheet

On Error Resume Next

Set DataTable = ActiveCell.CurrentRegion

If DataTable.Count = 1 Or DataTable.Rows.Count < 3 Then

MsgBox "Select a cell within the summary table", vbCritical

Exit Sub

End If

DataTable.Select

Set WS = Sheets.Add

Set OutputRange = Application.InputBox(prompt:="Select a cell starting where you'd like to output the new datatable.", Type:=8)

' Convert the range

RowOutput = 2

Application.ScreenUpdating = False

OutputRange.Range("A1:C3") = Array("Column1", "Column2", "Column3")

For r = 2 To DataTable.Rows.Count

For c = 2 To DataTable.Columns.Count

OutputRange.Cells(RowOutput, 1) = DataTable.Cells(r, 1)

OutputRange.Cells(RowOutput, 2) = DataTable.Cells(1, c)

OutputRange.Cells(RowOutput, 3) = DataTable.Cells(r, c)

OutputRange.Cells(RowOutput, 3).NumberFormat = DataTable.Cells(r, c).NumberFormat

RowOutput = RowOutput + 1

Next c

Next r

End Sub

Posted by Excel Instructor:
http://www.houstoncomputerclasses.com/excel-classes/

Microsoft PowerPoint Tutoring

Request a Session

Questions?

(312) 818-5515

How it Works?

Microsoft PowerPoint Class – Advanced

Request a Session

Questions?

(312) 818-5515

How Does it Work?

Microsoft PowerPoint Class – Intermediate

Request a Session

Questions?

(312) 818-5515

How Does it Work?

Microsoft PowerPoint Class – Introduction

Request a Session

Questions?

(312) 818-5515

How Does it Work?

Excel Consulting Services

Request a Quote

Questions?

(312) 818-5515

How it Works?

Microsoft Excel VBA Class – Introduction to Macros

Request a Session

Questions?

(312) 818-5515

How Does it Work?

Microsoft Excel Class – Advanced

Request a Session

Questions?

(312) 818-5515

How Does it Work?

Microsoft Excel Automation

Request a Quote

Questions?

(312) 818-5515

How it Works?

Locations: