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)*(DataB ase!$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='Cros sTab'!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


"Arjen de Winter" wrote in message
...
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

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


Bernie Deitrick wrote:
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)*(DataB ase!$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='Cros sTab'!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


"Arjen de Winter" wrote in message
...
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

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

HTH,
Bernie
MS Excel MVP


"Arjen de Winter" wrote in message
...
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


Bernie Deitrick wrote:
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)*(DataB ase!$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='Cros sTab'!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


"Arjen de Winter" wrote in message
...
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