how to transform three columns to a matrix using macro

I need some help converting three colums into a matrix using excel macro. Here is an example:

From this:

A A 0
A B 23
开发者_如何学CA C 3
B A 7
B B 56
B C 33
C A 31
C B 6
C C 5

to this:

   A   B  C
A  0  23  3
B  7  56 33
C 31   6  5

Hope you can help me.

Thanks

Not quite sure what exactly you are meaning by matrix. For the code below I assumed you were looking for a way to read the data in the first two columns as Row and Column data of the output table. Assume the input data is in the Columns 1 - 3 of "Sheet1"

Sub ConvertTableOfData()
Dim testArray(1 to 3)
Dim chkROW as Integer
Dim chkCOL as Integer
Dim chkVAL as Integer

'// index the Row and Column headers
testArray(1) = "A"
testArray(2) = "B"
testArray(3) = "C"

'// Iterate through every row in the initial dataset
For i = 1 to Worksheets("Sheet1").Cells(1, 1).End(xlDown).Row

    With Worksheets("Sheet1")
        '// Assign the Output Row and Column values 
        '// based on the array indices
        For j = 1 to UBound(testArray, 1)
            If .Cells(i, 1) = testArray(j) Then
                chkROW = j
            End If
            If .Cells(i, 2) = testArray(j) Then
                chkCOL = j
            End If
        Next j

        '// store the actual value
        chkVAL = .Cells(i, 3)
    End With

    '// output table (in Sheet2)
    With Worksheets("Sheet2")
        .Cells(chkROW, chkCOL) = chkVAL
    End With
Next i

'// Add headers to Output table
For i = 1 to 3
    With Worksheets("Sheet2")
        .Cells(i + 1, 1) = testArray(i)
        .Cells(i, i + 1) = testArray(i)
    End With
Next i
End Sub

You can also perform this without VBA.

Assume your table of data is in the range A1:C9. Assume the first number (0) in the 3 by 3 grid of data is cell F3, with A, B, C in the row above, and A, B, C in the column to the left.

Enter the formula in cell F3 as

=INDEX($C$1:$C$9,SUMPRODUCT(--($A$1:$A$9=$E3),--($B$1:$B$9=F$2),ROW($A$1:$A$9)))

Copy this formula to all 9 cells in the 3 by 3 grid.

This generalized to any size of data.