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

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
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

P
  • Question
Help i can't found the xpath to continue
Replies
3
Views
184
ptcsite46
P
S
  • Solved
VBA Code Help
Replies
4
Views
275
Peter_SSs
Peter_SSs
M
  • Solved
Find 6 digit PO using Xlookup in a string of POs in a cell.
Replies
5
Views
163
Peter_SSs
Peter_SSs
S
  • Solved
Formula or VBA for Cell Range of Values
2
Replies
17
Views
286
Eric W
Eric W
R
  • Question
Macro not working after adding two new items
Replies
4
Views
104
myall_blues
myall_blues

Forum statistics

Threads
1,214,789
Messages
6,121,593
Members
449,038
Latest member
Arbind kumar

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