Macro for counting instances of text

[ameya27]

Hi there

I have a file with these types of entries. I have created a table in the end to add the number of their instances.

A B C D E F
1 P1,4 P4 P2,G1 P1,2,G1 P2,G1 P2,G1(2)
2 P3,G2 G1(14) P2,G1 P1,2 P1,2,G1,2(2) G1

Above is a sample of the data...2 rows and 6 columns

• P or G denotes the type of problem
• the number after that denotes the size of the problem. e.g. P1<P2<P3<P4
• P1,4 means that both P1 and P4 are present in the sample
• G1(2) means that there are 2 instances of G1 in the sample
• P1,2,G1(2) means one instance of P1, P2 and two instances of G1 are present

I first used the command Data-->Text to Columns--> comma as delimiter and split the data into different cells. I manually added the missing P or G where required.

Then, using the COUNTIF statement I was able to count the different instances e.g. =COUNTIF(B1:B2,"P2")

I am now stumped as to how I can add the values in the brackets. e.g. if I have G1(14), I cant split it as that would give me a G1 and G14.

Is there a way that I can use a macro or nested IF statement to make the counter add it 14 times. Currently, it doesnt recognise this sort of value.

Sorry for the long post but the background I feel was necessary for comprehension. Thanks in advance to anyone who could help me with this.

[rylo]

Hi

Try this. For example purposes, I've put your sample data into the range A3:F4 and the output will go into columns H and I.

Please Login or Register  to view this content.

rylo

[rylo]

Hi

New code in file

rylo

[ameya27]

Thanks a lot Rylo...sry for the late reply...ur macro really works!