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 B24 (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" <(E-Mail Removed)> wrote in message
news:4513fa6b$0$4512$(E-Mail Removed)...
> 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)*(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 B24 (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" <(E-Mail Removed)> wrote in message
> news:4513fa6b$0$4512$(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
news:451420ca$0$4512$(E-Mail Removed)...
> 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)*(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 B24 (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" <(E-Mail Removed)> wrote in message
>> news:4513fa6b$0$4512$(E-Mail Removed)...
>>> 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
>>