counting names in a text file and writing count in that text file

Hi can any one help with a VBA macro, I have a folder with text files so N1.txt, N2.txt about 4000 of them in the folder from row 2 in each text file I have data like this written all the way down the text file.

1,Amazon,1,2,3,4,BUY
2,EBAY,1,2,4,8,SELL
3.Amazon,1,0,3,9,BUY
4,Amazon,1,4,7,4,BUY
5.EBAY,7,2,9,6,BUY

In each text file I am trying to do a running count of the the unique names for example using the data above amazon has been written 3 times so next to the first amazon entry I need to write "1" next to the second amazon entry I need to write "2" and next to the third amazon entry I need to write "3"

EBAY has been written twice so next to the first EBAY entry I need to write "1" and next to the second EBAY entry I need to write "2"

See last column below

1,Amazon,1,2,3,4,BUY,1
2,EBAY,1,2,3,4,SELL,1
3.Amazon,1,2,3,4,BUY,2
4,Amazon,1,2,3,4,BUY,3
5.EBAY,1,2,3,4,BUY,2


I need to add the comma before writing the count

Hi Marvin thanks for your reply but I need this for text files for example N1.txt , N2.txt all the text files in a folder and I really need to use VBA

No I want to use vba to go through each text file and write the count from row 2 in each row for each text file in the folder, so dont pull in anything just write it to the text files

Hi yes I want to access the text files and just add an extra column with the count to each text file so N1.txt we read then write an extra column to this text file and we do this for all the text files in the folder

here is the two text files they are attached

Just to clarify most of the text files have a lot more then 20 entries on the sample files I think there are only 20 entries

It goes in standard module. Have a sheet that's blank active when you run the code.

Make sure you end fPath with "\" Ex: "C:\Users\Admin\Documents\N\"

It will read content of each text file in the folder, put values into Excel. Perform calculation on count of each name at last column (AE)
Load result into array and perform concatenation to put it into original format and output to text.

This will overwrite original file. So I'd recommend performing test in copy of original files first.

If you want, add following line below Loop line to let you know that code has finished running.

Please Login or Register  to view this content.

With your first sample file result should look like below.
0.JPG

Hi xlnitwit, the running total is specific to each file so do one file then then reset the count and do the next file

Hi CK76 thankyou for the explanation I had the path file incorrect I needed to add "\" at the end, thank you the macro works fine