Data analysis by 2-dimensional data table + database

[H.G. Lamy]

Hello,

in an old Excel-manual (possibly way back to version 3) by Microsoft there
was an excellent example of a 2-dimensional datatable (in German:
Mehrfachoperation) with input variables based on database results. Now I
would need to apply this technique, but my old manual is lost, and I can't
figure out by heart exactly how this was done.

Does somebody still have an old manual from which to draw the model, or can
let me know the deatils of the trick?

Thank you in advance.

Kind regards,

H.G. Lamy

 

[Bernie Deitrick]

H.,

I think that you are talking about a cross-tab table.

There are two ways to convert a database to a cross-tab table.

If you have a three column database, and the third column is numeric, simply use the database as the
source of a pivot table. Drag column A to the row field, column B to the column field, and column C
to the data field, set to sum.

Or you can use a macro, like the one below, which converts a three column database to a cross-tab
table.

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)
MsgBox myTable.Address
MsgBox myTable.Columns(1).Cells.Address

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)

If IsNumeric(myCell(1, 3).Value) Then
mySht.Cells(myRow, myCol).Value = mySht.Cells(myRow, myCol).Value + myCell(1, 3).Value
Else
mySht.Cells(myRow, myCol).Value = myCell(1, 3).Value
End If
Next myCell

End Sub

 

[H.G. Lamy]

Bernie,

thank you very much for your suggestion, I'll have a close look at it to see
if I can use it (I'm not yet very familiar with VBA).

The model I originally refered to, however, simply harnessed the command
"Data - table..." (English version) or "Daten - Mehrfachoperation..."
(German version), without any programming (VBA was unknown to early XL
anyway).

In case there is still an old manual (MS still printed them out at the time)
of Excel 3 around - the technique was shown/explained there on page 500 +
something. Any idea where to retrieve that ?

Thank you in advance.

Kind regards,

H.G. Lamy