Count doubles that occur ΑΤ ΤΗΕ SΑΜΕ ΤΙΜΕ

[louis128]

Hi ΤΟ ΑΛΛ Could you please help me on the following?:So i have an A column with results of 1 or 2 like that:

1
2
1
2
1
2
1
1
2
2
.
.
.
.

So the formula i need is about the last 4 numbers event ,If 2 2 (or 1 1) comes count 1, if 1 1 2 2 (or 2 2 1 1) count 2 ,if 1 1 2 2 1 1 count 3 IF 11221122 , 4etc.Thank You.

[AliGW]

Congratulations on finally starting your own thread!

So, in the sample provided, what is the answer? is it 2? And what do the dots (.) represent?

It would be easier if you uploaded a workbook.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[louis128]

Thank You Ali! So we have aseries of numbers from 1-36 which they 18 of them are 1 and the remaining 2 sa per attachment. I would like a formula to count me only the doubles
like this : 11 must be count as 1 , 22 as 1 , 11 22 must be count as 2, 11 22 11 must be count as 3, 22,11,22,11 must be count as 4.Examble: lets say the outcome is
11 22 11 the formula must count 3 ,if the next outcome is 1 then count 0.

I try to upload but i cant.

[AliGW]

Please try again. We only need a small sample of data.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[louis128]

I would like to count the doubles that are happening at the same time.

[AliGW]

In what way does it not work? Have you followed the instructions I gave? If the workbook is too large, then use a smaller workbook with a subset of your data.

[Glenn Kennedy]

No need. Set up Two Named ranges (CTRl-F3 to view/wedit):

First
=Sheet1!$A$1:INDEX(Sheet1!$A:$A,MATCH(1E+100,Sheet1!$A:$A)-1)

Second
=Sheet1!$A$2:INDEX(Sheet1!$A:$A,MATCH(1E+100,Sheet1!$A:$A))

Then use this formula:
=SUMPRODUCT(--(First=Second))

It will need to be modified if triples (1 1 1 or 2 2 2) can occur. can they?


You may need ; instead of ,

[louis128]

Thank Clenn!So much! But see the Examble: lets say the outcome is
11 22 11 the formula must count 3 ,if the next outcome is 1 then count 0.So if 11 22 11 22 is 4 but if 11 22 11 22 and next result is 2 must show 0 zero . At the file you send me if the next result is 2 it counts 5 instead of 0 zero.
Tribles no.

[Bo_Ry]

Assume that no triple number, Data in Column A.
Please try at B1

=IFERROR(MATCH(0,INDEX(--(INDEX(A:A,COUNT(A:A)-6):INDEX(A:A,COUNT(A:A))=INDEX(A:A,COUNT(A:A)-7):INDEX(A:A,COUNT(A:A)-1)),N(IF(1,{7,5,3,1}))),)-1,4)

[louis128]

Thank you Bo!!.we are very near, at the workbook you send me if 1 have in A 121122112211 it should count 5 not 4.
Second in A 1212(without double )it should show o zero.
we count all doubles if exist if no zero.
I appreciate it !!!

[Bo_Ry]

I thought you say last 4 numbers event.

If not last 4 double then

C2 with Ctrl+Shift+Enter
=IFERROR(MATCH(0,INDEX(--(A2:INDEX(A:A,COUNT(A:A))=A1:INDEX(A:A,COUNT(A:A)-1)),N(IF(1,COUNT(A:A)-INT(ROW(INDIRECT("1:"&INT(COUNT(A:A)/2)))*2)+1))),)-1,INT(COUNT(A:A)/2))

[AliGW]

As far as I can see, Louis wants to count the number of doubles that exist at the end of the list, not just the final four pairs.

[louis128]

Dear Ali I can say that Bo_Ry make the job 99%done!.Thank You .

[AliGW]

Yes, Louis, and I am trying to help you to get the 100% result you want.

[louis128]

Thank so much .You are Great!

[louis128]

Can i ask for one more thing please can i have a formula that counting 121212 etc , in this case should count 3 if 2 comes(1212122) then count 0

[Glenn Kennedy]

That contradicts your originsl requirement, where XXXX22 counts as 1. Now it counts as 0.

So what DO you want?

[louis128]

Dear Clen ,the new formula iam asking is about counting the 12 as 1 ,example: 121212 etc , in this case should count 3 if 2 comes(1212122) then count 0

[Bo_Ry]

Please try this with Ctrl+Shift+Enter

=IFERROR(INT(MATCH(0,INDEX(--(A2:INDEX(A:A,COUNT(A:A))<>A1:INDEX(A:A,COUNT(A:A)-1)),N(IF(1,COUNT(A:A)-ROW(INDIRECT("1:"&COUNT(A:A)-1))))),)/2),INT(COUNT(A:A)/2))

[louis128]

Thank You!I wish You the best!

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.