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

Kishan

Well-known Member
Joined
Mar 15, 2011
Messages
1,648
Office Version
  1. 2010
Platform
  1. Windows
Using Excel 2000</SPAN></SPAN>
Hi,</SPAN></SPAN>

My data are in the column C:P, require results in the Column V:X</SPAN></SPAN>

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

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</SPAN></SPAN>

Example data....</SPAN></SPAN>


Book1
ABCDEFGHIJKLMNOPQRSTUVWXYZ
1
2
3
4
5P1P2P3P4P5P6P7P8P9P10P11P12P13P141X2
6X11121212X11X1100
7XX21XX1121XXXX040
8X11111111111111300
9222221X2222222007
101X1X11211XXX11200
11X2XX2X11211112001
12X112111211X1X1100
131XX21X111211X1100
141112XXX1XX1XXX030
151XX11111X112X1100
16X1X212XXX111XX020
1711X121X11121X1100
1811111X11122X11200
19XXXXXXXXXXX1XX020
20111121XX12X1X2001
211112211X1211X1100
2221X11X11121112001
2311111111111XX1100
24X11211211X111X010
251111X111111111900
26X1111211XX11X1100
2712XXXX12111X11200
28111212XXXX1X1X010
2911111111X121X2001
30111X111X11X112001
31111X11111111111000
321X11211XXX1X21100
332211X1X21221X1100
341X12111111X1X1100
3522212X1XX11XX1100
361XX2112X11X12X010
37X111111112XXXX010
38X211XX1112X111300
39X12121XXXXX112002
40111XX12X121XX1100
41XXX11X1111XX11200
422X21X1X11X1221100
43XX12X121111121100
44XX1X1111X1211X010
4511X11211111X11200
461111X2211111X1100
47
48
49
Sheet5


Thank you in advance</SPAN></SPAN>

Regards,</SPAN></SPAN>
Kishan</SPAN></SPAN>
 
Last edited:

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
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
 
Upvote 0
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.</SPAN></SPAN>

Thank you Rick, for your kind help and time to solve it.
</SPAN></SPAN>

Kind Regards,
</SPAN></SPAN>
Kishan :-D
</SPAN></SPAN>
 
Upvote 0
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><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></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
 
Upvote 0
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
 
Upvote 0
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.</SPAN></SPAN>

Have a nice day
</SPAN></SPAN>

Kind Regards,
</SPAN></SPAN>
Kishan :)
</SPAN></SPAN>
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,897
Messages
6,175,269
Members
452,628
Latest member
dd2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top