Database function - convert flat table

  • Thread starter Thread starter Arjen de Winter
  • Start date Start date
A

Arjen de Winter

Hi All,

I'm linking a worksheet to a table on a website on Internet. The data on
the table is in the following format:

LETTER NUMBER COLOR
Alpha One Green
Alpha Two Purple
Bravo Two Orange
Delta Seven Green
Delta One Nine
Etc.


The format I need is the following:

NUMBER One Two Three Four Five Six Seven
LETTER
Alpha Green Purple
Bravo Orange
Charlie
Delta Green
Echo Nine
Etc.


So:
The first column in the old table should become row headers (records)
The second column should become column headers (fields)
The third column should become the data


I thought I'd be able to do that with Pivot tables, but that just allows
you to summarize, count, etc.

I know that the information in the table is unique. E.g. there is no
instance where data in a record's field would be filled twice.

It's easy enough to import the table into Access, analyze it, and then
move it to Excel. However, I want to have a permanent link with the data
so that I can instantaneously refresh the information (and the results
of the analysis of that information).

Thanks,


Arjen
 
Arjen,

You can use a macro - select a single cell in your table, then run the macro below.

OR ----

If you have a set number of Letter and Number values, then you could set up a table made up of
formulas.

With your example database in cells A1:C6 on a sheet named DataBase, and your row and column headers
in row 1 and column A of a sheet named CrossTab, in cell B2, array enter (enter using
Ctrl-Shift-Enter) this formula

=IF(SUM((DataBase!$A$1:$A$6='CrossTab'!$A2)*(DataBase!$B$1:$B$6='CrossTab'!B$1)*ROW(DataBase!$B$1:$B$6))=0,"",INDEX(DataBase!$C$1:$C$6,SUM((DataBase!$A$1:$A$6='CrossTab'!$A2)*(DataBase!$B$1:$B$6='CrossTab'!B$1)*ROW(DataBase!$B$1:$B$6))))

And then copy to B2:D4 (which is what your example would produce)....

HTH,
Bernie
MS Excel MVP

Sub DBtoCrossTab2()
Dim myCell As Range
Dim myTable As Range
Dim mySht As Worksheet
Dim myRow As Long
Dim myCol As Integer

Set myTable = ActiveCell.CurrentRegion

If myTable.Columns.Count <> 3 Then
MsgBox "This macro works on a 3 column database only"
Exit Sub
End If

On Error Resume Next
Application.DisplayAlerts = False
Worksheets("Cross Tab").Delete
Application.DisplayAlerts = True

Set mySht = Worksheets.Add
mySht.Name = "Cross Tab"

'myTable.Rows(1).EntireRow.Copy mySht.Rows(1)

Set myTable = myTable.Offset(1, 0).Resize _
(myTable.Rows.Count - 1, myTable.Columns.Count)

For Each myCell In myTable.Columns(1).Cells
If IsError(Application.Match(myCell.Value, _
mySht.Range("A:A"), False)) Then
mySht.Range("A65536").End(xlUp)(2).Value = myCell.Value
End If
If IsError(Application.Match(myCell(1, 2).Value, _
mySht.Range("1:1"), False)) Then
mySht.Range("IV1").End(xlToLeft)(1, 2).Value = myCell(1, 2).Value
End If

myRow = Application.Match(myCell.Value, _
mySht.Range("A:A"), False)
myCol = Application.Match(myCell(1, 2).Value, _
mySht.Range("1:1"), False)

mySht.Cells(myRow, myCol).Value = myCell(1, 3).Value

Next myCell

End Sub
 
Hi Bernie,

This is fantastic. Thank you so much! I do indeed have a set number of
values for the rows and the columns so the formula should be sufficient.

Arjen
 
Good - glad you like it. Personally, I like the macro better, since there is no maintenance
involved....

HTH,
Bernie
MS Excel MVP
 
Back
Top