Counting the groups

[Nykyta]

I need to solve the nnext issue (in worksheet or VBA - never mind). In the next column, I have something as:


group

group

blank

group

group

group

blank

group

group

blank

group

group

group

group

blank

group

blank

group

blank

group

blank

group

group

group

blank


... etc ...
What is the best way to find how many groups I have there?
a) groups with 1 "group" repeating;

b) groups with 2 "group" repeating one after another;

c) groups with 3 "group" repeating one after another;

d) groups with 4 "group" repeating one after another;

e) groups with 5 "group" repeating one after another; etc.

I hope that my question is clear enoug. Thanks in advance! Nyk

 

[Nykyta]

I understand but I need it ) BTW, ar could be used too. SpecialCells(2) are dealing with clear text and my empty cells were not really empty so I needed some correction as:

    For Each c In Range("B4:B90").Cells
        If c.Value <> "Green" Then
            c.Value = ""
        End If
    Next

Thanks again!

 

[MARK858]

my empty cells were not really empty

Hmmm, weren't in your linked spreadsheet they were empty.

 

[Nykyta]

Yes they did. I made it for this purpose only as sample and your solution works perfectly well! But, data in column B are retrieved from another source and each cell has something as If data success, then output is Green so SpecialCells(2) recognize non empty but non Green neither cell as Text and return at the top 87 of course. Each cell textual.

 

[MARK858]

Yes they did. I made it for this purpose only as sample and your solution works perfectly well! But, data in column B are retrieved from another source

There is no point creating a sample sheet if you don't match the fundamentals of the original,it just wastes everybodies time

 

[Nykyta]

Strange comment... it's just you opinion... that was so minor issue... cell (visualy blank) content IsBlank or not - never mind. Tha main goal were important... I putted 'Green' data manualy just to show... my real Workbook is so big, with many Worksheets and so complicated... and source Workbook is something very else... why to post that?

Anyway: thanks again.

 

[MARK858]

Strange comment... it's just you opinion....

And Aladins opinion as well its seems as he added a like to the post.

As explanation although I don't need to is I wouldn't have written or posted the code I did if you had put formulas producing empty strings in your sample (and yes you can mimic that very easily) rather than real blank cells (and it is a big difference) and so yes it did waste my time.

 

[Oeldere]

Nykyta

it is appreciate if you also reply on my solution.

 

[Nykyta]

I can't agree with you about wasting of time. You solve my problem on perfect way so how it can bee wasting of time??? Blank or not blank - that wasn't the key. Yes, I could simulate NonBlank visual blanky cell and I didn't. Was it the tragedy?

And I did apologize for mesy initial post but after that, I corrected my mistake. What else I can do? I also thank you for your solution. So, your continuation is strange for me... dunno anything about aladin... seems that you are testing my attitude? Why so?

 

[Oeldere]

Nykyta

 it is appreciate if you also reply on my solution.

You been on line, respond on another member, but no reply to me.

In that case it was definitly a waste of time (on my site) !!!!!

 

[Nykyta]

New Excel file uploaded on http://laban.rs/MrExcel/CO_Sample_V2.xlsm