dear all,

i had spreadsheet which look like this:
ID info
-- ---
KAS_1 123
KAS_2 345
TAS_2 111

i wish to have a macro which can search through the ID column and group
those with same left 3 characters of ID into one txt file.

example of the content of txt files which i prefer:

KAS.txt
-------
1 having 123
2 having 345


(1,2 here is the character after the underscore of ID)

TAS.txt
-------
2 having 111
(2 here is the character after the underscore of ID)


can anyone help me on this?Thousand thanks


--
ymeyaw
------------------------------------------------------------------------
ymeyaw's Profile: http://www.excelforum.com/member.php...o&userid=34421
View this thread: http://www.excelforum.com/showthread...hreadid=541920

maybe a formula like this. Then copy/paste values. Or use a macro to do the
same for all.
=right(a1,1)& " having "& b1

--
Don Guillett
SalesAid Software

"ymeyaw" wrote in
message ...

dear all,

i had spreadsheet which look like this:
ID info
-- ---
KAS_1 123
KAS_2 345
TAS_2 111

i wish to have a macro which can search through the ID column and group
those with same left 3 characters of ID into one txt file.

example of the content of txt files which i prefer:

KAS.txt
-------
1 having 123
2 having 345


(1,2 here is the character after the underscore of ID)

TAS.txt
-------
2 having 111
(2 here is the character after the underscore of ID)


can anyone help me on this?Thousand thanks


--
ymeyaw
------------------------------------------------------------------------
ymeyaw's Profile:
http://www.excelforum.com/member.php...o&userid=34421
View this thread: http://www.excelforum.com/showthread...hreadid=541920

hi ,
Thanks for ur advise. But my problem is, i have thousands of data and i
cant simply using copy and paste.
Below is my script, but still cannot group 3 charaters of id into one
single txt file. I had been working on this for few days...:(


Sub test()

startrow = 2
a = startrow
Do While Not Cells(a, "A") = ""
a = a + 1
Loop
endrow = a - 1


For a = startrow To endrow

id_name=left(Cells(a,"A"),3)
id= (Cells(a, "A"))
info= Cells(a, "B")

Call print_output(id, info,id_name)


Close #1


Next a



Sub print_output(id, info,id_name)

homedir = ThisWorkbook.Path
folder = "my_folder"

outputfile = homedir & "/" & folder & "/" & id_name & ".txt"
Open outputfile For Output As #1

Print #1, & id "is "& " " & info; ""




End Sub


--
ymeyaw
------------------------------------------------------------------------
ymeyaw's Profile: http://www.excelforum.com/member.php...o&userid=34421
View this thread: http://www.excelforum.com/showthread...hreadid=541920

Try this

'############################################
Option Explicit

Sub test()

Const startrow As Long = 2
Dim a As Long
Dim id_name As String, id As String, info As String
Dim temp As String


a = startrow
Do While Not ActiveSheet.Cells(a, "A").Value = ""
temp = ActiveSheet.Cells(a, "A").Value
id_name = Left(temp, 3)
id = Right(temp, Len(temp) - 4)
info = ActiveSheet.Cells(a, "B").Value

Call print_output(id, info, id_name)

a = a + 1
Loop

End Sub




Sub print_output(id, info, id_name)

Dim sPath As String

sPath = ThisWorkbook.Path & "/Test/" & id_name & ".txt"
'note use of append: "for output" will
' always overwrite the last item....
Open sPath For Append As #1
Print #1, id & " is " & info
Close #1
End Sub
'#########################################

Tim


"ymeyaw" wrote in message
...

hi ,
Thanks for ur advise. But my problem is, i have thousands of data and i
cant simply using copy and paste.
Below is my script, but still cannot group 3 charaters of id into one
single txt file. I had been working on this for few days...:(


Sub test()

startrow = 2
a = startrow
Do While Not Cells(a, "A") = ""
a = a + 1
Loop
endrow = a - 1


For a = startrow To endrow

id_name=left(Cells(a,"A"),3)
id= (Cells(a, "A"))
info= Cells(a, "B")

Call print_output(id, info,id_name)


Close #1


Next a



Sub print_output(id, info,id_name)

homedir = ThisWorkbook.Path
folder = "my_folder"

outputfile = homedir & "/" & folder & "/" & id_name & ".txt"
Open outputfile For Output As #1

Print #1, & id "is "& " " & info; ""




End Sub


--
ymeyaw
------------------------------------------------------------------------
ymeyaw's Profile: http://www.excelforum.com/member.php...o&userid=34421
View this thread: http://www.excelforum.com/showthread...hreadid=541920

hi tim,

Thanks for ur greatest help.This is exactly what i want.

I just realized that i have to insert statements in first few lines of
the txt file .So, i do a print action, but the result is poor. Do i need
a reference template (with statement) or i can just write inside the
code?

note: the statement is the same for all the txt files.

example of txt file which i wish to optimise:

KAS.txt
--------
hello world 1
hello world 2
1 is 123
2 is 345


--
ymeyaw
------------------------------------------------------------------------
ymeyaw's Profile: http://www.excelforum.com/member.php...o&userid=34421
View this thread: http://www.excelforum.com/showthread...hreadid=541920

sorry ,

The output in txt which i would like to have:

KAS.txt
--------
Hello world 1
1 having 123
2 having 345
Hello world 2


--
ymeyaw
------------------------------------------------------------------------
ymeyaw's Profile: http://www.excelforum.com/member.php...o&userid=34421
View this thread: http://www.excelforum.com/showthread...hreadid=541920