I should probably explain that the code I provided makes two possibly
incorrect assumptions about the data in columns A and B:
That the list of row headers and column headers in those columns only appear
once, as a simple list for each. If it appears otherwise, if you use that
code, then make 2 lists in 2 columns and point the code to them.


"JLatham" wrote:

An alternative? I wasn't sure which way your data for the matrix values is
laid out in column C, so wrote code to deal with it either being grouped by
the matrix columns or by the matrix rows. Choose which method to use by
setting the variable DataIsByRows to either True or False just ahead of the
code that moves that information.

Sub CreateMatrix()
'General Assumptions:
'sheet with data is currently active
'there is at least 1 row title
'there is at least 1 column title
'
'there is data for all cells in the matrix
'
Dim RowCount As Long
Dim RL As Long ' loop counter
Dim ColumnCount As Integer
Dim CL As Integer ' loop counter
'change these constants as appropriate for your
'layout
Const ULCorner = "H1" ' upperleft corner of matrix to build
Const RowLabels = "A1"
Const ColLabels = "B1"
Const MatrixData = "C1"
Dim DataIsByRows As Boolean ' flag to be set later by YOU
'
'get the Row headers and keep count
'assumption: row headers are in column A
'begin at A1 and continue unbroken to end of list
'
'Building matrix with H1 as upper left corner
'
RowCount = 0 ' initialize
Do Until IsEmpty(Range("A1").Offset(RowCount, 0))
Range(ULCorner).Offset(RowCount + 1, 0) = _
Range("A1").Offset(RowCount, 0)
RowCount = RowCount + 1
Loop
'
'get column headers
'
ColumnCount = 0 ' initialize
Do Until IsEmpty(Range("B1").Offset(ColumnCount, 0))
Range(ULCorner).Offset(0, ColumnCount + 1) = _
Range("B1").Offset(ColumnCount, 0)
ColumnCount = ColumnCount + 1
Loop
'
'two solutions are provided for filling the matrix data
'which to use depends on how the 'raw data' in a column
'is laid out.
' Set variable DataIsByRows = TRUE to use
' Method 1, set it to = FALSE to used Method 2
' currently set to Method 1 - right he
DataIsByRows = True
'***********************
If DataIsByRows = True Then
'
'fill matrix - Method 1
'use when raw data is sequenced in row groups
For RL = 1 To RowCount
For CL = 1 To ColumnCount
Range(ULCorner).Offset(RL, CL) = _
Range("C1").Offset(((RL * ColumnCount) - ColumnCount) +
(CL - 1), 0)
Next
Next

Else ' executed when DataIsByRows set = FALSE
'
'fill matrix - method 2
'use when raw data is sequenced in column groups
For CL = 1 To ColumnCount
For RL = 1 To RowCount
Range(ULCorner).Offset(RL, CL) = _
Range("E1").Offset(((CL * RowCount) - RowCount) + (RL -
1), 0)
Next
Next
End If
End Sub

"sa02000" wrote:


I have data in three columns. I would like to take this data and create
a matrix from this data.
So, data in columnA become column labels in matrix, data in ColumnB
become row lables in matrix and data in columnC populates the matrix
(crossection of columnA and ColumnB values).

I would prefer if this is done via macro/VBA but a formula will be fine
too.

Thanks for help in advance.
Jay


--
sa02000
------------------------------------------------------------------------
sa02000's Profile: http://www.excelforum.com/member.php...o&userid=27747
View this thread: http://www.excelforum.com/showthread...hreadid=555700