Count "0" and "1" in a single cell separated by vertical bar

K

Kishan

Well-known Member
Joined
Mar 15, 2011
Messages
1,648
Office Version
  1. 2010
Platform
  1. Windows
Using Excel 2000</SPAN></SPAN>
Hi,</SPAN></SPAN>

Data in columns C:P, need to count "0" and "1" in a single cell separated by vertical bar in the column R </SPAN></SPAN>

Example data</SPAN></SPAN>


Book1
ABCDEFGHIJKLMNOPQRST
1
2
3
4
5P1P2P3P4P5P6P7P8P9P10P11P12P13P14EMCount 0 And 1
60000000000000014
7000100110100113 | 1 | 2 | 2 | 1 | 1 | 2 | 2
801110101001101u
9011001011001111 | 2 | 2 | 1 | 1 | 2 | 2 | 3
10101011011000111 | 1 | 1 | 2 | 1 | 2 | 3 | 2
11000000110111106 | 2 | 1 | 4 | 1
12011011101101011 | 2 | 1 | 3 | 1 | 2 | 1 | 1 | 1 | 1
13100010111011011 | 3 | 1 | 1 | 3 | 1 | 2 | 1 | 1
14111000010010003 | 4 | 1 | 2 | 1 | 3
15100111110110011 | 2 | 5 | 1 | 2 | 2
16010010000111001 | 1 | 2 | 1 | 4 | 3 | 2
17110101011101012 | 1 | 1 | 1 | 1 | 1 | 3 | 1 | 1 | 1 | 1
18111110111000115 | 1 | 3 | 3 | 2
19011001111011001 | 2 | 2 | 4 | 1 | 2 | 2
20111101001001004 | 1 | 1 | 2 | 1 | 2 | 1 | 2
21111001101011013 | 2 | 2 | 1 | 1 | 1 | 2 | 1 | 1
22010110111011101 | 1 | 1 | 2 | 1 | 3 | 1 | 3 | 1
23111110110100015 | 1 | 2 | 1 | 1 | 3 | 1
24011011011011101 | 2 | 1 | 2 | 1 | 2 | 1 | 3 | 1
25111101111111114 | 1 | 9
26011110110011011 | 4 | 1 | 2 | 2 | 2 | 1 | 1
27100000101110111 | 5 | 1 | 1 | 3 | 1 | 2
28111010000010103 | 1 | 1 | 5 | 1 | 1 | 1 | 1
29111001010101003 | 2 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 2
30111011101101103 | 1 | 3 | 1 | 2 | 1 | 2 | 1
31111011111111113 | 1 | 10
32101101100010011 | 1 | 2 | 1 | 2 | 3 | 1 | 2 | 1
33001101001001012 | 2 | 1 | 1 | 2 | 1 | 2 | 1 | 1 | 1
34101011111101011 | 1 | 1 | 1 | 6 | 1 | 1 | 1
35011100100110011 | 3 | 2 | 1 | 2 | 2 | 2 | 1
36100011001101001 | 3 | 2 | 2 | 2 | 1 | 1 | 2
37011111111000001 | 8 | 5
38001100111001112 | 2 | 2 | 3 | 2 | 3
39010101000001101 | 1 | 1 | 1 | 1 | 1 | 5 | 2 | 1
40111001001010013 | 2 | 1 | 2 | 1 | 1 | 1 | 2 | 1
41000110111100113 | 2 | 1 | 4 | 2 | 2
42000101011010013 | 1 | 1 | 1 | 1 | 2 | 1 | 1 | 2 | 1
43001001011111012 | 1 | 2 | 1 | 1 | 5 | 1 | 1
44001011110101102 | 1 | 1 | 4 | 1 | 1 | 1 | 2 | 1
45110110111110112 | 1 | 2 | 1 | 5 | 1 | 2
46111100011111014 | 3 | 5 | 1 | 1
47001111101111012 | 5 | 1 | 4 | 1 | 1
48101001011111111 | 1 | 1 | 2 | 1 | 1 | 7
49010101010000011 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 5 | 1
50011110111111101 | 4 | 1 | 7 | 1
51010110001000111 | 1 | 1 | 2 | 3 | 1 | 3 | 2
52011100111111001 | 3 | 2 | 6 | 2
53011000111110001 | 2 | 3 | 5 | 3
54010010001001001 | 1 | 2 | 1 | 3 | 1 | 2 | 1 | 2
55000101101101013 | 1 | 1 | 2 | 1 | 2 | 1 | 1 | 1 | 1
561111111111111114
57
58
Sheet1


Thank you in advance</SPAN></SPAN>

Regards,</SPAN>
Kishan</SPAN></SPAN>
 
Last edited:

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
Sort by date Sort by votes
Create a module in VBA and put in the following code:

Code: 
Function CountGroups(Rng As Range)

Dim Cel As Range
Dim output As String
Dim CheckValue As String
Dim Counter As Integer
Counter = 0
output = ""
CheckValue = Rng(1).Value
For Each Cel In Rng
    If CheckValue = Cel.Value Then
        Counter = Counter + 1
    Else
        CheckValue = Cel.Value
        If Len(output) > 0 Then
            output = output & "|" & Counter
        Else
            output = Counter
        End If
        Counter = 1
    End If
Next
        If Len(output) > 0 Then
        output = output & "|" & Counter
        Else
           output = Counter
        End If
CountGroups = output
End Function

And then in the cell you want you can use this formula:

Code: 
=Countgroups(C1:P1)

This will work for any values and any range.
 
Upvote 0
Puertorekinsam said:
Create a module in VBA and put in the following code:

Code: 
Function CountGroups(Rng As Range)

End Function

And then in the cell you want you can use this formula:

Code: 
=Countgroups(C1:P1)

This will work for any values and any range.
Click to expand...
Hi, Puertorekinsam thank you for the Function it is giving a result as request and as you said it works perfect for any values and any range too.</SPAN></SPAN>

I do appreciate your kind help. Just a question could it be made a macro, why? Because the functions are always active and do not live the values.</SPAN></SPAN>

Thank you

Kind Regards,</SPAN></SPAN>
Kishan</SPAN></SPAN>
 
Upvote 0
Try this for all results in column "R":-
Code: 
[COLOR="Navy"]Sub[/COLOR] MG23Oct05
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] nStr [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Temp [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range("C6", Range("C" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng

[COLOR="Navy"]For[/COLOR] Ac = 1 To 14
    [COLOR="Navy"]If[/COLOR] Not Dn(, Ac) = Temp [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]If[/COLOR] c > 0 [COLOR="Navy"]Then[/COLOR]
            nStr = nStr & IIf(nStr = "", c, "|" & c)
            c = 0
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]End[/COLOR] If
    c = c + 1: Temp = Dn(, Ac)
[COLOR="Navy"]Next[/COLOR] Ac


[COLOR="Navy"]If[/COLOR] Not Dn(, Ac) = Temp [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]If[/COLOR] c > 0 [COLOR="Navy"]Then[/COLOR]
        nStr = nStr & IIf(nStr = "", c, "|" & c)
        c = 0
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] If

Dn.Offset(, 15).Value = nStr

nStr = "": c = 0
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
I would keep the function as it is, and call it as a separate sub routine

Code: 
Sub RunTheCode()

Dim i As Integer

i = 1

Do Until Cells(i, 3) = ""
    Cells(i, 18) = CountGroups(Range(Cells(i, 3), Cells(i, 16)))

    i = i + 1
Loop
End Sub
 
Upvote 0
MickG said:
Try this for all results in column "R":-
Code: 
[COLOR=navy]Sub[/COLOR] MG23Oct05
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range, Ac [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] c [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] nStr [COLOR=navy]As[/COLOR] [COLOR=navy]String[/COLOR]
[COLOR=navy]Dim[/COLOR] Temp [COLOR=navy]As[/COLOR] [COLOR=navy]String[/COLOR]
[COLOR=navy]Set[/COLOR] Rng = Range("C6", Range("C" & Rows.Count).End(xlUp))
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng

[COLOR=navy]For[/COLOR] Ac = 1 To 14
    [COLOR=navy]If[/COLOR] Not Dn(, Ac) = Temp [COLOR=navy]Then[/COLOR]
        [COLOR=navy]If[/COLOR] c > 0 [COLOR=navy]Then[/COLOR]
            nStr = nStr & IIf(nStr = "", c, "|" & c)
            c = 0
        [COLOR=navy]End[/COLOR] If
    [COLOR=navy]End[/COLOR] If
    c = c + 1: Temp = Dn(, Ac)
[COLOR=navy]Next[/COLOR] Ac


[COLOR=navy]If[/COLOR] Not Dn(, Ac) = Temp [COLOR=navy]Then[/COLOR]
    [COLOR=navy]If[/COLOR] c > 0 [COLOR=navy]Then[/COLOR]
        nStr = nStr & IIf(nStr = "", c, "|" & c)
        c = 0
    [COLOR=navy]End[/COLOR] If
[COLOR=navy]End[/COLOR] If

Dn.Offset(, 15).Value = nStr

nStr = "": c = 0
[COLOR=navy]Next[/COLOR] Dn
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
Regards Mick
Click to expand...
MickG, I liked the macro solution and it worked Prefect!! </SPAN></SPAN>

Thank you for your time and kind help</SPAN></SPAN>

Kind Regards,</SPAN></SPAN>
Kishan :)</SPAN></SPAN>
 
Upvote 0
Puertorekinsam said:
I would keep the function as it is, and call it as a separate sub routine

Code: 
Sub RunTheCode()

Dim i As Integer

i = 1

Do Until Cells(i, 3) = ""
    Cells(i, 18) = CountGroups(Range(Cells(i, 3), Cells(i, 16)))

    i = i + 1
Loop
End Sub
Click to expand...
Hi, Puertorekinsam I tried putting the "Sub RunTheCode" in the same "Function" module, when I run it do not get any result, and then tried in another module but no results. Don't know what I am doing wrong</SPAN></SPAN>

Please could you check?</SPAN></SPAN>

Thank you</SPAN></SPAN>
Kishan </SPAN></SPAN>

 
Last edited:
Upvote 0
Put the sub in the code for the individual sheet.

Or add "Sheets("NAME OF SHEET")." infront of the word Cells and Range in the sub.
 
Upvote 0
Kishan said:
MickG, I liked the macro solution and it worked Prefect!! </SPAN></SPAN>

Thank you for your time and kind help</SPAN></SPAN>

Kind Regards,</SPAN></SPAN>
Kishan :)</SPAN></SPAN>
Click to expand...
MickG, would you please do a favour I find my mistake row 6 and row 56 the result are 14, but row 6 starting with 0 & the row 56 starting with 1, to make them distinction I want please can you alter a code (just adding a 1st "1" or "0" striating of the each row as shown below</SPAN></SPAN>


Book1
ABCDEFGHIJKLMNOPQRST
1
2
3
4
5P1P2P3P4P5P6P7P8P9P10P11P12P13P14EMCount 0 And 1
6000000000000000 - 14
7000100110100110 - 3|1|2|2|1|1|2|2
8011101010011010 - 1|3|1|1|1|1|2|2|1|1
9011001011001110 - 1|2|2|1|1|2|2|3
10101011011000111 - 1|1|1|1|2|1|2|3|2
11000000110111100 - 6|2|1|4|1
12011011101101010 - 1|2|1|3|1|2|1|1|1|1
13100010111011011 - 1|3|1|1|3|1|2|1|1
14111000010010001 - 3|4|1|2|1|3
15100111110110011 - 1|2|5|1|2|2|1
16010010000111000 - 1|1|2|1|4|3|2
17110101011101011 - 2|1|1|1|1|1|3|1|1|1|1
18111110111000111 - 5|1|3|3|2
19011001111011000 - 1|2|2|4|1|2|2
20111101001001001 - 4|1|1|2|1|2|1|2
21111001101011011 - 3|2|2|1|1|1|2|1|1
22010110111011100 - 1|1|1|2|1|3|1|3|1
23111110110100011 - 5|1|2|1|1|3|1
24011011011011100 - 1|2|1|2|1|2|1|3|1
25111101111111111 - 4|1|9
26011110110011010 - 1|4|1|2|2|2|1|1
27100000101110111 - 1|5|1|1|3|1|2
28111010000010101 - 3|1|1|5|1|1|1|1
29111001010101001 - 3|2|1|1|1|1|1|1|1|2
30111011101101101 - 3|1|3|1|2|1|2|1
31111011111111111 - 3|1|10
32101101100010011 - 1|1|2|1|2|3|1|2|1
33001101001001010 - 2|2|1|1|2|1|2|1|1|1
34101011111101011 - 1|1|1|1|6|1|1|1|1
35011100100110010 - 1|3|2|1|2|2|2|1
36100011001101001 - 1|3|2|2|2|1|1|2
37011111111000000 - 1|8|5
38001100111001110 - 2|2|2|3|2|3
39010101000001100 - 1|1|1|1|1|1|5|2|1
40111001001010011 - 3|2|1|2|1|1|1|2|1
41000110111100110 - 3|2|1|4|2|2
42000101011010010 - 3|1|1|1|1|2|1|1|2|1
43001001011111010 - 2|1|2|1|1|5|1|1
44001011110101100 - 2|1|1|4|1|1|1|2|1
45110110111110111 - 2|1|2|1|5|1|2
46111100011111011 - 4|3|5|1|1
47001111101111010 - 2|5|1|4|1|1
48101001011111111 - 1|1|1|2|1|1|7
49010101010000010 - 1|1|1|1|1|1|1|1|5|1
50011110111111100 - 1|4|1|7|1
51010110001000110 - 1|1|1|2|3|1|3|2
52011100111111000 - 1|3|2|6|2
53011000111110000 - 1|2|3|5|3
54010010001001000 - 1|1|2|1|3|1|2|1|2
55000101101101010 - 3|1|1|2|1|2|1|1|1|1
56111111111111111 - 14
57
58
Sheet2


Thank you</SPAN></SPAN>

Kind Regards,</SPAN></SPAN>
Kishan</SPAN></SPAN>
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Solved
Irregular Data Set
2
Replies
19
Views
320
IdabaMalouki
I
R
  • Question Question
Move row to bottom of range is cell value is zero
Replies
6
Views
270
Peter_SSs
Peter_SSs
J
  • Question Question
Calculation based on value in table based on date range
Replies
0
Views
227
jsand
J
C
  • Question Question
Searching using TextBox and ListBox
Replies
1
Views
197
campbell1093
C
H
  • Question Question
Positioning active cell when adding table rows.
Replies
3
Views
161
myall_blues
myall_blues

Forum statistics

Threads
1,223,227
Messages
6,170,849
Members
452,361
Latest member
d3ad3y3

Share this page

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