VBA for a huge database.. 800k lines...

[stedaley]

Right,

I have 800000+ cells with varying text in, split between 50 different runs, around 17000 in the first run, so
A B C D E
1 Text
1 Text
1 Text
1 Text
2 Text
2 Text
2 Text

etc

I need the script to search the text for certain phrases and then count how many of these there are per run per phrase. (I have 7 phrases)

I managed to do this for the 1st set using Functions but know it would be easier with VBA and with 800k lines for this first Excel sheet.

I need it to show the run in Column C and then how many times each phrase has appeared across the row.

A B C D E
Run Phrase 1 Phrase 2 etc......
1 Text 1
1 Text 2
1 Text 3
1 Text 4
2 Text 5
2 Text 6
2 Text 7

Can anyone help? I haven't got much to go on!

Ste

[walruseggman]

This sounds doable, but a sample worksheet is preferred. As you can see, your formatting didn't turn out great.

If you can show two sheets, a before and after, that would help. Data set doesn't have to be huge, as long as you get your point across, and can demonstrate things like what exactly splits two runs, etc.

[MrShorty]

Would a pivot table work? Pivot tables can be quick and efficient ways to summarize large databases of information. It may also be easier to set up than coding a routine in VBA.

[stedaley]

Example Phrase sheet.xlsx

Heres a little example sheet for you to get an idea, Im pretty new to VBA and most of the kit im working with so if you could help that would be great!

Ste

[LordLoki]

as Mentioned by MrShorty a pivottable would be the easiest way to do it see my example

Pivot.PNG

[stedaley]

This is close, but i dont want all of the Phrases. only certain ones.

I forgot to say these words are in sentences in the Phrase part,

So it would be
"A defeated B at point 340"

but that could account for A-Z worth of objects.

Heres a slightly bigger version of what i Have

Example Phrase sheet.xlsx

[LordLoki]

Hi There,

I still dont think that a Macro is needed and also not that it would be faster

Here is an Example with a Simple Formula to Scan the Text and count it
I inserted 800k Rows to see if it takes forever or not and it doesnt :D

[stedaley]

Hi There,

I still dont think that a Macro is needed and also not that it would be faster

Here is an Example with a Simple Formula to Scan the Text and count it
I inserted 800k Rows to see if it takes forever or not and it doesnt :D

think you have cracked it my friend! I shall try and apply it to mine and let you know how i get on!