HELP: Formula for combining fields
 

Can someone help me ?

I have a CSV file which can be opened in Excel. The CSV file i
comma-seperated !

Column 1 contains the cd numbers and column 2 contains the tracknumber
and names

So I do have a table with the following contents:
1,01;track1
1,02;track2
1,03;track3
2,02;track1
2,02;track2
And so on....

I want to have a formula, or result with the following contents:
1,01;track1|02;track2|03;track3
2,02;track1|02;track2

Is that possible to create ? How

--
pollewop
-----------------------------------------------------------------------
pollewops's Profile: http://www.excelforum.com/member.php...fo&userid=3399
View this thread: http://www.excelforum.com/showthread.php?threadid=53762

Formula for combining fields
 

Assume you open the sheet in excel and it appears as

Column A Column B
1 01;track1
1 02;track2
1 03;track3

And you want

Column A Column B Column C Column D
1 01;track1 02;track2 03;track3

Sub ProcessCSVFile()
Dim fname as Variant
Dim sh as Worksheet, sh1 as Worksheet
Dim rng as Range, i as Long, j as Long

fname = Application.GetOpenFilename(FileFilter:="CSV Files (*.CSV),*.CSV")
if fName = False then
exit sub
end if
set bk = Workbooks.Open(fName)

Set sh = ActiveSheet
With sh
Set rng = .Range(.Cells(1, 2), .Cells(Rows.Count, 2).End(xlUp))
EndWith
Set sh1 = Worksheets.Add(After:=Worksheets(Worksheets.Count) )
i = 1
j = 2
sh1.Cells(i, 1) = rng(1, 0).Value
For Each cell In rng
sh1.Cells(i, j) = cell.Value
If cell(1, 0).Value < cell(2, 0).Value Then
i = i + 1
j = 2
sh1.Cells(i, 1).Value = cell(2, 0).Value
Else
j = j + 1
End If
Next
Application.DisplayAlerts = False
sh.delete
Application.DisplayAlerts = true
End Sub

--
Regards,
Tom Ogilvy



"pollewops" wrote
in message ...

Can someone help me ?

I have a CSV file which can be opened in Excel. The CSV file is
comma-seperated !

Column 1 contains the cd numbers and column 2 contains the tracknumbers
and names

So I do have a table with the following contents:
1,01;track1
1,02;track2
1,03;track3
2,02;track1
2,02;track2
And so on....

I want to have a formula, or result with the following contents:
1,01;track1|02;track2|03;track3
2,02;track1|02;track2

Is that possible to create ? How ?


--
pollewops
------------------------------------------------------------------------
pollewops's Profile:
http://www.excelforum.com/member.php...o&userid=33999
View this thread: http://www.excelforum.com/showthread...hreadid=537625

HELP: Formula for combining fields
 

You seem to have 3 delimeters on your 'desired' rendering of your data comma,
semi-colon and pipe. (?).

Depending on the size of your CSV file, you might want to rewrite its
content to another file in the desired format and that that file instead:

Sub aa()
Close
FtieIn = FreeFile
Open "c:\zz.csv" For Input As #FtieIn
Ftieout = FreeFile
Open "c:\zz2.csv" For Output As #Ftieout
REC = "0"
Line = ""
Do Until EOF(FtieIn)
Line Input #FtieIn, a
If REC = Left(a, InStr(a, ",") - 1) Then
If a = Line Then
Line = Line
Else
Line = Line & "," & Mid(a, 2 + Len(REC))
End If
Else
If 0 < Len(Line) Then
Print #Ftieout, Line
Debug.Print Line
End If
REC = Left(a, InStr(a, ",") - 1) ' , is your record delimiter
Line = a
End If
Loop
Print #Ftieout, Line
Debug.Print Line
Close
End Sub

The Debug.Print (also written to the out file) produces:
1,01;track1,02;track2,03;track3
2,02;track1,02;track2