Separate counts depending on start patterns

Kishan · Sep 17, 2018

Using Excel 2000

Hi,

Note: Fill colours not required, it is just to shown to explain the example clearer

I want a Formula or batter will be the VBA solution to " Separate counts depending on start patterns ", data in column C:D, count result in columns L:Q & in the S:X

Count examples of the row 6, starting C6 has 1|X, so count instance and put them in cell L6 = 2 (Not in the S6), then look count for reverse match pattern X|1=1 put them in cell M6 =1 (not in the T6) then E3= 1|2 put them in N6 (Not in the V6)

So the count must be as per left to right order and match put in to L:Q & S:X as per order too. 

Result data example

Book1

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

1

2

3

4

5

P1

P2

P3

P4

P5

P6

P7

1|X

X|1

1|2

2|1

X|2

2|X

X|1

1|X

2|1

1|2

2|X

X|2

6

1|X

X|1

1|2

1|1

2|2

1|X

1|1

2

1

7

X|1

1|X

1|1

1|X

X|1

4

2

8

1|X

1|2

2|1

X|1

1|X

1|2

2|1

2

1

2

9

2|X

1|1

1|2

X|X

X|1

1|1

2|1

1

10

1|X

2|2

2|1

X|X

2|2

X|X

1|1

1

11

1|X

1|1

X|X

2|2

X|X

1|1

X|X

1

12

X|X

X|1

1|1

1|X

2|1

2|X

1

2

1

13

1|X

X|1

2|1

1|1

2|X

1

2

14

X|2

X|1

1|X

1|1

X|1

1|X

1

2

15

1|1

X|X

X|1

1|1

2|X

1

16

1|2

X|1

1|2

X|2

1|1

1|X

3

1

17

1|X

X|1

1|2

X|X

1|X

1|1

X|1

2

1

18

1|1

1|X

1|1

X|1

1|1

1

19

X|1

2|X

1|1

1|2

X|1

1|2

X|X

2

1

20

1|1

1|X

X|1

1|1

2

21

1|1

1|X

X|2

1|1

1|2

1

22

1|1

2|X

X|1

1

23

1|2

2|1

1|1

1|X

1|1

1

24

1|1

X|1

1|1

2|1

1

25

X|X

X|1

1|1

X|1

1|2

X|1

1|1

3

1

26

X|2

1|1

2|1

1|1

X|X

1|X

1

27

1|X

2|2

1|1

1|2

X|X

1|1

1|X

2

1

28

2|1

X|X

1|1

X|X

1|X

1|1

1

29

X|1

2|1

2|X

X|1

1|2

X|1

1|2

3

1

2

1

30

X|1

X|X

1|1

1|X

1|1

1

31

32

Sheet7

Thank you in advance

Regards,
Kishan

Fluff · Sep 17, 2018

In L6
=COUNTIF($C6:$I6,L$5)
then copy across & down

Kishan · Sep 17, 2018

Fluff said:
In L6
=COUNTIF($C6:$I6,L$5)
then copy across & down

Hi Fluff, it is a general formula that does not look the order see the result are not exactly what I want.

Book1

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

1

2

3

4

5

P1

P2

P3

P4

P5

P6

P7

1|X

X|1

1|2

2|1

X|2

2|X

X|1

1|X

2|1

1|2

2|X

X|2

6

1|X

X|1

1|2

1|1

2|2

1|X

1|1

2

1

2

1

7

X|1

1|X

1|1

1|X

X|1

2

4

2

8

1|X

1|2

2|1

X|1

1|X

1|2

2|1

2

1

2

1

2

9

2|X

1|1

1|2

X|X

X|1

1|1

2|1

1

10

1|X

2|2

2|1

X|X

2|2

X|X

1|1

1

11

1|X

1|1

X|X

2|2

X|X

1|1

X|X

1

12

X|X

X|1

1|1

1|X

2|1

2|X

2

1

2

1

13

1|X

X|1

2|1

1|1

2|X

1

2

1

2

14

X|2

X|1

1|X

1|1

X|1

1|X

2

1

2

1

15

1|1

X|X

X|1

1|1

2|X

1

16

1|2

X|1

1|2

X|2

1|1

1|X

1

3

1

3

1

17

1|X

X|1

1|2

X|X

1|X

1|1

X|1

2

1

2

1

18

1|1

1|X

1|1

X|1

1|1

1

19

X|1

2|X

1|1

1|2

X|1

1|2

X|X

2

1

2

1

20

1|1

1|X

X|1

1|1

2

21

1|1

1|X

X|2

1|1

1|2

1

22

1|1

2|X

X|1

1

23

1|2

2|1

1|1

1|X

1|1

1

24

1|1

X|1

1|1

2|1

1

25

X|X

X|1

1|1

X|1

1|2

X|1

1|1

3

1

3

1

26

X|2

1|1

2|1

1|1

X|X

1|X

1

27

1|X

2|2

1|1

1|2

X|X

1|1

1|X

2

1

2

1

28

2|1

X|X

1|1

X|X

1|X

1|1

1

29

X|1

2|1

2|X

X|1

1|2

X|1

1|2

3

2

1

3

1

2

1

30

X|1

X|X

1|1

1|X

1|1

1

31

32

Sheet7-1

These count are bit tricky, I have separated them in 2 groups one in the column L:Q & other in the column S:X, here I give you an example of pattern in cells L5: L6 are 1|X, X|1 and in the cells S5:T5 are in reverse order X|1, 1|X, so far count trick is if starting of the row it is 1|X than count will go in L:M, or if starting of the row it is X|1 than count will go in S:T that is why row 6 count in L6:M6 and Row 7 count are in S6:T6

Hope this helps

Thank you

Regards,
Kishan

Fluff · Sep 17, 2018

I had not fully understood your requirement and unfortunately, cannot figure out how to do it.

Rick Rothstein · Sep 17, 2018

Kishan said:
These count are bit tricky, I have separated them in 2 groups one in the column L:Q & other in the column S:X, here I give you an example of pattern in cells L5: L6 are 1|X, X|1 and in the cells S5:T5 are in reverse order X|1, 1|X, so far count trick is if starting of the row it is 1|X than count will go in L:M, or if starting of the row it is X|1 than count will go in S:T that is why row 6 count in L6:M6 and Row 7 count are in S6:T6

I am having trouble deciphering your count requirement. Can you explain why the counts for Rows 12, 26, 28 are in the columns they are in?

Kishan · Sep 17, 2018

Fluff said:
I had not fully understood your requirement and unfortunately, cannot figure out how to do it.

Hi Fluff, in the data column C:I, there are total 9 unique patterns, we can put them in 4 different groups as shown below 
Group1 - 1|1, X|X, 2,2
Group2 - 1|X, X|1
Group3 - 1|2, 2|1
Group4 - X|2, 2|X 

Group1 - has only one order, whereever the can appear in the row there is no reverse match. It will look always like the same 1|1, X|X, 2|2 the can not be reversed

Group2- can be appear in the row as it original order first 1|X and after X|1, or May it can come first X|1 and after 1|X

Group3- can be appear in the row as it original order first 1|2 and after 2|1, or May it can come first 2|1 and after 1|2

Group4- can be appear in the row as it original order first X|2 and after 2|X, or May it can come first 2|X and after X|2
So what I did group 2, 3 & 4 I have distributed in 2 groups1st-in the original order and 2nd-in their reverse order as they can be appeared 

Original order - 1|X, X|1, 1|2, 2|1, X|2, 2|X------ Reverse order - X|1, 1|X, 2|1, 1|2, X|2, 2|X

If the row 6 has following patterns Post#1

1|X

X|1

1|2

1|1

2|2

1|X

1|1

In this above line 1|X is appeared in the starting first and followed by X|1 so count will go in the group Original order 1|X = 2 (in the cell L6) and X|1=1 (in the cell M6)

If the row 7 has following patterns Post#1

X|1

1|X

1|1

1|X

X|1

In this above line X|1 is appeared in the starting first and followed by 1|X so count will go in the group Reverse order X|1 = 4 (in the cell S6) and 1|X=2 (in the cell T6)

The same method will be applied for the rest rows 
Hope this helps

Thank you

Regards,
Kishan

Kishan · Sep 17, 2018

Rick Rothstein said:
I am having trouble deciphering your count requirement. Can you explain why the counts for Rows 12, 26, 28 are in the columns they are in?

Hi Rick Rothstein, I think I have wrong placed in the row 12, but the row 26 & 28 seem to be ok

Post#1 as shown

Book1

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

1

2

3

4

5

P1

P2

P3

P4

P5

P6

P7

1|X

X|1

1|2

2|1

X|2

2|X

X|1

1|X

2|1

1|2

2|X

X|2

12

X|X

X|1

1|1

1|X

2|1

2|X

1

2

1

26

X|2

1|1

2|1

1|1

X|X

1|X

1

28

2|1

X|X

1|1

X|X

1|X

1|1

1

31

32

Sheet7

It should be as below

Book1

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

1

2

3

4

5

P1

P2

P3

P4

P5

P6

P7

1|X

X|1

1|2

2|1

X|2

2|X

X|1

1|X

2|1

1|2

2|X

X|2

12

X|X

X|1

1|1

1|X

2|1

2|X

1

2

1

26

X|2

1|1

2|1

1|1

X|X

1|X

1

28

2|1

X|X

1|1

X|X

1|X

1|1

1

31

32

Sheet7-2

Hope this helps

Thank you

Regards,
Kishan

Rick Rothstein · Sep 17, 2018

Kishan said:
Hi Rick Rothstein, I think I have wrong placed in the row 12, but the row 26 & 28 seem to be ok

I think I may be starting to understand what you want. Your answer to the following question will tell me if I am right or not. For your example, is the green count in the wrong group for Row 13?

Kishan · Sep 17, 2018

Rick Rothstein said:
I think I may be starting to understand what you want. Your answer to the following question will tell me if I am right or not. For your example, is the green count in the wrong group for Row 13?

Hi Rick Rothstein, yes I am sorry, Row 13 green count are placed in the wrong group, count should be in other group in the cell U13, but they are placed wrongly in the wrong group in the cell O13

Thank you for observing it minutely 

Regards,
Kishan

Marcelo Branco · Sep 17, 2018

Maybe...

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

1

2

3

4

5

P1

P2

P3

P4

P5

P6

P7

1|X

X|1

1|2

2|1

X|2

2|X

X|1

1|X

2|1

1|2

2|X

X|2

6

1|X

X|1

1|2

1|1

2|2

1|X

1|1

2

1

7

X|1

1|X

1|1

1|X

X|1

4

2

8

1|X

1|2

2|1

X|1

1|X

1|2

2|1

2

1

2

9

2|X

1|1

1|2

X|X

X|1

1|1

2|1

1

10

1|X

2|2

2|1

X|X

2|2

X|X

1|1

1

11

1|X

1|1

X|X

2|2

X|X

1|1

X|X

1

12

X|X

X|1

1|1

1|X

2|1

2|X

1

2

1

13

1|X

X|1

2|1

1|1

2|X

1

2

14

X|2

X|1

1|X

1|1

X|1

1|X

1

2

15

1|1

X|X

X|1

1|1

2|X

1

16

1|2

X|1

1|2

X|2

1|1

1|X

3

1

17

1|X

X|1

1|2

X|X

1|X

1|1

X|1

2

1

18

1|1

1|X

1|1

X|1

1|1

1

19

X|1

2|X

1|1

1|2

X|1

1|2

X|X

2

1

20

1|1

1|X

X|1

1|1

2

21

1|1

1|X

X|2

1|1

1|2

1

22

1|1

2|X

X|1

1

23

1|2

2|1

1|1

1|X

1|1

1

24

1|1

X|1

1|1

2|1

1

25

X|X

X|1

1|1

X|1

1|2

X|1

1|1

1

3

26

X|2

1|1

2|1

1|1

X|X

1|X

1

27

1|X

2|2

1|1

1|2

X|X

1|1

1|X

2

1

28

2|1

X|X

1|1

X|X

1|X

1|1

1

29

X|1

2|1

2|X

X|1

1|2

X|1

1|2

3

1

2

1

30

X|1

X|X

1|1

1|X

1|1

1

31

Formula in L6 copied across until Q6 and down
=IF(COUNTIF($C6:$I6,L$5),IF(MOD(COLUMN(L6),2)=0,IF(MATCH(L$5,$C6:$I6,0)>IFERROR(MATCH(RIGHT(L$5)&"|"&LEFT(L$5),$C6:$I6,0),100),"",COUNTIF($C6:$I6,L$5)),IF(AND(COUNTIF($C6:$I6,L$5),K6<>""),COUNTIF($C6:$I6,L$5),"")),"")

Formula in S6 copied across until X6 and down
=IF(AND(COUNTIF($C6:$I6,S$5),INDEX($L6:$Q6,MATCH(S$5,$L$5:$Q$5,0))=""),COUNTIF($C6:$I6,S$5),"")

Hope this helps

M.

Separate counts depending on start patterns

Kishan

Well-known Member

Excel Facts

Fluff

MrExcel MVP, Moderator

Kishan

Well-known Member

Fluff

MrExcel MVP, Moderator

Rick Rothstein

MrExcel MVP

Kishan

Well-known Member

Kishan

Well-known Member

Rick Rothstein

MrExcel MVP

Kishan

Well-known Member

Marcelo Branco

MrExcel MVP

Similar threads

Forum statistics

Share this page

Separate counts depending on start patterns

Well-known Member

Excel Facts

MrExcel MVP, Moderator

Well-known Member

MrExcel MVP, Moderator

MrExcel MVP

Well-known Member

Well-known Member

MrExcel MVP

Well-known Member

MrExcel MVP

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