Complex count next to alphabets of header group in entire column data

Kishan · Nov 24, 2017

Using Excel 2000

Hi,

I have got 3-alphabets, 14-group is in the row 2, 3, and 4, columns S:AF

Note: Range S2:AF4, 3-alphabets is inserted manually and is not fixed so VBA should always consider the values are in range S2:AF4.

I want VBA, which counts (A, B, C and D occurrence) after the 3-alphabets groups, is find in the each columns through (C:P) and show counts results in range S7:AF10

And also highlights the cells in the column (C:P) as shown it is an optional if possible

For example...
Column "S" 3-alphabets in range S2:S4 = ABC, look ABC in to whole column "C" and count how many times has occurred after the "ABC" (A, B, C, and D) and the count of column "C" placed in to S7:S10

Column "T" 3-alphabets in range T2:T4 = ABD, look ABD in to whole column "D" and count how many times has occurred after the "ABD" (A, B, C, and D) and the count of column "C" placed in to T7:T10

Same procedure for the next columns up to column P

Sheet 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

AA

AB

AC

AD

AE

AF

AG

1

2

A

C

A

B

D

B

A

C

B

A

3

B

A

C

B

A

D

A

4

C

D

A

B

A

B

C

A

C

A

5

1

2

3

4

5

6

7

8

9

10

11

12

13

14

1

2

3

4

5

6

7

8

9

10

11

12

13

14

6

7

D

B

A

D

B

C

A

D

C

B

C

A

2

3

1

2

1

2

8

D

C

A

D

C

B

A

D

B

C

B

A

B

2

1

2

1

9

D

A

B

D

A

D

A

C

D

B

A

C

A

C

1

10

C

B

A

D

C

A

D

A

D

B

A

D

2

1

11

B

D

B

D

B

C

A

B

C

A

B

12

D

C

D

C

A

B

C

A

B

A

B

C

13

C

B

D

A

B

A

C

A

B

C

A

D

A

14

A

C

D

B

D

B

A

B

D

A

B

D

B

15

A

B

C

A

D

C

A

D

C

16

B

C

D

B

A

B

D

A

C

D

B

17

C

A

D

C

B

C

D

C

A

B

A

D

C

18

A

C

A

C

B

C

A

B

D

B

C

A

19

B

A

B

C

A

D

C

B

20

C

A

C

D

A

B

A

D

B

A

D

21

A

B

D

A

C

D

A

D

C

A

D

C

22

B

A

B

C

B

A

C

A

D

A

C

B

23

C

A

C

A

D

A

B

C

B

C

24

B

A

B

A

C

B

A

C

B

C

B

25

C

A

C

B

A

C

A

C

A

C

26

B

A

C

B

A

B

A

B

A

27

C

B

D

A

B

C

A

B

C

A

28

A

C

D

C

D

B

D

B

A

D

A

B

29

A

D

B

C

D

C

D

C

B

C

B

D

30

A

D

C

A

C

B

C

A

D

A

C

D

31

A

D

A

C

A

C

A

C

32

A

B

A

C

B

A

B

A

B

A

33

B

D

A

D

A

B

D

C

D

B

C

D

A

34

C

D

A

B

C

A

C

A

C

D

A

D

A

35

B

C

A

C

B

A

B

A

C

B

D

A

36

C

B

C

B

D

C

A

B

C

D

B

37

B

C

A

C

A

B

D

B

D

38

D

B

A

B

A

D

C

39

D

C

B

A

D

C

B

D

A

B

40

C

A

C

B

D

A

D

C

D

C

B

C

41

B

C

B

C

A

C

B

D

C

A

42

D

A

B

C

B

A

B

C

D

B

A

43

D

C

B

D

A

D

A

C

A

C

D

A

44

D

A

D

C

A

C

A

B

A

B

A

C

B

45

C

B

C

A

B

A

C

A

C

B

A

C

46

A

C

A

B

D

B

A

C

B

A

47

B

A

C

B

A

D

A

48

C

D

A

B

A

B

C

A

C

A

49

Sheet1

Thank you in advance

Regards,
Kishan

Eric W · Nov 24, 2017

You can do this with just a formula - for example:

	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]A[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF99CC]#FF99CC[/URL] , align: center"]A[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]C[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF99CC]#FF99CC[/URL] , align: center"]A[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]B[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF99CC]#FF99CC[/URL] , align: center"]D[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]B[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF99CC]#FF99CC[/URL] , align: center"]A[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]A[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF99CC]#FF99CC[/URL] , align: center"]A[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]A[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF99CC]#FF99CC[/URL] , align: center"]C[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]B[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF99CC]#FF99CC[/URL] , align: center"]A[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]B[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF99CC]#FF99CC[/URL] , align: center"]B[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]A[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF99CC]#FF99CC[/URL] , align: center"]A[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]C[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF99CC]#FF99CC[/URL] , align: center"]C[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]C[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF99CC]#FF99CC[/URL] , align: center"]B[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]A[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF99CC]#FF99CC[/URL] , align: center"]A[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]A[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF99CC]#FF99CC[/URL] , align: center"]A[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]D[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF99CC]#FF99CC[/URL] , align: center"]A[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]C[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF99CC]#FF99CC[/URL] , align: center"]D[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]A[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF99CC]#FF99CC[/URL] , align: center"]B[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]B[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF99CC]#FF99CC[/URL] , align: center"]A[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]B[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF99CC]#FF99CC[/URL] , align: center"]C[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]A[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF99CC]#FF99CC[/URL] , align: center"]A[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]A[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF99CC]#FF99CC[/URL] , align: center"]A[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]C[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF99CC]#FF99CC[/URL] , align: center"]A[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00FF00]#00FF00[/URL] , align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00FF00]#00FF00[/URL] , align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00FF00]#00FF00[/URL] , align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00FF00]#00FF00[/URL] , align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00FF00]#00FF00[/URL] , align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00FF00]#00FF00[/URL] , align: center"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00FF00]#00FF00[/URL] , align: center"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00FF00]#00FF00[/URL] , align: center"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00FF00]#00FF00[/URL] , align: center"]9[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00FF00]#00FF00[/URL] , align: center"]10[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00FF00]#00FF00[/URL] , align: center"]11[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00FF00]#00FF00[/URL] , align: center"]12[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00FF00]#00FF00[/URL] , align: center"]13[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00FF00]#00FF00[/URL] , align: center"]14[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=0000FF]#0000FF[/URL] , align: center"]A[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=808080]#808080[/URL] , align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=808080]#808080[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=808080]#808080[/URL] , align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=808080]#808080[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=808080]#808080[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=808080]#808080[/URL] , align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=808080]#808080[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=808080]#808080[/URL] , align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=808080]#808080[/URL] , align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=808080]#808080[/URL] , align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=808080]#808080[/URL] , align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=808080]#808080[/URL] , align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=808080]#808080[/URL] , align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=808080]#808080[/URL] , align: center"]2[/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=0000FF]#0000FF[/URL] , align: center"]B[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=808080]#808080[/URL] , align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=808080]#808080[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=808080]#808080[/URL] , align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=808080]#808080[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=808080]#808080[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=808080]#808080[/URL] , align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=808080]#808080[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=808080]#808080[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=808080]#808080[/URL] , align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=808080]#808080[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=808080]#808080[/URL] , align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=808080]#808080[/URL] , align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=808080]#808080[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=808080]#808080[/URL] , align: center"]3[/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=0000FF]#0000FF[/URL] , align: center"]C[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=808080]#808080[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=808080]#808080[/URL] , align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=808080]#808080[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=808080]#808080[/URL] , align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=808080]#808080[/URL] , align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=808080]#808080[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=808080]#808080[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=808080]#808080[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=808080]#808080[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=808080]#808080[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=808080]#808080[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=808080]#808080[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=808080]#808080[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=808080]#808080[/URL] , align: center"]0[/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=0000FF]#0000FF[/URL] , align: center"]D[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=808080]#808080[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=808080]#808080[/URL] , align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=808080]#808080[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=808080]#808080[/URL] , align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=808080]#808080[/URL] , align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=808080]#808080[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=808080]#808080[/URL] , align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=808080]#808080[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=808080]#808080[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=808080]#808080[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=808080]#808080[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=808080]#808080[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=808080]#808080[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=808080]#808080[/URL] , align: center"]0[/TD]

</tbody>

Sheet1

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]S7[/TH]
[TD="align: left"]{=SUM(IF(C$7:C$45=S$2,IF(C$8:C$46=S$3,IF(C$9:C$47=S$4,IF(C$10:C$48=$R7,1)))))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

Put the array formula in S7, then copy to the rest of the table. It doesn't do the highlighting, but you can probably make Conditional Formatting work. (CF is far different in Excel 2000 to the current version.) There are ways to hide the zeros if you prefer to see empty cells instead of 0.

If you still want a macro, that should be easy enough, but this formula does point out a difference. In your column 5, you show a count of 1 for BCBC. The discrepancy seems to be in cells G25:G30. G25:G28 = BCBC, and G27:G30 = BCBC, it overlaps. How do you want to handle that?

Kishan · Nov 24, 2017

Eric W said:
You can do this with just a formula - for example
Put the array formula in S7, then copy to the rest of the table. It doesn't do the highlighting, but you can probably make Conditional Formatting work. (CF is far different in Excel 2000 to the current version.) There are ways to hide the zeros if you prefer to see empty cells instead of 0.

Hi Eric, Thanks for your reply

Eric W said:
If you still want a macro, that should be easy enough,

Really if it is not much trouble I would prefer to have macro solution

Eric W said:
but this formula does point out a difference. In your column 5, you show a count of 1 for BCBC. The discrepancy seems to be in cells G25:G30. G25:G28 = BCBC, and G27:G30 = BCBC, it overlaps. How do you want to handle that?

I do not want handle overlaps if G25:G27 Is BCB & G28 = C so it's counted = 1, if G28:G30 would have been BCB & G31 would have been C, so it could have been correct count = 2

This you can observe in column A For "ABC" A15:A17 = ABC, A18:A20 = ABC, A21:A23 = ABC, are not over lapped

Have a nice time

Regards,
Kishan

Eric W · Nov 24, 2017

Consider:

Code:

Sub CountNColor()
Dim i As Long, r As Long, j As Long, ctrs(1 To 4, 1 To 1) As Variant, lr As Long, MyGray As Long, MyPink As Long

    Range("C:P").Interior.Color = xlNone
    Range("C5:P5").Interior.Color = vbGreen
    MyGray = RGB(100, 100, 100)
    MyPink = RGB(255, 153, 204)
    
    For i = 1 To 14
        lr = Cells(Rows.Count, i + 2).End(xlUp).Row
        Erase ctrs
        For r = 7 To lr - 2
            If Cells(r, i + 2) = Cells(2, i + 18) And _
               Cells(r + 1, i + 2) = Cells(3, i + 18) And _
               Cells(r + 2, i + 2) = Cells(4, i + 18) Then
               For j = 0 To 2
                    If Cells(r + j, i + 2).Interior.Color <> MyGray Then Cells(r + j, i + 2).Interior.Color = IIf(i Mod 2 = 1, vbYellow, MyPink)
               Next j
               For j = 1 To 4
                    If Cells(r + 3, i + 2) = Cells(6 + j, "R") Then
                        ctrs(j, 1) = ctrs(j, 1) + 1
                        Cells(r + 3, i + 2).Interior.Color = MyGray
                        r = r + 2
                        Exit For
                    End If
                Next j
            End If
        Next r
        Range(Cells(7, i + 18), Cells(10, i + 18)).Value = ctrs
    Next i
                
End Sub

The counts are different for column 12. It looks like you missed N24:N27.

Kishan · Nov 24, 2017

Eric W said:

Consider:

Code:

Sub CountNColor()
Dim i As Long, r As Long, j As Long, ctrs(1 To 4, 1 To 1) As Variant, lr As Long, MyGray As Long, MyPink As Long

    Range("C:P").Interior.Color = xlNone
    Range("C5:P5").Interior.Color = vbGreen
    MyGray = RGB(100, 100, 100)
    MyPink = RGB(255, 153, 204)
    
    For i = 1 To 14
        lr = Cells(Rows.Count, i + 2).End(xlUp).Row
        Erase ctrs
        For r = 7 To lr - 2
            If Cells(r, i + 2) = Cells(2, i + 18) And _
               Cells(r + 1, i + 2) = Cells(3, i + 18) And _
               Cells(r + 2, i + 2) = Cells(4, i + 18) Then
               For j = 0 To 2
                    If Cells(r + j, i + 2).Interior.Color <> MyGray Then Cells(r + j, i + 2).Interior.Color = IIf(i Mod 2 = 1, vbYellow, MyPink)
               Next j
               For j = 1 To 4
                    If Cells(r + 3, i + 2) = Cells(6 + j, "R") Then
                        ctrs(j, 1) = ctrs(j, 1) + 1
                        Cells(r + 3, i + 2).Interior.Color = MyGray
                        r = r + 2
                        Exit For
                    End If
                Next j
            End If
        Next r
        Range(Cells(7, i + 18), Cells(10, i + 18)).Value = ctrs
    Next i
                
End Sub

The counts are different for column 12. It looks like you missed N24:N27.

Hi Eric, I like your VBA solution!! Good idea to have sky-blue colour in the background looks cool!!

Thank you very much for your time and help

Have a nice time

Regards,
Kishan

MickG · Nov 24, 2017

Try this:-
It should be close !!!!

Code:

[COLOR=navy]Sub[/COLOR] MG24Nov04
[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] nStr [COLOR=navy]As[/COLOR] [COLOR=navy]String,[/COLOR] R [COLOR=navy]As[/COLOR] Range, nRng [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] A, B, C, D
[COLOR=navy]Set[/COLOR] Rng = Range("C7", Range("C" & Rows.Count).End(xlUp))
Rng.Resize(, 14).Interior.ColorIndex = xlNone
Rng.Resize(, 14).Font.ColorIndex = 1
[COLOR=navy]Set[/COLOR] R = [a1]
[COLOR=navy]For[/COLOR] Ac = 0 To 13
    [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
        nStr = Join(Application.Transpose(Cells(2, 19).Resize(3).Offset(, Ac)), "")
            [COLOR=navy]If[/COLOR] Join(Application.Transpose(Dn.Offset(, Ac).Resize(3)), "") = nStr And Intersect(R, Dn.Resize(3)) [COLOR=navy]Is[/COLOR] Nothing [COLOR=navy]Then[/COLOR]
                [COLOR=navy]Set[/COLOR] R = Dn.Offset(, Ac).Resize(3)
                Dn.Resize(3).Offset(, Ac).Interior.ColorIndex = IIf(Ac Mod 2 = 0, 6, 7)
                    [COLOR=navy]If[/COLOR] nRng [COLOR=navy]Is[/COLOR] Nothing [COLOR=navy]Then[/COLOR] [COLOR=navy]Set[/COLOR] nRng = Dn.Offset(3, Ac) Else [COLOR=navy]Set[/COLOR] nRng = Union(nRng, Dn.Offset(3, Ac))
                        [COLOR=navy]Select[/COLOR] [COLOR=navy]Case[/COLOR] True
                            [COLOR=navy]Case[/COLOR] Dn.Offset(3, Ac) = "A": A = A + 1
                            [COLOR=navy]Case[/COLOR] Dn.Offset(3, Ac) = "B": B = B + 1
                            [COLOR=navy]Case[/COLOR] Dn.Offset(3, Ac) = "C": C = C + 1
                            [COLOR=navy]Case[/COLOR] Dn.Offset(3, Ac) = "D": D = D + 1
                        [COLOR=navy]End[/COLOR] Select
                    [COLOR=navy]End[/COLOR] If
    [COLOR=navy]Next[/COLOR] Dn
Cells(7, 19 + Ac).Value = IIf(A = 0, "", A): A = 0
Cells(8, 19 + Ac).Value = IIf(B = 0, "", B): B = 0
Cells(9, 19 + Ac).Value = IIf(C = 0, "", C): C = 0
Cells(10, 19 + Ac).Value = IIf(D = 0, "", D): D = 0
[COLOR=navy]Next[/COLOR] Ac
[COLOR=navy]With[/COLOR] nRng
    .Interior.ColorIndex = 16
    .Font.Color = vbWhite
[COLOR=navy]End[/COLOR] [COLOR=navy]With[/COLOR]
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Sorry didn't realise you had received a VbA solution
Regards Mick

Kishan · Nov 24, 2017

MickG said:
Try this:-
It should be close !!!!
Sorry didn't realise you had received a VbA solution
Regards Mick

Hi MickG, Nevertheless having one more option

Code is finding overlapped occurrence result should be as shown below

Thanks for your reply

Book1

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

AD

AE

AF

AG

1

2

A

C

A

B

D

B

A

C

B

A

3

B

A

C

B

A

D

A

4

C

D

A

B

A

B

C

A

C

A

5

1

2

3

4

5

6

7

8

9

10

11

12

13

14

6

7

A

2

3

1

2

1

2

8

B

2

1

3

1

9

C

1

10

D

2

1

11

Sheet2

Have a nice time

Regards,
Kishan

Eric W · Nov 24, 2017

I'm glad you like it!

When you said "sky-blue colour", I was scratching my head since I didn't intentionally put in a sky-blue colour. Then I realized that this line

Code:

Range("C:P").Interior.Color = xlNone

should have been

Code:

Range("C:P").Interior.Color = vbWhite

xlNone and vbWhite are built-in constants, but xlNone really doesn't apply to Interior.Color. It just happened to map to a sky blue. And normally I would have spotted that before posting my code, but in a odd coincidence, I keep the default background color for my windows at almost the exact same shade of blue. I find a lot of white to be hard on the eyes. So I didn't notice that the color was unexpected. But if you like it, then it works!

Marcelo Branco · Nov 24, 2017

Is possible to adapt the code i provided in the thread
https://www.mrexcel.com/forum/excel...der-group-whole-column-data-fill-colours.html
to do the two tasks at once.

Try

Code:

Sub aTest()
    Dim rCrit As Range, rData As Range, rDest As Range
    Dim i As Long, j As Long, dic As Object, lGray As Long
    
    Set rCrit = Range("S2:AF4")
    Set rDest = Range("S7:AF10")
    Set rData = Range("C7:P" & Cells(Rows.Count, "C").End(xlUp).Row)
    Set dic = CreateObject("Scripting.Dictionary")
    dic.CompareMode = vbTextCompare
    lGray = RGB(192, 192, 192)
    
    For i = 1 To rData.Columns.Count
        dic("A") = Empty: dic("B") = Empty: dic("C") = Empty: dic("D") = Empty
        With rData.Columns(i)
            j = 1
            Do
                If .Cells(j) = rCrit.Columns(i).Cells(1) _
                        And .Cells(j + 1) = rCrit.Columns(i).Cells(2) _
                        And .Cells(j + 2) = rCrit.Columns(i).Cells(3) Then
                    .Cells(j).Resize(3).Interior.Color = rCrit.Columns(i).Cells(1).Interior.Color
                    
                    If .Cells(j).Offset(-1).Interior.Color = rCrit.Columns(i).Cells(1).Interior.Color Then
                            .Cells(j).Interior.Color = lGray
                    End If
                    
                    If j < rData.Rows.Count - 3 Then
                        .Cells(j + 3).Interior.Color = lGray
                        dic(.Cells(j + 3).Value) = dic(.Cells(j + 3).Value) + 1
                    End If
                    j = j + 3
                Else
                    j = j + 1
                End If
            Loop Until j > rData.Rows.Count - 2
        End With
        rDest.Columns(i) = Application.Transpose(dic.items)
    Next i
End Sub

M.

Kishan · Nov 24, 2017

Eric W said:
I'm glad you like it!
xlNone and vbWhite are built-in constants, but xlNone really doesn't apply to Interior.Color. It just happened to map to a sky blue. And normally I would have spotted that before posting my code, but in a odd coincidence, I keep the default background color for my windows at almost the exact same shade of blue. I find a lot of white to be hard on the eyes. So I didn't notice that the color was unexpected. But if you like it, then it works!

Hi Eric, Thank you for the explanation even the colour is unexpected but I will use it with my other workbooks too. Because it is a very cool!! Shade.

Have a nice time

Regards,
Kishan

Complex count next to alphabets of header group in entire column data

Kishan

Well-known Member

Excel Facts

Eric W

MrExcel MVP

Kishan

Well-known Member

Eric W

MrExcel MVP

Kishan

Well-known Member

MickG

MrExcel MVP

Kishan

Well-known Member

Eric W

MrExcel MVP

Marcelo Branco

MrExcel MVP

Kishan

Well-known Member

Similar threads

Forum statistics

Share this page

Complex count next to alphabets of header group in entire column data

Well-known Member

Excel Facts

MrExcel MVP

Well-known Member

MrExcel MVP

Well-known Member

MrExcel MVP

Well-known Member

MrExcel MVP

MrExcel MVP

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