generate matrix from table?

[nsmit1]

Hi,

I have a 1824x5 table that I want to convert in 1824 'identity statements' stacked on top of one another. To give you an idea, here's a row of data:

1;1;2;1;2

From this, I want to derive the following

1;2;1 (value in columns 1 and 2 is identical)
1;3;0 (value in columns 1 and 3 is different)
1;4;1 (etc)
1;5;0
2;3;0
2;4;1
2;5;0
3;4;0
3;5;1
4;5;0

Since this yields 18.240 rows of data, it will be obvious that I don't really want to do this by hand... I tried to get Excel 2003 to do it, but without luck so far; who has any suggestions how to handle this problem? I should add that the format is essential: I know how to get the identity statements, but I need them all in a single column.

thanks!
Niels

[mdmackillop]

Hi Neils,
Welcome to VBAX
I don't follow the etc. Can you be clearer how many comparisons you are making and how the result is determined. Posting a small sample would help. Use Manage Attachments in the Go Advanced reply section.
Regards
MD

[nsmit1]

Dear MD,

that's quick! Thanks already

The other derived rows follow the same format: values for column 1 and 4 are identical --> 1; values for 1 and 5 are different --> 0; values for 2 and 3 are different --> 0. Et cetera

This explodes each original row into 10 new ones. Get my idea? For some reason my computer doesn't allow me to post attachments; I'll do so as soon as I get home if this is not clear enough.

[NukedWhale]

XLD just helped me with this issue...so I may be able to help you with yours.

A few questions:

Does your matrix have headers? i.e column 1 is called apples?

Do you want your matrix converted to a one column vector (just results) or a 3 column vector (column, row, result)?

Wouldn't this yield 9120 rows?

[nsmit1]

Dear NukedWhale,

Unfortunately -- as I just explained to MD -- I can't posts attachments from where I am now. I'll be as complete as possible below.

The original data looks like this:

id1;var1;var2;var3;var4 (so, one row with variable names)
563;34;34;74;34;74 (1824 rows like this one. This is case number 563)

This I want to convert to:

id1;varnum1;varnum2;var5
563;1;2;1 (values of original variable 1 and 2 are identical -->1)
563;1;3;0 (values of original variables 1 and 3 are different --> 0)
563;1;4;1 (values of 1 and 4 are identical -->1)
...
563;4;5;0 (values of original variables 4 and 5 are different -->0)

So: yes, there's also a row of variable names. Yes, I eventually want a 3-col vector (in a 4-col table, but case number should remain constant across vectors). No; it really yields 18.240 rows (you're dividing the result by 2 one time too many).

I hope this is clear enough

thanks again!

[NukedWhale]

I'm understanding a little bit better now. I thought you were just creating a co-occurence matrix. I.e. count how many times varnum1 and varnum2 are equal. This is code I created to do that...I can't figure out how to adapt it yet, but maybe you, or someone else will before I do.

[VBA]
Sub CreateMatrixVector()
Dim SourceSheet As Worksheet
Dim DestinationSheet As Worksheet
Dim HeaderRange As Range
Dim MyColumns As Long
Dim NewWorksheetName As String
Dim WeightingRange As String
Dim MyRows As Long
Dim MyCounter As Long

'Original worksheet
Set SourceSheet = ActiveSheet

'Prompt for Header Range
Set HeaderRange = Application.InputBox(prompt:="Select the header row (These will be used to generate and label the matrix)", Type:=8)

'Prompt for Weighting Range
WeightingRange = InputBox("Please enter the column letter of the column used for weighting")

'Prompt for name of new worksheet until it is valid
Do While NewWorksheetName = "" Or Len(NewWorksheetName) > 31 Or NewWorksheetName = "Enter the new worksheet name HERE"
NewWorksheetName = InputBox("Please enter a worksheet name", "New Worksheet Name", "Enter the new worksheet name HERE")
Loop

'Add a new worksheet, give it the name from the input box after checking for any invalid characters.
Set DestinationSheet = Sheets.Add
ActiveSheet.Name = ValidFileName(NewWorksheetName)

'Turn off Screen Updating to speed code up
Application.ScreenUpdating = False
'Turn off Auto Calculation to speed code up
Application.Calculation = xlCalculationManual

'Count the number of columns in the user specified range
MyColumns = HeaderRange.Columns.Count

'Count the number of rows in the worksheet
MyRows = SourceSheet.UsedRange.Rows.Count

'Set MyCounter to zero
MyCounter = 0

'Loop through dataset
For i = 1 To MyColumns
For j = 1 To MyColumns
For k = 2 To MyRows
If SourceSheet.Cells(k, i).Value > 0 Then
If SourceSheet.Cells(k, j).Value > 0 Then
MyCounter = MyCounter + SourceSheet.Cells(k, WeightingRange).Value
End If
End If
Next k
'Paste header 1 and 2
DestinationSheet.Cells((i - 1) * MyColumns + j, 1).Value = SourceSheet.Cells(1, i)
DestinationSheet.Cells((i - 1) * MyColumns + j, 2).Value = SourceSheet.Cells(1, j)
'Paste the result of the counter
DestinationSheet.Cells((i - 1) * MyColumns + j, 3).Value = MyCounter
MyCounter = 0
Next j
Next i
'Turn Screen Updating back on
Application.ScreenUpdating = True
'Turn Auto Calculation back on
Application.Calculation = xlCalculationAutomatic
End Sub
[/VBA]