Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel Rows to Column 1

Status
Not open for further replies.

Brianfree

Programmer
Feb 6, 2008
220
0
0
GB
Hello, i have excel document containing 2 columns..

AAA, 123
AAA, 234
BBB, 345
CCC, 456
DDD, 567
DDD, 678
DDD, 789

ETC..

Need to convert the rows to columns but preserve the first row.. So would need it to look like..

AAA, 123, 234
BBB, 345
CCC, 456
DDD, 567, 678, 789

ETC..

I can find ways to convert columns to rows but not the other way round?

Please any anyone assist?

Many thanks

Brian.


 
I tried this
brianfree.vbs
Code:
set myDictionary = CreateObject("Scripting.Dictionary")

with CreateObject("scripting.filesystemobject").OpenTextFile("brianfree.csv")
  do while not .AtEndOfStream
    inputLine = .readLine
    inputLineArray = split(inputLine, ",")
    key = inputLineArray(0)
    val = inputLineArray(1)
    'store elements into dictionary
    if myDictionary.exists(key) then
      myDictionary.item(key) = myDictionary.item(key) & "," & val
    else
      myDictionary.item(key) = val
    end if
  loop
end with

set outputFile = CreateObject("scripting.filesystemobject").CreateTextFile("brianfree_out.csv", True)
for each key in myDictionary.keys
  outputLine = key & "," & myDictionary.item(key)
  outputFile.writeLine(outputLine)
next

For this input CSV file
Code:
AAA,123
AAA,234
BBB,345
CCC,456
DDD,567
DDD,678
DDD,789
the program creates this output CSV file
Code:
AAA,123,234
BBB,345
CCC,456
DDD,567,678,789
 
Thankyou Mikrom, will have a play but looks perfect thankyou!!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top