Count if 3 character "1 X 2" are find in the 3 rows.

Kishan · Oct 24, 2017

Using Excel 2000

Hi,

Count if 3 character "1 X 2" are find in the 3 rows no matter the if they are not in sequence (I mean can be "1X2", "X12", "2X1" or in any sequence no problem)

Data are in cells C6:I80 (count results in K6:K80

Look "1X2" into 3 rows STARTING FROM C6 for example....

Step1-Start looking in C6, C7, and C8 - D6, D7, and D8 - E6, E7, and E8 - F6, F7, and F8 - G6, G7, and G8 - H6, H7, and H8 - I6, I7, and I8 (DO NOT FOUND "1X2" RESULT = IN K8=0)

Step2-Start looking IN NEXT 3 ROWS C7, C8, and C9 - D7, D8, and D9 - E7, E8, and E9 - F7, F8, and F9 - G7, G8, and G9 - H7, H8, and H9 - I7, I8, and I8 (FOUND "1X2" in - I7, I8, and I8 RESULT = IN K9=1

Step3-Start looking IN NEXT 3 ROWS C8, C9, and C10 - D8, D9, and D10 - E8, E9, and E10 - F8, F9, and F10 - G8, G9, and G10 - H8, H9, and H10 - I8, I9, and I10 (FOUND "1X2" in - C8, C9, and C10 RESULT = IN K10=1

And continue finding into next 3 rows till end and result in column K

Example....

Book1

A

B

C

D

E

F

G

H

I

J

K

L

1

2

3

4

Cycle

5

C1

C2

C3

C4

C5

C6

C7

Completad

6

X

1

2

1

2

7

X

2

1

X

1

8

X

1

2

1

2

0

9

2

1

X

2

1

X

1

10

1

X

1

X

1

2

1

11

X

2

X

2

X

1

3

12

X

1

2

1

13

1

X

2

1

X

1

14

1

2

X

15

1

X

1

16

X

1

X

2

1

2

X

17

1

X

1

2

1

X

18

1

X

1

19

X

1

X

1

20

1

2

1

X

21

1

2

1

22

2

1

X

1

X

1

23

1

2

1

24

X

1

2

1

2

25

1

X

1

26

X

1

2

1

27

1

2

X

1

28

1

2

1

2

X

29

1

2

1

X

30

1

X

1

31

1

X

1

32

1

X

1

2

1

33

2

1

X

1

X

34

1

X

1

2

1

35

2

1

2

X

1

36

1

X

2

1

2

37

X

1

38

X

2

1

X

1

39

X

1

2

1

2

1

X

40

1

X

1

2

41

X

1

X

1

42

2

X

2

1

X

1

X

43

X

1

2

X

1

2

44

X

1

X

1

45

1

X

1

2

1

46

1

X

2

47

2

X

1

48

1

2

1

X

2

1

2

49

X

1

X

1

2

1

2

50

X

1

X

1

51

X

1

X

1

X

2

52

X

1

X

1

53

X

1

2

X

1

54

2

1

X

2

1

X

55

X

2

1

X

1

56

1

X

2

X

1

2

1

57

X

2

X

1

58

1

X

1

X

1

59

1

X

1

X

60

2

1

X

1

2

61

X

1

2

X

1

62

2

1

2

1

63

1

2

1

64

1

X

2

1

X

1

65

1

X

1

X

1

X

66

X

1

67

X

2

1

2

1

68

1

2

1

69

X

1

X

1

70

1

2

X

1

X

71

1

X

1

2

X

72

1

X

1

X

73

1

X

1

X

1

X

2

74

1

X

2

1

75

1

X

1

X

1

76

1

X

1

2

77

2

X

1

X

1

78

1

X

1

2

79

1

X

1

80

1

2

1

X

81

82

Sheet2

Thank you in advance

Regards,
Kishan

Kishan · Oct 24, 2017

Eric W said:

I got Kenneth's UDF to work fine. But in case you still have issues getting it to work, here's a basic subroutine you can try:

Code:

Sub CheckCycle()
Dim r As Long, c As Long, ctr As Long, x As Boolean

    For r = 9 To Cells(Rows.Count, "C").End(xlUp).Row
        ctr = 0
        For c = 3 To 9
            x = (WorksheetFunction.CountIf(Cells(r - 2, c).Resize(3), 1) > 0) And _
                (WorksheetFunction.CountIf(Cells(r - 2, c).Resize(3), 2) > 0) And _
                (WorksheetFunction.CountIf(Cells(r - 2, c).Resize(3), "X") > 0)
            If x Then ctr = ctr + 1
        Next c
        Cells(r, "K") = ctr
    Next r
    
End Sub

Thank you Eric, spot on!! It is resulting as request 100% Ok.

I appreciate very much your help and time.

Good Luck

Regards,
Kishan

Kishan · Oct 25, 2017

Hi, well it is solved; as per opening post I need if possible could be modified existing code or new one that can check "1X2" cycle within 4 rows rather than 3

Look "1X2" pattern into 4 rows STARTING FROM C6 for example....

Step1-Start looking in C6, C7, C8 and C9 - D6, D7, D8 and D9 - E6, E7, E8 and E9 - F6, F7, F8 and F9 - G6, G7, G8 and G9 - H6, H7, H8 and H9 - I6, I7, I8 and I9 (FOUND "221X" in - I6, I7, I8 and I9 RESULT IN K9=1

And continue finding into next 4 rows till end and result in column K

PATTERN COULD BE ANY "212X", "XX21", "112X", "11X2", 2X21"
IF START WITH "1" 4TH CHARECTER SHOULD BE END WITH "X" or "2"
IF START WITH "X" 4TH CHARECTER SHOULD BE END WITH "1" or "2"
IF START WITH "2" 4TH CHARECTER SHOULD BE END WITH "1" or "X"

Example....

Book1

A

B

C

D

E

F

G

H

I

J

K

L

1

2

3

4

Cycle

5

C1

C2

C3

C4

C5

C6

C7

Completad

6

X

1

2

1

2

7

X

2

1

X

1

8

X

1

2

1

2

9

2

1

X

2

1

X

1

10

1

X

1

X

1

2

11

X

2

X

2

X

1

2

12

X

1

2

1

13

1

X

2

1

X

1

14

1

2

X

15

1

X

1

16

X

1

X

2

1

2

X

17

1

X

1

2

1

X

18

1

X

1

19

X

1

X

1

20

1

2

1

X

21

1

2

1

22

2

1

X

1

X

1

23

1

2

1

24

X

1

2

1

2

25

1

X

1

26

X

1

2

1

27

1

2

X

1

28

1

2

1

2

X

29

1

2

1

X

30

1

X

1

31

1

X

1

32

1

X

1

2

1

33

2

1

X

1

X

34

1

X

1

2

1

35

2

1

2

X

1

36

1

X

2

1

2

37

X

1

38

X

2

1

X

1

39

X

1

2

1

2

1

X

40

1

X

1

2

41

X

1

X

1

42

2

X

2

1

X

1

X

43

X

1

2

X

1

2

44

X

1

X

1

45

1

X

1

2

1

46

1

X

2

47

2

X

1

48

1

2

1

X

2

1

2

49

X

1

X

1

2

1

2

50

X

1

X

1

51

X

1

X

1

X

2

52

X

1

X

1

53

X

1

2

X

1

54

2

1

X

2

1

X

55

X

2

1

X

1

56

1

X

2

X

1

2

1

57

X

2

X

1

58

1

X

1

X

1

59

1

X

1

X

60

2

1

X

1

2

61

X

1

2

X

1

62

2

1

2

1

63

1

2

1

64

1

X

2

1

X

1

65

1

X

1

X

1

X

66

X

1

67

X

2

1

2

1

68

1

2

1

69

X

1

X

1

70

1

2

X

1

X

71

1

X

1

2

X

72

1

X

1

X

73

1

X

1

X

1

X

2

74

1

X

2

1

75

1

X

1

X

1

76

1

X

1

2

77

2

X

1

X

1

78

1

X

1

2

79

1

X

1

80

1

2

1

X

81

82

Sheet3

Thank you in advance

Regards,
Kishan

Eric W · Oct 25, 2017

You just need to change the long comparison line:

Rich (BB code):

Sub CheckCycle()
Dim r As Long, c As Long, ctr As Long, x As Boolean

    For r = 9 To Cells(Rows.Count, "C").End(xlUp).Row
        ctr = 0
        For c = 3 To 9
            x = (WorksheetFunction.CountIf(Cells(r - 3, c).Resize(4), 1) > 0) And _
                (WorksheetFunction.CountIf(Cells(r - 3, c).Resize(4), 2) > 0) And _
                (WorksheetFunction.CountIf(Cells(r - 3, c).Resize(4), "X") > 0) And _
                (Cells(r - 3, c) <> Cells(r, c))
            If x Then ctr = ctr + 1
        Next c
        Cells(r, "K") = ctr
    Next r
    
End Sub

Kishan · Oct 25, 2017

Eric W said:

You just need to change the long comparison line:

Rich (BB code):

Sub CheckCycle()
Dim r As Long, c As Long, ctr As Long, x As Boolean

    For r = 9 To Cells(Rows.Count, "C").End(xlUp).Row
        ctr = 0
        For c = 3 To 9
            x = (WorksheetFunction.CountIf(Cells(r - 3, c).Resize(4), 1) > 0) And _
                (WorksheetFunction.CountIf(Cells(r - 3, c).Resize(4), 2) > 0) And _
                (WorksheetFunction.CountIf(Cells(r - 3, c).Resize(4), "X") > 0) And _
                (Cells(r - 3, c) <> Cells(r, c))
            If x Then ctr = ctr + 1
        Next c
        Cells(r, "K") = ctr
    Next r
    
End Sub

Thank you so much Eric, for your kind help it is working perfect as per new request.

Have a nice day

Good Luck

Regards,
Kishan

Kishan · Oct 25, 2017

Hi Eric,

I think modified code is looking 3 characters "1X2" into 4 rows but not the cycle

Cycle of 3 characters "1X2" for example...
If cycle start with "1" has to finish with "X or 2"
If cycle start with "X" has to finish with "1 or 2"
If cycle start with "2" has to finish with "1 or X"

I have calculated manually and shown expected results in column K and in the column L results are generated by the macro

Results require as per column K

Book1

A

B

C

D

E

F

G

H

I

J

K

L

M

1

2

3

4

Cycle 4 Rows

5

C1

C2

C3

C4

C5

C6

C7

Result Ok

By Macro

6

X

1

2

1

2

7

X

2

1

X

1

8

X

1

2

1

2

9

2

1

X

2

1

X

1

10

1

X

1

X

1

2

3

11

X

2

X

2

X

1

2

12

X

1

2

1

0

2

13

1

X

2

1

X

1

0

14

1

2

X

1

2

15

1

X

1

0

16

X

1

X

2

1

2

X

1

17

1

X

1

2

1

X

1

2

18

1

X

1

19

X

1

X

1

2

3

20

Sheet3

Please could you check?

Thank you in advance

Regards,
Kishan

Eric W · Oct 25, 2017

I don't see the problem. In K10, you say you expect 2, when the macro returns 3. C7:C10 = X,X,2,1. G7:G10 = X,2,2,1. I7:I10 = 1,2,X,2. Each of those sets contains 1,2, and X, and each of them ends with a different character than it starts with. Which one of them should not be counted, and why?

Kishan · Oct 25, 2017

Eric W said:
I don't see the problem. In K10, you say you expect 2, when the macro returns 3. C7:C10 = X,X,2,1. G7:G10 = X,2,2,1. I7:I10 = 1,2,X,2. Each of those sets contains 1,2, and X, and each of them ends with a different character than it starts with. Which one of them should not be counted, and why?

Hi Eric, thank you for your reply

This is ok C7:C10 = X, X, 2,1. Cycle is perfect started with X and end with 1
This is ok G7:G10 = X, 2, 2,1. Cycle is perfect started with X and end with 1

This is not ok I7:I10 = 1, 2, X, 2. If you look this pattern first three characters 1, 2, X has completed the cycle and 2 is creating a new cycle

I mean repeated characters could be in-between 1st and 4th characters
So far I7:I10 = 1, 2, X, 2. If this would have been 1, 2, 2, X were ok

More examples ´
X, 2, 1, 2 not ok, but X, 2, 2, 1 is ok
2, 1, X, 2 not ok, but 2, 1, 2, X is ok

Hope this help

Regards,
Kishan

Eric W · Oct 25, 2017

Say you have this sequence: 1, 2, X, 2, 1 starting on row 6. Should the 1,2,X be included in the total on row 8? Should the 1,2,X,2 be counted on row 9, because there is not a total on row 8? Should the X,2,1 be included in the total on row 10, since the X has been included in either row 8 or 9?

If I have 1,2,X,1,2,X,1,2,X,1,2,X,1,2 I won't count it on row 8 even though it completes the cycle. I won't count it on row 9 because it starts and ends with 1. I won't count it on row 10 because it starts and ends with 2. I won't count it on 11 because it starts and ends with X. In short, it won't count any of the completed cycles?

Kishan · Oct 25, 2017

Eric W said:
Say you have this sequence: 1, 2, X, 2, 1 starting on row 6. Should the 1,2,X be included in the total on row 8? Should the 1,2,X,2 be counted on row 9, because there is not a total on row 8? Should the X,2,1 be included in the total on row 10, since the X has been included in either row 8 or 9?

If I have 1,2,X,1,2,X,1,2,X,1,2,X,1,2 I won't count it on row 8 even though it completes the cycle. I won't count it on row 9 because it starts and ends with 1. I won't count it on row 10 because it starts and ends with 2. I won't count it on 11 because it starts and ends with X. In short, it won't count any of the completed cycles?

Hi Eric, thank you for your reply

Yes I understand your viewpoint you are correct the way you are thinking.

1st of all I want to look cycle in the 4 rows

Found 1 cycle in the row 6, 7, 8, 9 only in the column I, started with 2 followed by 1 (not closed by X) than followed by 2 again and finally looked by missing X

Book1

A

B

C

D

E

F

G

H

I

J

K

L

1

2

3

4

Cycle 4 Rows

5

C1

C2

C3

C4

C5

C6

C7

Result Ok

By Macro

6

X

1

2

1

2

7

X

2

1

X

1

8

X

1

2

1

2

9

2

1

X

2

1

X

1

Sheet3

Found 2 cycle in the row 7, 8, 9, 10 in the column C, started with X than followed by X again than followed by 2 again and finally looked by missing 1 & in G started with X
followed by 2 (not closed by 1) than followed by 2 again and finally looked by missing

Book1

A

B

C

D

E

F

G

H

I

J

K

L

1

2

3

4

Cycle 4 Rows

5

C1

C2

C3

C4

C5

C6

C7

Result Ok

By Macro

6

7

X

2

1

X

1

8

X

1

2

1

2

9

2

1

X

2

1

X

10

1

X

1

X

1

2

3

Sheet3

Found 1 cycle in the row 8, 9, 10, 11 in the column I

Book1

A

B

C

D

E

F

G

H

I

J

K

L

1

2

3

4

Cycle 4 Rows

5

C1

C2

C3

C4

C5

C6

C7

Result Ok

By Macro

6

7

8

X

1

2

1

2

9

2

1

X

2

1

X

10

1

X

1

X

1

2

11

X

2

X

2

X

1

2

Sheet3

Not found any cycle in the row 9, 10, 11, 11 in any column

Book1

A

B

C

D

E

F

G

H

I

J

K

L

1

2

3

4

Cycle 4 Rows

5

C1

C2

C3

C4

C5

C6

C7

Result Ok

By Macro

6

7

8

9

2

1

X

2

1

X

10

1

X

1

X

1

2

11

X

2

X

2

X

1

12

X

1

2

1

0

2

Sheet3

So these ways keep tracking into 4 next rows

Regards,
Kishan

Kishan · Oct 25, 2017

Hi Eric, I tried to make all possible pattern could be made which complete the cycle. I found total 18, if find any out of these 18 within 4 rows can be counted for the total

Here are the pattern cycle lists....

Book1

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

1

2

3

4

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

5

6

1

X

2

7

1

X

2

1

X

2

1

X

2

8

X

2

1

X

1

2

1

X

1

2

X

2

1

2

X

2

1

X

9

2

X

2

X

2

1

X

1

X

1

10

11

Sheet1

Hope this help

Regards,
Kishan

Count if 3 character "1 X 2" are find in the 3 rows.

Kishan

Well-known Member

Kishan

Well-known Member

Excel Facts

Kishan

Well-known Member

Eric W

MrExcel MVP

Kishan

Well-known Member

Kishan

Well-known Member

Eric W

MrExcel MVP

Kishan

Well-known Member

Eric W

MrExcel MVP

Kishan

Well-known Member

Kishan

Well-known Member

Similar threads

Forum statistics

Share this page

Count if 3 character "1 X 2" are find in the 3 rows.

Well-known Member

Well-known Member

Excel Facts

Well-known Member

MrExcel MVP

Well-known Member

Well-known Member

MrExcel MVP

Well-known Member

MrExcel MVP

Well-known Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock