need for some code to add in a counter??

[walt1122]

Hello all, Have a need for some code to add in a counter?
Let me try to explain and maybe there is a better way to do it.

I have a tremendous amount of data, an average of 46,000 KB on each file in .RTF (Rich Text Format). I did find some code that helps me parse it into Excel by allowing me to set the maximum number of lines I can put on one workbook and then add a new workbook to continue the import filling into one workbook after another until all the data is imported. The way the original data is set up it is hard to work with, it isn't set up very well as you will see in the sample below. I have been able to use some simple formulas to extract the info on to one line so I can later do a sum-total for each person on the file. From the 60,000+ records on each worksheet I can get convert that into about 5,000 lines. However the problem, because the import stops at the predetermined counter number I quite often have a persons information on two workbooks. I would like to be able to pause the import at some point other than just the line counter alone and use the word "Northern Trust" or something so I don't pick up some participant information on the end of one file and conclude it on the top of another. I was thinking of a second counter in conjunction with the first so that the code does something like Count1 = "When row > 65,500 AND Count2 = "Northern Trust" > some count ( for example I did a countif and found 1,158 occurrences of "NORTHERN TRUST" on the file I have already imported. . I would eventually like to automate combining the workbooks together later and I could probably to try and fit all the data on one workbook. I'm thinking it is easier to do the sub totals now on 5,000 lines of data instead of trying to do it on 65,000 lines after I have combined them. Any help or direction on this effort would be greatly appreciated.

temp_5.txt
	A	B	C	D	E	F	G	H	I	J
34	\par	NORTHERN TRUST
35	\par	BENEFIT PAYMENTS	ANNUAL STATEMENT	OF PAYMENTS REPO	RT BY	ALPHA LAST	NAME		DATE	1/14/2005
36	\par	REPORT ID BPP425-ASP		*HONEYWELL*					PROGRAM	ID BPPB250R
37	\par	CLIENT 0018		HONEYWELL					PAGE	19,705
38	\par	PLAN AB5	FRO	M 1/01/2004 - 12	/31/2	4
39	\par
40	\par	PARTICIPANT NAME	SSN				PAYMT REF#
41	\par	HOME ADDRESS	PART ID	PAYMT DATE	PAYMT	TYPE	W/H & DED
42	\par	PAYMENT/ADVICE ADDRESS	RETIRE DATE	FUNDING SOURCE	AMOUN	T	TAXATION	AMOUNT
43	\par	___________________________________	_____________________________	_________________	_____	____________	______________	___________	_______________	____________
44	\par	Public, John Q.		5/3/2004	CHECK		5146845
45	\par	123 any where	123-45-6789	BENEFIT		999.83	FEDERAL	0	NET AMOUNT	789.37
46	\par	any town, any city zip	123456789	GROSS AMOUNT		999.83	GA STATE	0
47	\par		10/1/2002				BASMED	210.46
48	\par		DIST CODE 7				TOTAL	210.46
49	\par
50	\par						ORDINC	999.83	TAXABLE	999.83
51	\par								NONTAXABLE	0
52	\par	___________________________________	_____________________________	_________________	_____	____________	______________	___________	_______________	____________
53	\par	Public, John Q.		6/1/2004	CHECK		5691750
54	\par	123 any where	123-45-6789	BENEFIT		999.83	FEDERAL	0	NET AMOUNT	789.37
55	\par	any town, any city zip	123456789	GROSS AMOUNT		999.83	GA STATE	0
56	\par		10/1/2002				BASMED	210.46
57	\par		DIST CODE 7				TOTAL	210.46
58	\par
59	\par						ORDINC	999.83	TAXABLE	999.83
60	\par								NONTAXABLE	0
61	\par	___________________________________	_____________________________	_________________	_____	____________	______________	___________	_______________	____________
62	\par	Smith, John		7/1/2004	CHECK		6283632
63	\par	105 my street	3456-78-9012	BENEFIT		999.83	FEDERAL	0	NET AMOUNT	789.37
64	\par	new town NJ zip	3456789012	GROSS AMOUNT		999.83	GA STATE	0
65	\par		10/1/2002				BASMED	210.46
66	\par		DIST CODE 7				TOTAL	210.46
67	\par
68	\par						ORDINC	999.83	TAXABLE	999.83
69	\par								NONTAXABLE	0
70	\par	___________________________________	_____________________________	_________________	_____	____________	______________	___________	_______________	____________
71	\par	Smith, John		8/2/2004	CHECK		6775662
72	\par	105 my street	3456-78-9012	BENEFIT		999.83	FEDERAL	0	NET AMOUNT	789.37
73	\par	new town NJ zip	3456789012	GROSS AMOUNT		999.83	GA STATE	0
74	\par		10/1/2002				BASMED	210.46
75	\par		DIST CODE 7				TOTAL	210.46
76	\par
77	\par						ORDINC	999.83	TAXABLE	999.83
78	\par								NONTAXABLE	0
79	\par	___________________________________	_____________________________	_________________	_____	____________	______________	___________	_______________	____________
temp_5

</span><span class="gensmall"></span></td></tr></table></td></tr><tr><td class="row1" width="150" align="left" valign="middle"><span class="nav">Back to top</span></td><td class="row1" width="100%" height="28" valign="bottom" nowrap="nowrap"><table cellspacing="0" cellpadding="0" border="0" height="18" width="18"><tr>

 

[walt1122]

I'm afraid what ever method; it has to be something our outside auditor's can do. I'm a functional illiterate in Excel but the are ten times worse. They would have to be the ones to load up the data and do the queries cause remember I can't touch the data. That is the purpose for the macros. If left to their own devices they will print out the report and circle the hundreds of names they need to check and then want me to address letters and envelopes and mail out and log and research and tabulate and, and. You get the idea.

Walt

 

[walt1122]

Hi Norie, not to be a bigger pest than I already am.. but that code is not working for me. I try running it but it takes way, way too long. Best I have accomplished is to get to line 5,000 after, I bet, more than 30 minutes of time has elapsed. It would take too long this way.

Can you just answer this question.

Is there any way to combine an "IF statement", or use the "case", or loop or whatever so that

there could be some thing like this:

If Counter > 65000 And Left(ResultStr, 19) = "\par NORTHERN TRUST" Then
'Output One Line Of Text From Variable To File
Print #g, ResultStr

or
If Counter > 65000 And Left(ResultStr, 19) = "\par NORTHERN TRUST" Then
'Output One Line Of Text From Variable To File
Print #g, ResultStr
Else
If Counter >65001 And Left(ResultStr, 19) = "\par NORTHERN TRUST" Then
'Output One Line Of Text From Variable To File
Print #g, ResultStr
Else
If Counter > 65002 And Left(ResultStr, 19) = "\par NORTHERN TRUST" Then
'Output One Line Of Text From Variable To File
Print #g, ResultStr
Else
If Counter > 65003 And Left(ResultStr, 19) = "\par NORTHERN TRUST" Then
'Output One Line Of Text From Variable To File
Print #g, ResultStr
Else
If Counter > 65004 And Left(ResultStr, 19) = "\par NORTHERN TRUST" Then
'Output One Line Of Text From Variable To File
Print #g, ResultStr
Else
If Counter > 65005 And Left(ResultStr, 19) = "\par NORTHERN TRUST" Then
'Output One Line Of Text From Variable To File
Print #g, ResultStr

or case1. something = 65000
case2.something = "Northern Trust"
or LOOP UNTIL SOMETHING


I don't know what I'm talking about but I can't believe Excel doesn't have a way to do this.

I try it and I get it to loop once and do the 10,000KB or so but then it just does 1KB after another adding in a new Text.TXT file one after another. I know it is that Print #g, ResultStr thing but I just can't seem to get it to reset?? and start over????

thanks

Walt