Matrix to a Column Report

[Dave]

Hello - I have a csv file in the form of a matrix with >256 variables (e.g.
300 x 300) that I want to summarize to a column report. For example:

Var1 Var2 Var3
Var1 1 2 3
Var2 4 5 6
Var3 7 8 9


And I would like the output to be:
Var1, Var1 1
Var1, Var2 2
Var1, Var3 3
Var2, Var1 4
Var2, Var2 5
Var2, Var3 6
Var3, Var1 7
Var3, Var2 8
Var3, Var3 9

Anyone know of an easy way to do this in VBA?

 

[Ardus Petus]

Your example is not clear.
Could you specify Var1, Var2 & var 3's values

HTH

 

[Tom Ogilvy]

if it is 300 x 300, you would need to write out 90,000 rows, but there are
only 65,536

is the first row and first column actually variable names.

A CSV file usually means comma separated, but you show fixed width. What
does your file actually look like?

do you really need the result in excel or just write it back out to another
CSV file?


In your sample output,
Var1, var2, 2

is the Var1 from the left side of the input (row) or from the top row
(column)?


You show
row var, column var, value

(based on the value 2), but just to be sure.

 

[Dave]

This is actually correlation report, where each value within this matrix is
the correlation between the 2 variables. Here is a more specific example:

Var1 Var2 Var3
Var1 1 .9 .1
Var2 .9 1 .8
Var3 .1 .8 1

So, for example, the correlation betwen Var1 and Var2 is .9

And this is contained within a .csv file so the file will look like this:

,Var1,Var2,Var3
Var1,1,.9,.1
Var2,.9,1,.8
Var3,.1,.8,1

And I need an output that looks like this:

Var1,Var1,1
Var1,Var2,.9
Var1,Var3,.1
Var2,Var1,.9
Var2,Var2,1
Var2,Var3,.8
Var3,Var1,.1
Var3,Var2,.8
Var3,Var3,1

Does that help?

 

[Dave]

Hi Tom - Here are some answers to your questions:

if it is 300 x 300, you would need to write out 90,000 rows, but there are
only 65,536

Ya, I will then bring this new file into Access. I would bring the original
file into Access but it has the 256 variable limitation as well.

is the first row and first column actually variable names. Yes

A CSV file usually means comma separated, but you show fixed width. What
does your file actually look like?

Sorry, I should have been more specific. I just posted an answer to Ardus'
question that shows an example of what my file actually looks like.

do you really need the result in excel or just write it back out to another
CSV file?

Output to a .csv file would be great.

In your sample output,
Var1, var2, 2

is the Var1 from the left side of the input (row) or from the top row
(column)?


You show
row var, column var, value

(based on the value 2), but just to be sure.

Thanks for clarifying, yes this is correct.

 

[Tom Ogilvy]

Sub convertData()
Dim ans As Long, hdr As Variant
Dim fName As Variant, s As String
Dim i As Long, j As Long
Dim ff As Long, ff1 As Long
Dim l1 As String, l As String
Dim v As Variant
Dim fName1 As String, sPath As String
ChDrive "C"
ChDir "C:\Chris"
fName = Application.GetOpenFilename("CSV Files (*.CSV),*.CSV")
If fName = False Then
Exit Sub
End If
fName1 = Replace(LCase(fName), ".csv", "_CONVERTED.csv")

s = fName & vbNewLine & vbNewLine & _
"will be converted and results stored in " & _
vbNewLine & vbNewLine & fName1 & vbNewLine & _
vbNewLine & "Proceed?"
ans = MsgBox(Prompt:=s, _
Buttons:=vbYesNo + vbQuestion, _
Title:="Convert " & fName1)
If ans = vbNo Then
MsgBox "You chose to quit. Ending", vbCritical
Exit Sub
End If
ff = FreeFile()
Open fName For Input As ff
ff1 = FreeFile()
Open fName1 For Output As ff1
Line Input #ff, l1
hdr = Split(l1, ",")
Do While Not EOF(ff)
Line Input #ff, l
v = Split(l, ",")
For j = LBound(hdr) + 1 To UBound(hdr)
Print #ff1, v(LBound(v)) & "," & hdr(j) & "," & v(j)
Next j
Loop
Close #ff
Close #ff1
End Sub

 

[Dave]

Thanks, worked great!

Sub convertData()
Dim ans As Long, hdr As Variant
Dim fName As Variant, s As String
Dim i As Long, j As Long
Dim ff As Long, ff1 As Long
Dim l1 As String, l As String
Dim v As Variant
Dim fName1 As String, sPath As String
ChDrive "C"
ChDir "C:\Chris"
fName = Application.GetOpenFilename("CSV Files (*.CSV),*.CSV")
If fName = False Then
Exit Sub
End If
fName1 = Replace(LCase(fName), ".csv", "_CONVERTED.csv")

s = fName & vbNewLine & vbNewLine & _
"will be converted and results stored in " & _
vbNewLine & vbNewLine & fName1 & vbNewLine & _
vbNewLine & "Proceed?"
ans = MsgBox(Prompt:=s, _
Buttons:=vbYesNo + vbQuestion, _
Title:="Convert " & fName1)
If ans = vbNo Then
MsgBox "You chose to quit. Ending", vbCritical
Exit Sub
End If
ff = FreeFile()
Open fName For Input As ff
ff1 = FreeFile()
Open fName1 For Output As ff1
Line Input #ff, l1
hdr = Split(l1, ",")
Do While Not EOF(ff)
Line Input #ff, l
v = Split(l, ",")
For j = LBound(hdr) + 1 To UBound(hdr)
Print #ff1, v(LBound(v)) & "," & hdr(j) & "," & v(j)
Next j
Loop
Close #ff
Close #ff1
End Sub

 

[Dave]

Hi Tom - Thanks again, I just have a follow up question, what if I wanted to
only include the correlations that are greater than a certain value, say .4?
Where would I add that code?

 

[Dave]

....nevermind, got it!

Hi Tom - Thanks again, I just have a follow up question, what if I wanted to
only include the correlations that are greater than a certain value, say .4?
Where would I add that code?