Need to process more data than excel can handle

[dal777]

Hi all, I'm trying to process so much data that excel just stops responding, so what I need to do is process smaller amounts at a time and store the result on a seperate sheet. This is what the data looks like when it's imported from a .txt file :

KMTN 39.32566412 -76.41376644
{
RUNWAY 5016
PARKING 18
PARKING 31
PARKING 10
PARKING 10
PARKING 10
}

The imported data goes in ROWS A B C, the processed data goes in - J L N P R T V X Z AB AD AF AH AI AJ


As you can see it's only 3 columns wide but there's about 500,000 lines of it .The above data will form 1 record once processed but the amount of rows in a record varies. Every record ends with a } symbol.
I know excel cant handle more than about 65,000 rows - thats not a major problem, I'll split the txt into 10 files of 50,000 if need be.
How do I get excel to proccess a record i.e - a varying number of rows - typically between 5 & 50, put the result on another sheet then proccess some more. I've not got a clue about VISUAL BASIC or macros which I suspect is where the answer lies.
Any help appreciated - thanks.

 

[XLGibbs]

You would need to show us a sample of how the data looks once imported, and how it looks once it is "processed" by excel.

And yes, the answer is VB, or possible a Pivot table.

You could throw all the imported data into a pivot table and see if that lets you spin it for the desired output. Excel's help file is pretty decent on using pivot tables.

 

[dal777]

Thanks for the quick reply there Gibbs.

Okay here is the imported data :

And the output looks like this :

[/img]

 

[XLGibbs]

Okay, now explain the logic of how the information translates...I looked at it and all I can see is that you count the occurrence of the word runway as a count...

 

[dal777]

This should explain all. As you can see there can be many lines of imported data just for 1 line of the data that I really want.


http://img.photobucket.com/albums/v732/dal777/image1.jpg