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

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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

montecarlo2012
  • Question Question
change display from horizontal to vertical
Replies
2
Views
100
montecarlo2012
montecarlo2012
H
  • Question Question
Transposing Data - Evaluating formula to an array error.
Replies
1
Views
42
Skyybot
Skyybot
I
  • Solved
Irregular Data Set
2
Replies
19
Views
332
IdabaMalouki
I
K
  • Solved
Index with Multiple Conditions
Replies
2
Views
65
kivikatz
K
R
  • Solved
YTD and Period Averages? Possible averageif with index/match?
Replies
2
Views
27
reacon84
R

Forum statistics

Threads
1,223,893
Messages
6,175,246
Members
452,623
Latest member
cliftonhandyman

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