Matrix to a Column Report

Dave · 05-02-2006, 02:00 PM

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 · 05-02-2006, 02:10 PM

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

HTH
--
AP

"Dave" <[email protected]> a écrit dans le message de news:
[email protected]...
> 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?

Tom Ogilvy · 05-02-2006, 02:15 PM

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.

--
Regards,
Tom Ogilvy

"Dave" wrote:

> 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?

Dave · 05-02-2006, 02:25 PM

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?

"Ardus Petus" wrote:

> Your example is not clear.
> Could you specify Var1, Var2 & var 3's values
>
> HTH
> --
> AP
>
> "Dave" <[email protected]> a Ã©crit dans le message de news:
> [email protected]...
> > 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?
>
>
>

Dave · 05-02-2006, 02:35 PM

Hi Tom - Here are some answers to your questions:

"Tom Ogilvy" wrote:

> 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.

>
> --
> Regards,
> Tom Ogilvy
>
>
> "Dave" wrote:
>
> > 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?

Tom Ogilvy · 05-02-2006, 03:20 PM

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

--
Regards,
Tom Ogilvy

"Dave" wrote:

> Hi Tom - Here are some answers to your questions:
>
> "Tom Ogilvy" wrote:
>
> > 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.
>
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "Dave" wrote:
> >
> > > 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?

Dave · 05-02-2006, 03:40 PM

Thanks, worked great!

"Tom Ogilvy" wrote:

> 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
>
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Dave" wrote:
>
> > Hi Tom - Here are some answers to your questions:
> >
> > "Tom Ogilvy" wrote:
> >
> > > 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.
> >
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > > "Dave" wrote:
> > >
> > > > 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?

Dave · 05-02-2006, 04:10 PM

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?

"Tom Ogilvy" wrote:

> 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
>
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Dave" wrote:
>
> > Hi Tom - Here are some answers to your questions:
> >
> > "Tom Ogilvy" wrote:
> >
> > > 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.
> >
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > > "Dave" wrote:
> > >
> > > > 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?

Dave · 05-02-2006, 04:35 PM

....nevermind, got it!

"Dave" wrote:

> 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?
>
> "Tom Ogilvy" wrote:
>
> > 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
> >
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "Dave" wrote:
> >
> > > Hi Tom - Here are some answers to your questions:
> > >
> > > "Tom Ogilvy" wrote:
> > >
> > > > 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.
> > >
> > > >
> > > > --
> > > > Regards,
> > > > Tom Ogilvy
> > > >
> > > >
> > > > "Dave" wrote:
> > > >
> > > > > 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?