Count from "last to first" character max occurrences as long as it find

Kishan · Jul 3, 2019

Using Excel 2000
Hi,

My data are in the column C:P, require results in the Column V:X

For example count max occurrences of the "last to first" character find in the each row
Row 6 last to first character "1" max occurrence=1, result In V6=1
Row 7 last to first character X max occurrence=4, result In W7=4
Row 8 last to first character 1 max occurrence=13, result In V8=13
Row 9 last to first character 2 max occurrence=7, result In X9=7,and so on... 

Please need to highlight the columns C:P as long as max continue fine "last to first" as shown, 1 with red fills, X with green fills, and 2 with blue fills

Example data....

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

P8

P9

P10

P11

P12

P13

P14

1

X

2

6

X

1

2

1

2

1

2

X

1

X

1

0

7

X

2

1

X

1

2

1

X

0

4

0

8

X

1

13

0

9

2

1

X

2

0

7

10

1

X

1

X

1

2

1

X

1

2

0

11

X

2

X

2

X

1

2

1

2

0

1

12

X

1

2

1

2

1

X

1

X

1

0

13

1

X

2

1

X

1

2

1

X

1

0

14

1

2

X

1

X

1

X

0

3

0

15

1

X

1

X

1

2

X

1

0

16

X

1

X

2

1

2

X

1

X

0

2

0

17

1

X

1

2

1

X

1

2

1

X

1

0

18

1

X

1

2

X

1

2

0

19

X

1

X

0

2

0

20

1

2

1

X

1

2

X

1

X

2

0

1

21

1

2

1

X

1

2

1

X

1

0

22

2

1

X

1

X

1

2

1

2

0

1

23

1

X

1

0

24

X

1

2

1

2

1

X

1

X

0

1

0

25

1

X

1

9

0

26

X

1

2

1

X

1

X

1

0

27

1

2

X

1

2

1

X

1

2

0

28

1

2

1

2

X

1

X

1

X

0

1

0

29

1

X

1

2

1

X

2

0

1

30

1

X

1

X

1

X

1

2

0

1

31

1

X

1

10

0

32

1

X

1

2

1

X

1

X

2

1

0

33

2

1

X

1

X

2

1

2

1

X

1

0

34

1

X

1

2

1

X

1

X

1

0

35

2

1

2

X

1

X

1

X

1

0

36

1

X

2

1

2

X

1

X

1

2

X

0

1

0

37

X

1

2

X

0

1

0

38

X

2

1

X

1

2

X

1

3

0

39

X

1

2

1

2

1

X

1

2

0

2

40

1

X

1

2

X

1

2

1

X

1

0

41

X

1

X

1

X

1

2

0

42

2

X

2

1

X

1

X

1

X

1

2

1

0

43

X

1

2

X

1

2

1

2

1

0

44

X

1

X

1

X

1

2

1

X

0

1

0

45

1

X

1

2

1

X

1

2

0

46

1

X

2

1

X

1

0

47

48

49

Sheet5

Thank you in advance

Regards,
Kishan

Rick Rothstein · Jul 3, 2019

Give this macro a try...

Code:

Sub ConsecutiveCountForFirstCharacter()
  Dim R As Long, C As Long, Cnt As Long, Chars As Variant
  For R = 6 To Cells(Rows.Count, "C").End(xlUp).Row
    Chars = Cells(R, "C").Resize(, 14)
    Cnt = 0
    For C = UBound(Chars, 2) To 1 Step -1
      If Cells(R, C + 2).Value = Cells(R, "P").Value Then
        Cnt = Cnt + 1
      Else
        Exit For
      End If
    Next
    Cells(R, "R").Resize(, 3) = 0
    With Cells(R, "Q").Offset(, InStr(1, "1X2", Cells(R, "P").Value, vbTextCompare))
      .Value = Cnt
      .Interior.Color = Choose(InStr(1, "1X2", Cells(R, "P").Value), vbRed, 5287936, vbBlue)
      .Font.Color = vbWhite
    End With
    With Cells(R, "Q").Offset(, -Cnt).Resize(, Cnt)
      .Interior.Color = Choose(InStr(1, "1X2", Cells(R, "P").Value), vbRed, 5287936, vbBlue)
      .Font.Color = vbWhite
    End With
  Next
End Sub

Kishan · Jul 3, 2019

Rick Rothstein said:

Give this macro a try...

Code:

Sub ConsecutiveCountForFirstCharacter()
  Dim R As Long, C As Long, Cnt As Long, Chars As Variant
  For R = 6 To Cells(Rows.Count, "C").End(xlUp).Row
    Chars = Cells(R, "C").Resize(, 14)
    Cnt = 0
    For C = UBound(Chars, 2) To 1 Step -1
      If Cells(R, C + 2).Value = Cells(R, "P").Value Then
        Cnt = Cnt + 1
      Else
        Exit For
      End If
    Next
    Cells(R, "R").Resize(, 3) = 0
    With Cells(R, "Q").Offset(, InStr(1, "1X2", Cells(R, "P").Value, vbTextCompare))
      .Value = Cnt
      .Interior.Color = Choose(InStr(1, "1X2", Cells(R, "P").Value), vbRed, 5287936, vbBlue)
      .Font.Color = vbWhite
    End With
    With Cells(R, "Q").Offset(, -Cnt).Resize(, Cnt)
      .Interior.Color = Choose(InStr(1, "1X2", Cells(R, "P").Value), vbRed, 5287936, vbBlue)
      .Font.Color = vbWhite
    End With
  Next
End Sub

Hi Rick, macro worked perfect and after the running macro I did noticed that I had mistake in the row 37 has 4 "X's", and row 39 has only 1 "2's" , I managed to change the result in the column V:X it took me more time than the you build macro.

Thank you Rick, for your kind help and time to solve it. 

Kind Regards,
Kishan

DanteAmor · Jul 3, 2019

If you want the solution with formulas and Conditional formatting:

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:18.06px;" /><col style="width:18.06px;" /><col style="width:38.02px;" /><col style="width:38.02px;" /><col style="width:38.02px;" /><col style="width:38.02px;" /><col style="width:38.02px;" /><col style="width:38.02px;" /><col style="width:38.02px;" /><col style="width:38.02px;" /><col style="width:38.02px;" /><col style="width:38.02px;" /><col style="width:38.02px;" /><col style="width:38.02px;" /><col style="width:38.02px;" /><col style="width:38.02px;" /><col style="width:18.06px;" /><col style="width:21.86px;" /><col style="width:18.06px;" /><col style="width:18.06px;" /><col style="width:18.06px;" /><col style="width:38.02px;" /><col style="width:38.02px;" /><col style="width:38.02px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td><td >K</td><td >L</td><td >M</td><td >N</td><td >O</td><td >P</td><td >Q</td><td >R</td><td >S</td><td >T</td><td >U</td><td >V</td><td >W</td><td >X</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="color:#333333; font-family:Verdana; font-size:9pt; "> </td><td style="color:#333333; font-family:Verdana; font-size:9pt; "> </td><td style="color:#333333; font-weight:bold; font-family:Verdana; font-size:9pt; text-align:center; ">P1</td><td style="color:#333333; font-weight:bold; font-family:Verdana; font-size:9pt; text-align:center; ">P2</td><td style="color:#333333; font-weight:bold; font-family:Verdana; font-size:9pt; text-align:center; ">P3</td><td style="color:#333333; font-weight:bold; font-family:Verdana; font-size:9pt; text-align:center; ">P4</td><td style="color:#333333; font-weight:bold; font-family:Verdana; font-size:9pt; text-align:center; ">P5</td><td style="background-color:#00ffff; color:#333333; font-weight:bold; font-family:Verdana; font-size:9pt; text-align:center; ">P6</td><td style="background-color:#00ffff; color:#333333; font-weight:bold; font-family:Verdana; font-size:9pt; text-align:center; ">P7</td><td style="background-color:#00ffff; color:#333333; font-weight:bold; font-family:Verdana; font-size:9pt; text-align:center; ">P8</td><td style="background-color:#00ffff; color:#333333; font-weight:bold; font-family:Verdana; font-size:9pt; text-align:center; ">P9</td><td style="background-color:#00ffff; color:#333333; font-weight:bold; font-family:Verdana; font-size:9pt; text-align:center; ">P10</td><td style="background-color:#00ffff; color:#333333; font-weight:bold; font-family:Verdana; font-size:9pt; text-align:center; ">P11</td><td style="background-color:#00ffff; color:#333333; font-weight:bold; font-family:Verdana; font-size:9pt; text-align:center; ">P12</td><td style="background-color:#00ffff; color:#333333; font-weight:bold; font-family:Verdana; font-size:9pt; text-align:center; ">P13</td><td style="background-color:#00ffff; color:#333333; font-weight:bold; font-family:Verdana; font-size:9pt; text-align:center; ">P14</td><td style="font-family:Verdana; font-size:9pt; "> </td><td style="font-family:Verdana; font-size:9pt; "> </td><td style="font-family:Verdana; font-size:9pt; "> </td><td style="font-family:Verdana; font-size:9pt; "> </td><td style="font-family:Verdana; font-size:9pt; "> </td><td style="background-color:#ffff00; font-weight:bold; font-family:Verdana; font-size:9pt; text-align:center; ">1</td><td style="background-color:#ffff00; font-weight:bold; font-family:Verdana; font-size:9pt; text-align:center; ">X</td><td style="background-color:#ffff00; font-weight:bold; font-family:Verdana; font-size:9pt; text-align:center; ">2</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="color:#333333; font-family:Verdana; font-size:9pt; "> </td><td style="color:#333333; font-family:Verdana; font-size:9pt; "> </td><td style="text-align:center; ">X</td><td style="text-align:center; ">1</td><td style="text-align:center; ">1</td><td style="text-align:center; ">1</td><td style="text-align:center; ">2</td><td style="text-align:center; ">1</td><td style="text-align:center; ">2</td><td style="text-align:center; ">1</td><td style="text-align:center; ">2</td><td style="text-align:center; ">X</td><td style="text-align:center; ">1</td><td style="text-align:center; ">1</td><td style="text-align:center; ">X</td><td style="background-color:#ff0000; text-align:center; ">1</td><td style="font-family:Verdana; font-size:9pt; "> </td><td style="font-family:Verdana; font-size:9pt; "> </td><td style="font-family:Verdana; font-size:9pt; "> </td><td style="font-family:Verdana; font-size:9pt; "> </td><td style="font-family:Verdana; font-size:9pt; "> </td><td style="background-color:#ff0000; text-align:center; ">1</td><td style="text-align:center; ">0</td><td style="text-align:center; ">0</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="color:#333333; font-family:Verdana; font-size:9pt; "> </td><td style="color:#333333; font-family:Verdana; font-size:9pt; "> </td><td style="text-align:center; ">X</td><td style="text-align:center; ">X</td><td style="text-align:center; ">2</td><td style="text-align:center; ">1</td><td style="text-align:center; ">X</td><td style="text-align:center; ">X</td><td style="text-align:center; ">1</td><td style="text-align:center; ">1</td><td style="text-align:center; ">2</td><td style="text-align:center; ">1</td><td style="background-color:#008000; text-align:center; ">X</td><td style="background-color:#008000; text-align:center; ">X</td><td style="background-color:#008000; text-align:center; ">X</td><td style="background-color:#008000; text-align:center; ">X</td><td style="font-family:Verdana; font-size:9pt; "> </td><td style="font-family:Verdana; font-size:9pt; "> </td><td style="font-family:Verdana; font-size:9pt; "> </td><td style="font-family:Verdana; font-size:9pt; "> </td><td style="font-family:Verdana; font-size:9pt; "> </td><td style="text-align:center; ">0</td><td style="background-color:#009900; text-align:center; ">4</td><td style="text-align:center; ">0</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="color:#333333; font-family:Verdana; font-size:9pt; "> </td><td style="color:#333333; font-family:Verdana; font-size:9pt; "> </td><td style="text-align:center; ">X</td><td style="background-color:#ff0000; text-align:center; ">1</td><td style="background-color:#ff0000; text-align:center; ">1</td><td style="background-color:#ff0000; text-align:center; ">1</td><td style="background-color:#ff0000; text-align:center; ">1</td><td style="background-color:#ff0000; text-align:center; ">1</td><td style="background-color:#ff0000; text-align:center; ">1</td><td style="background-color:#ff0000; text-align:center; ">1</td><td style="background-color:#ff0000; text-align:center; ">1</td><td style="background-color:#ff0000; text-align:center; ">1</td><td style="background-color:#ff0000; text-align:center; ">1</td><td style="background-color:#ff0000; text-align:center; ">1</td><td style="background-color:#ff0000; text-align:center; ">1</td><td style="background-color:#ff0000; text-align:center; ">1</td><td style="font-family:Verdana; font-size:9pt; "> </td><td style="font-family:Verdana; font-size:9pt; "> </td><td style="font-family:Verdana; font-size:9pt; "> </td><td style="font-family:Verdana; font-size:9pt; "> </td><td style="font-family:Verdana; font-size:9pt; "> </td><td style="background-color:#ff0000; text-align:center; ">13</td><td style="text-align:center; ">0</td><td style="text-align:center; ">0</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="color:#333333; font-family:Verdana; font-size:9pt; "> </td><td style="color:#333333; font-family:Verdana; font-size:9pt; "> </td><td style="text-align:center; ">2</td><td style="text-align:center; ">2</td><td style="text-align:center; ">2</td><td style="text-align:center; ">2</td><td style="text-align:center; ">2</td><td style="text-align:center; ">1</td><td style="text-align:center; ">X</td><td style="background-color:#0000ff; text-align:center; ">2</td><td style="background-color:#0000ff; text-align:center; ">2</td><td style="background-color:#0000ff; text-align:center; ">2</td><td style="background-color:#0000ff; text-align:center; ">2</td><td style="background-color:#0000ff; text-align:center; ">2</td><td style="background-color:#0000ff; text-align:center; ">2</td><td style="background-color:#0000ff; text-align:center; ">2</td><td style="font-family:Verdana; font-size:9pt; "> </td><td style="font-family:Verdana; font-size:9pt; "> </td><td style="font-family:Verdana; font-size:9pt; "> </td><td style="font-family:Verdana; font-size:9pt; "> </td><td style="font-family:Verdana; font-size:9pt; "> </td><td style="text-align:center; ">0</td><td style="text-align:center; ">0</td><td style="background-color:#0000ff; text-align:center; ">7</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="color:#333333; font-family:Verdana; font-size:9pt; "> </td><td style="color:#333333; font-family:Verdana; font-size:9pt; "> </td><td style="text-align:center; ">1</td><td style="text-align:center; ">X</td><td style="text-align:center; ">1</td><td style="text-align:center; ">X</td><td style="text-align:center; ">1</td><td style="text-align:center; ">1</td><td style="text-align:center; ">2</td><td style="text-align:center; ">1</td><td style="text-align:center; ">1</td><td style="text-align:center; ">X</td><td style="text-align:center; ">X</td><td style="text-align:center; ">X</td><td style="background-color:#ff0000; text-align:center; ">1</td><td style="background-color:#ff0000; text-align:center; ">1</td><td style="font-family:Verdana; font-size:9pt; "> </td><td style="font-family:Verdana; font-size:9pt; "> </td><td style="font-family:Verdana; font-size:9pt; "> </td><td style="font-family:Verdana; font-size:9pt; "> </td><td style="font-family:Verdana; font-size:9pt; "> </td><td style="background-color:#ff0000; text-align:center; ">2</td><td style="text-align:center; ">0</td><td style="text-align:center; ">0</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="color:#333333; font-family:Verdana; font-size:9pt; "> </td><td style="color:#333333; font-family:Verdana; font-size:9pt; "> </td><td style="text-align:center; ">X</td><td style="text-align:center; ">2</td><td style="text-align:center; ">X</td><td style="text-align:center; ">X</td><td style="text-align:center; ">2</td><td style="text-align:center; ">X</td><td style="text-align:center; ">1</td><td style="text-align:center; ">1</td><td style="text-align:center; ">2</td><td style="text-align:center; ">1</td><td style="text-align:center; ">1</td><td style="text-align:center; ">1</td><td style="text-align:center; ">1</td><td style="background-color:#0000ff; text-align:center; ">2</td><td style="font-family:Verdana; font-size:9pt; "> </td><td style="font-family:Verdana; font-size:9pt; "> </td><td style="font-family:Verdana; font-size:9pt; "> </td><td style="font-family:Verdana; font-size:9pt; "> </td><td style="font-family:Verdana; font-size:9pt; "> </td><td style="text-align:center; ">0</td><td style="text-align:center; ">0</td><td style="background-color:#0000ff; text-align:center; ">1</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="color:#333333; font-family:Verdana; font-size:9pt; "> </td><td style="color:#333333; font-family:Verdana; font-size:9pt; "> </td><td style="text-align:center; ">X</td><td style="text-align:center; ">1</td><td style="text-align:center; ">1</td><td style="text-align:center; ">2</td><td style="text-align:center; ">1</td><td style="text-align:center; ">1</td><td style="text-align:center; ">1</td><td style="text-align:center; ">2</td><td style="text-align:center; ">1</td><td style="text-align:center; ">1</td><td style="text-align:center; ">X</td><td style="text-align:center; ">1</td><td style="text-align:center; ">X</td><td style="background-color:#ff0000; text-align:center; ">1</td><td style="font-family:Verdana; font-size:9pt; "> </td><td style="font-family:Verdana; font-size:9pt; "> </td><td style="font-family:Verdana; font-size:9pt; "> </td><td style="font-family:Verdana; font-size:9pt; "> </td><td style="font-family:Verdana; font-size:9pt; "> </td><td style="background-color:#ff0000; text-align:center; ">1</td><td style="text-align:center; ">0</td><td style="text-align:center; ">0</td></tr></table> <table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >V7</td><td >{=LOOKUP(16,FREQUENCY(IF($C7:$P7=V$6,COLUMN($C7:$P7)),IF($C7:$P7<>V$6,COLUMN($C7:$P7))))}</td></tr></table></td></tr></table>

-----
Array formulas
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

-----

Conditional formatting

Column V
Formula:
=$V7>0

Format:
Fill color Red

Applies to:
=$V$7:$V$47

Column W
Formula:
=$W7>0

Format:
Fill color Green

Applies to:
=$W$7:$W$47

Column X
Formula:
=$X7>0

Format:
Fill color Blue

Applies to:
=$X$7:$X$47
------

Columns C:P

Formula:
=COLUMN(C7)>=17-$V7

Format:
Fill color Red

Applies to:
=$C$7:$P$47

Formula:
=COLUMN(C7)>=17-$W7

Format:
Fill color Green

Applies to:
=$C$7:$P$47

Formula:
=COLUMN(C7)>=17-$X7

Format:
Fill color Blue

Applies to:
=$C$7:$P$47

DanteAmor · Jul 3, 2019

Edit:

I started the data in row 7.

I put the updated rows to start in row 6

Array formula:
{=LOOKUP(16,FREQUENCY(IF($C6:$P6=V$5,COLUMN($C6:$P6)),IF($C6:$P6<>V$5,COLUMN($C6:$P6))))}

Conditional formatting

Column V
Formula:
=$V6>0
Format:
Fill color Red
Applies to:
=$V$6:$V$46

Column W
Formula:
=$W6>0
Format:
Fill color Green
Applies to:
=$W$6:$W$46

Column X
Formula:
=$X6>0
Format:
Fill color Blue
Applies to:
=$X$6:$X$46
------

Columns C:P

Formula:
=COLUMN(C6)>=17-$V6
Format:
Fill color Red
Applies to:
=$C$6:$P$46

Formula:
=COLUMN(C6)>=17-$W6
Format:
Fill color Green
Applies to:
=$C$6:$P$46

Formula:
=COLUMN(C6)>=17-$X6
Format:
Fill color Blue
Applies to:
=$C$6:$P$46

Kishan · Jul 5, 2019

DanteAmor said:
Edit:

I started the data in row 7.

I put the updated rows to start in row 6

Array formula:
{=LOOKUP(16,FREQUENCY(IF($C6:$P6=V$5,COLUMN($C6:$P6)),IF($C6:$P6<>V$5,COLUMN($C6:$P6))))}

Conditional formatting

Hi DanteAmor, thank you very much for building a formula and Conditional formatting it worked fine, but I sincerely find Rick VBA solution more useful and practical to work with any range of data.

Have a nice day 

Kind Regards,
Kishan

DanteAmor · Jul 5, 2019

Kishan said:
Hi DanteAmor, thank you very much for building a formula and Conditional formatting it worked fine, but I sincerely find Rick VBA solution more useful and practical to work with any range of data.

Have a nice day

Kind Regards,
Kishan

Agree with you. Either way you have the formula in case you have something similar later. In the same way it can serve someone else.
Thanks for the feedback.

Have a nice day too.

Count from "last to first" character max occurrences as long as it find

Kishan

Well-known Member

Excel Facts

Rick Rothstein

MrExcel MVP

Kishan

Well-known Member

DanteAmor

Well-known Member

DanteAmor

Well-known Member

Kishan

Well-known Member

DanteAmor

Well-known Member

Similar threads

Forum statistics

Share this page

Count from "last to first" character max occurrences as long as it find

Kishan

Well-known Member

Excel Facts

Rick Rothstein

MrExcel MVP

Kishan

Well-known Member

DanteAmor

Well-known Member

DanteAmor

Well-known Member

Kishan

Well-known Member

DanteAmor

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