Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Matrix to a Column Report
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Matrix to a Column Report
Your example is not clear.
Could you specify Var1, Var2 & var 3's values HTH -- AP "Dave" a écrit dans le message de news: ... 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Matrix to a Column Report
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Matrix to a Column Report
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" a écrit dans le message de news: ... 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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Matrix to a Column Report
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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Matrix to a Column Report
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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Matrix to a Column Report
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? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Matrix to a Column Report
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? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Matrix to a Column Report
....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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Column multiplied by a matrix | Excel Discussion (Misc queries) | |||
Making a Matrix into a column....... | Excel Discussion (Misc queries) | |||
How would I create a report from data in a matrix | Excel Discussion (Misc queries) | |||
how do i create a report from a matrix | Excel Discussion (Misc queries) | |||
Matrix to single column | Excel Worksheet Functions |