Count groups of cells is a range

amitcohen

Board Regular
Joined
Jan 14, 2010
Messages
118
Hi Guys

I have a row that contains the following cells
(First cell is 'A1', last cell is 'AB1')
Code:
[B][COLOR=#008000]X X X X X [/COLOR][/B][COLOR=#ff0000]Z Z Z[/COLOR] [COLOR=#008000][B]X X X X X X X X X X[/B][/COLOR] [COLOR=#ff0000]Z Z Z Z[/COLOR] [B][COLOR=#008000]X X X X X X[/COLOR][/B]

Need to find a way to:
1. Formula that will tell me how many 'X' groups I have in the range, (In this case it's 3) and the sizes of groups (5, 10, 6)
2. Formula that tells me how many 'Z' groups (In this case it's 2) and the sizes of groups (3, 4)

Hope you can give me directions..

Many thanks,
Amit
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
With these values in A1:AB1
Code:
[B][COLOR=#008000]X X X X X [/COLOR][/B][COLOR=#ff0000]Z Z Z[/COLOR] [COLOR=#008000][B]X X X X X X X X X X[/B][/COLOR] [COLOR=#ff0000]Z Z Z Z[/COLOR] [B][COLOR=#008000]X X X X X X[/COLOR][/B]
and
AE1: Char
AE2: X
AE3: Z
and
AF1: Grp Count

This ARRAY FORMULA, completed by holding CTRL and SHIFT when you press ENTER (instead of just pressing ENTER)
returns the number of groups containing the char
Code:
AF2: =COUNT(1/FREQUENCY(IF($A$1:$AB$1=AE2,COLUMN($A$1:$AB$1)),IF($A$1:$AB$1<>AE2,COLUMN($A$1:$AB$1))))
Copy that formula into AF3.

Now for the group sizes:
This ARRAY FORMULA (see above) returns the size of the first group, counting from the left
Code:
AG2: =IFERROR(LARGE(FREQUENCY(IF($A$1:$AB$1=$AE2,COLUMN($A$1:$AB$1)),IF($A$1:$AB$1<>$AE2,COLUMN($A$1:$AB$1))),$AF2-COLUMNS($AG:AG)+1),"")
• Copy that formula
• Paste it into AH2:AP2
• Paste it into AG3:AP3

These will be the results:
Code:
Char   Group Count    Grp_01     Grp_02     Grp_03          Grp_04
X          3          5          6          10              (blank)
Z          2          3          4          (blank)         (blank)

Does that help?
 
Upvote 0
Hi Ron

This is just perfect!
Thank you so much for taking your time and help with solution.

Amit.
 
Upvote 0
Hi Ron
Just 1 more Q, if I may..

Is there a way to keep the 'Group Size' accordingly?
I mean instead of ascending, to keep it the right order,
Like:
Code:
Char   Group Count    Grp_01     Grp_02     Grp_03          Grp_04
 X          3             5         10         6           (blank)
Thank you,
Amit
 
Upvote 0
THAT was a nice little challenge. There may be a better way, but this is what I came up with.

Same set-up as my prior post...
With these values in A1:AB1
X X X X X Z Z Z X X X X X X X X X X Z Z Z Z X X X X X X
and
AE1: Char
AE2: X
AE3: Z
and
AF1: Grp Count

This REGULAR formula (improved formula) returns the number of groups containing the char
Code:
AF2: =($A$1=AE2)+SUMPRODUCT(($A$1:$AA$1<>AE2)*($B$1:$AB$1=AE2))
Copy that formula into AF3.

For the group sizes:
This ARRAY FORMULA, completed by holding CTRL and SHIFT when you press ENTER (instead of just pressing ENTER)
actually does return the size of the first group, counting from the left
Code:
AG2: =IFERROR(LARGE(COUNTIF(OFFSET($A$1,,,,COLUMN($A$1:$AB$1)),"="&$AE2)*($B$1:$AC$1<>$AE2)*($A$1:$AB$1=$AE2),$AF2),"")

And this ARRAY FORMULA continues the list of consecutive group sizes, counting from the left
Code:
AH2: =IFERROR(LARGE(COUNTIF(OFFSET($A$1,,,,COLUMN($A$1:$AB$1)),"="&$AE2)*($B$1:$AC$1<>$AE2)*($A$1:$AB$1=$AE2),$AF2-COLUMNS($AH:AH))-SUM($AG2:AG2),"")
Copy AH2 and paste into AI2:AP2

That takes care of the data for "X".
For the "Z" data....
Copy AF2:AP2 and paste into AF3:AP3

The results will be this:
Code:
Char  GrpCnt     Grp1     Grp2     Grp3     Grp4     Grp5     Grp6     Grp7     Grp8     Grp9     Grp10
X     3          5        10       6        (blank)  (blank)  (blank)  (blank)  (blank)  (blank)  (blank)
Z     2          3         4       (blank)  (blank)  (blank)  (blank)  (blank)  (blank)  (blank)  (blank)
 
Last edited:
Upvote 0
Hi Ron
It works like a charm.

With the spirit of the Olympic games,
You get the Gold medal. :)

Thank you so much for your time and efforts.

Amit Cohen.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top