View Full Version : generate matrix from table?

nsmit1

01-23-2009, 11:01 AM

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

01-23-2009, 11:06 AM

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

01-23-2009, 11:13 AM

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

01-23-2009, 11:20 AM

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

01-23-2009, 11:30 AM

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

01-23-2009, 12:44 PM

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.

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

Powered by vBulletin® Version 4.2.5 Copyright © 2020 vBulletin Solutions Inc. All rights reserved.