Re: Number selection

[cotech_10]

Re: Number selection

Hello All,

Sometime back ( June 1st 2019 ) I put in a request in the forum
for help on Configuring Number Groups.

https://www.mrexcel.com/forum/excel-...er-groups.html

What I would like to do is to perform much the same task as the original request
except for the following:

Firstly just to revisit the original request I submitted. I still have the original data located in 'n' number of rows with number values in various cells across 'n' number of columns.

In the example spreadsheet attached I have 6 columns and 10 rows of numbers of either 35 or 45 numbers in the various rows and columns, and there is still no FIXED number of columns and rows.

What I would further like to do is to identify all numbers that fall in a certain pattern

For example:
1. I would like to select every 8th number from those columns and rows of numbers
2. Or I may want to select only every 2nd, 3rd , 4th , 5th etc......…



[TABLE="class: cms_table"]
<tbody>[TR="bgcolor: #CACACA"]
[TD][/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]1[/TD]
[TD]Every 8th Number green highlighted[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]2[/TD]
[TD="bgcolor: #FFFF00, align: right"]1[/TD]
[TD="bgcolor: #FFFF00, align: right"]5[/TD]
[TD="bgcolor: #FFFF00, align: right"]56[/TD]
[TD="bgcolor: #FFFF00, align: right"]2[/TD]
[TD="bgcolor: #FFFF00, align: right"]67[/TD]
[TD="bgcolor: #99CC00, align: right"]51[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]3[/TD]
[TD="bgcolor: #FFFF00, align: right"]100[/TD]
[TD="bgcolor: #FFFF00, align: right"]4[/TD]
[TD="bgcolor: #FFFF00, align: right"]59[/TD]
[TD="bgcolor: #FFFF00, align: right"]22[/TD]
[TD="bgcolor: #FFFF00, align: right"]71[/TD]
[TD="bgcolor: #FFFF00, align: right"]11[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]4[/TD]
[TD="bgcolor: #FFFF00, align: right"]21[/TD]
[TD="bgcolor: #FFFF00, align: right"]21[/TD]
[TD="bgcolor: #FFFF00, align: right"]200[/TD]
[TD="bgcolor: #FFFF00, align: right"]432[/TD]
[TD="bgcolor: #99CC00, align: right"]72[/TD]
[TD="bgcolor: #FFFF00, align: right"]12[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]5[/TD]
[TD="bgcolor: #FFFF00, align: right"]323[/TD]
[TD="bgcolor: #FFFF00, align: right"]21[/TD]
[TD="bgcolor: #FFFF00, align: right"]43[/TD]
[TD="bgcolor: #FFFF00, align: right"]433[/TD]
[TD="bgcolor: #FFFF00, align: right"]73[/TD]
[TD="bgcolor: #FFFF00, align: right"]13[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]6[/TD]
[TD="bgcolor: #FFFF00, align: right"]24[/TD]
[TD="bgcolor: #FFFF00, align: right"]99[/TD]
[TD="bgcolor: #FFFF00, align: right"]43[/TD]
[TD="bgcolor: #FFFF00, align: right"]109[/TD]
[TD="bgcolor: #FFFF00, align: right"]102[/TD]
[TD="bgcolor: #FFFF00, align: right"]14[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]7[/TD]
[TD="bgcolor: #FFFF00, align: right"]9[/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD="bgcolor: #FFFF00, align: right"]58[/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD="bgcolor: #FFFF00, align: right"]103[/TD]
[TD="bgcolor: #FFFF00, align: right"]25[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]8[/TD]
[TD="bgcolor: #FFFF00, align: right"]15[/TD]
[TD="bgcolor: #FFFF00, align: right"]88[/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD="bgcolor: #FFFF00, align: right"]104[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]9[/TD]
[TD="bgcolor: #99CC00, align: right"]16[/TD]
[TD="bgcolor: #99CC00, align: right"]77[/TD]
[TD="bgcolor: #99CC00, align: right"]90[/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD="bgcolor: #FFFF00, align: right"]105[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]10[/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD="bgcolor: #FFFF00, align: right"]7[/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD="bgcolor: #FFFF00, align: right"]40[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]11[/TD]
[TD="bgcolor: #FFFF00, align: right"]117[/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD="bgcolor: #FFFF00, align: right"]50[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



The maximum or limit of selection would be say every 23rd number

I look forward in hearing back from someone soon.



Thanking You...


cotech_10

 

[Rick Rothstein]

Re: Number selection

1. The calculation for the output of every nth number is not correct for example in the table provided I
get the following results for every 8th number in the Table:

18; 10; 26; 32 & 44

where the output should of been

14; 13; 42; 41 & 39

The same issue exists in the other code that runs the row by row analysis

The problem is your original posting showed your data starting on Row 2 so I designed my code for that; however, the data you are now showing starts on Row 1, so the count is off because of that. Here is the revised code where I use a variable for the start row (highlighted in red)... just in case.

Sub ColorEveryNthCellCountingColumnByColumn()
  Dim R As Long, C As Long, Nth As Long, Cnt As Long, Indx As Long, SR As Long, LR As Long, LC As Long, Nums As Variant
  Nth = 8
  With Sheets("Sheet1")
    [B][COLOR="#FF0000"]SR = 1[/COLOR][/B]
    LR = .Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
    LC = .Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Column
    ReDim Nums(1 To Int(.Range("A" & SR, .Cells(LR, LC)).SpecialCells(xlConstants).Count / Nth), 1 To 1)
    For C = 1 To LC
      For R = SR To LR
        If Len(.Cells(R, C).Value) Then
          Cnt = Cnt + 1
          If Cnt = Nth Then
            If .Cells(R, C).Interior.Color = vbCyan Or .Cells(R, C).Interior.Color = vbYellow Then
              .Cells(R, C).Interior.Color = vbYellow
            Else
              .Cells(R, C).Interior.Color = vbGreen
            End If
            Indx = Indx + 1
            Nums(Indx, 1) = .Cells(R, C).Value
            Cnt = 0
          End If
        End If
      Next
    Next
  End With
  With Sheets("Sheet2")
    .Cells(1, Columns.Count).End(xlToLeft).Offset(, 1).Value = "Col By Col"
    .Cells(2, Columns.Count).End(xlToLeft).Offset(, 1).Resize(UBound(Nums)).Value = Nums
  End With
End Sub

Sub ColorEveryNthCellCountingRowByRow()
  Dim R As Long, C As Long, Nth As Long, Cnt As Long, Indx As Long, SR As Long, LR As Long, LC As Long, Nums As Variant
  Nth = 8
  With Sheets("Sheet1")
    [B][COLOR="#FF0000"]SR = 1[/COLOR][/B]
    LR = .Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
    LC = .Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Column
    ReDim Nums(1 To Int(.Range("A" & SR, .Cells(LR, LC)).SpecialCells(xlConstants).Count / Nth), 1 To 1)
    For R = SR To LR
      For C = 1 To LC
        If Len(.Cells(R, C).Value) Then
          Cnt = Cnt + 1
          If Cnt = Nth Then
            If .Cells(R, C).Interior.Color = vbGreen Or .Cells(R, C).Interior.Color = vbYellow Then
              .Cells(R, C).Interior.Color = vbYellow
            Else
              .Cells(R, C).Interior.Color = vbCyan
            End If
            Indx = Indx + 1
            Nums(Indx, 1) = .Cells(R, C).Value
            Cnt = 0
          End If
        End If
      Next
    Next
  End With
  With Sheets("Sheet2")
    .Cells(1, Columns.Count).End(xlToLeft).Offset(, 1).Value = "Row By Row"
    .Cells(2, Columns.Count).End(xlToLeft).Offset(, 1).Resize(UBound(Nums)).Value = Nums
  End With
End Sub

2. The second issue is in the first row in sheet 1 text is written in that row, pls
see Table attached. It has written in cell E1"Col by Col"

The code I posted cannot output that text on the same sheet (Sheet1) as the data being processed... it outputs the result on Sheet2. As I explained in my write-up, I had to do that so one list would not screw up locating the last column for the other routine.

 

[cotech_10]

Re: Number selection

Hi Rick,

My apologies for changing the design, thank you for amazing pieces of code to
do exactly what I wanted to achieve in this type of number analysis..

.....Perfect...

I am in awe of what you have achieved and provided.... and thank you
for your time and efforts..

I do have more table number analysis that I would like to undertake on those
type of tables and will post additional forum threads. I look forward to seeing
possibly your responses and contributions.

Once again a great effort and much appreciated.


Thanks and Regards




COTECH_10

 

[cotech_10]

Re: Number selection

Hi Peter,

Sorry to get to you so late... but my appreciation for your efforts... I am also impressed how you achieved the outcomes
with no VB script...

I have run the formula and it works perfectly for Vertical analysis, can I put in a request to have the same type of analysis
when undertaking horizontal analysis...row by row instead of in a columnar direction ?

Also I am not sure how the conditional formatiing works and if you can explain a little more about that it would be
appreciated..


Thanks and Regards



COTECH_10

 

[Peter_SSs]

Re: Number selection

Hi Peter,

Sorry to get to you so late...

No problem.

can I put in a request to have the same type of analysis
when undertaking horizontal analysis...row by row instead of in a columnar direction ?

In this example I have again used the range B2:K20 for the formula to analyse.

<b>Every Nth (Horizontal)</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:35px;" /><col style="width:40px;" /><col style="width:33px;" /><col style="width:40px;" /><col style="width:40px;" /><col style="width:40px;" /><col style="width:33px;" /><col style="width:29px;" /><col style="width:29px;" /><col style="width:29px;" /><col style="width:29px;" /><col style="width:26px;" /><col style="width:38px;" /></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></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:center; ">8</td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">1</td><td style="font-size:10pt; text-align:right; ">5</td><td style="font-size:10pt; text-align:right; ">56</td><td style="font-size:10pt; text-align:right; ">2</td><td style="font-size:10pt; text-align:right; ">67</td><td style="font-size:10pt; text-align:right; ">51</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">4</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">100</td><td style="background-color:#92d050; font-size:10pt; text-align:right; ">4</td><td style="font-size:10pt; text-align:right; ">59</td><td style="font-size:10pt; text-align:right; ">22</td><td style="font-size:10pt; text-align:right; ">71</td><td style="font-size:10pt; text-align:right; ">11</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">432</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">21</td><td style="font-size:10pt; text-align:right; ">21</td><td style="font-size:10pt; text-align:right; ">200</td><td style="background-color:#92d050; font-size:10pt; text-align:right; ">432</td><td style="font-size:10pt; text-align:right; ">72</td><td style="font-size:10pt; text-align:right; ">12</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">13</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">323</td><td style="font-size:10pt; text-align:right; ">21</td><td style="font-size:10pt; text-align:right; ">43</td><td style="font-size:10pt; text-align:right; ">433</td><td style="font-size:10pt; text-align:right; ">73</td><td style="background-color:#92d050; font-size:10pt; text-align:right; ">13</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">58</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">24</td><td style="font-size:10pt; text-align:right; ">99</td><td style="font-size:10pt; text-align:right; ">43</td><td style="font-size:10pt; text-align:right; ">109</td><td style="font-size:10pt; text-align:right; ">102</td><td style="font-size:10pt; text-align:right; ">14</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">90</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">9</td><td style="font-size:10pt; "> </td><td style="background-color:#92d050; font-size:10pt; text-align:right; ">58</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">103</td><td style="font-size:10pt; text-align:right; ">25</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">15</td><td style="font-size:10pt; text-align:right; ">88</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">104</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">16</td><td style="font-size:10pt; text-align:right; ">77</td><td style="background-color:#92d050; font-size:10pt; text-align:right; ">90</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">105</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">7</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">40</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">117</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">50</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </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>Spreadsheet Formulas</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 >M2</td><td >=IFERROR(INDEX<span style=' color:008000; '>($B$2:$K$20,INT<span style=' color:#0000ff; '>(AGGREGATE<span style=' color:#ff0000; '>(15,6,<span style=' color:#804000; '>(<span style=' color:#ff7837; '>(ROW<span style=' color:#8000ff; '>($B$2:$K$20)</span>-ROW<span style=' color:#8000ff; '>($B$2)</span>+1)</span>*1000000+<span style=' color:#ff7837; '>(COLUMN<span style=' color:#8000ff; '>($B$2:$K$20)</span>-COLUMN<span style=' color:#8000ff; '>($B$2)</span>+1)</span>)</span>/<span style=' color:#804000; '>($B$2:$K$20<>"")</span>,ROWS<span style=' color:#804000; '>(M$2:M2)</span>*$L$1)</span>/1000000)</span>,RIGHT<span style=' color:#0000ff; '>(AGGREGATE<span style=' color:#ff0000; '>(15,6,<span style=' color:#804000; '>(<span style=' color:#ff7837; '>(ROW<span style=' color:#8000ff; '>($B$2:$K$20)</span>-ROW<span style=' color:#8000ff; '>($B$2)</span>+1)</span>*1000000+<span style=' color:#ff7837; '>(COLUMN<span style=' color:#8000ff; '>($B$2:$K$20)</span>-COLUMN<span style=' color:#8000ff; '>($B$2)</span>+1)</span>)</span>/<span style=' color:#804000; '>($B$2:$K$20<>"")</span>,ROWS<span style=' color:#804000; '>(M$2:M2)</span>*$L$1)</span>,3)</span>+0)</span>,"")</td></tr></table></td></tr></table><br /><table style="font-family:Arial; font-size:10pt; background-color:#fffcf9; border-style: groove ;border-color:#ff0000"><tr><td ><b>Conditional formatting </b></td></tr><tr><td ><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial; font-size:10pt; padding-left:2pt; padding-right:2pt; "><tr><td >Cell</td><td >Nr.: / Condition</td><td >Format</td></tr><tr><td >B2</td><td >1. / Formula is =AND(B2<>"",MOD(COUNT($B$1:$K1)+COUNT($B2:B2),$L$1)=0)</td><td style="background-color:#92d050; ">Abc</td></tr></table></td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4

Also I am not sure how the conditional formatiing works and if you can explain a little more about that it would be
appreciated..

In this horizontal analysis format, the CF formula counts how many numbers above each cell (hence B1:K1 must not contain any purely numerical cells) and adds the number of numbers in the same row as the cell itself starting from column B up to and including the cell itself. If that number is exactly divisible by the number in L1 (MOD() = 0) and the cell actually contains a number, then colour the cell.

For example, consider the 200 in cell D4. Above that cell there are 12 numbers (B2:G3), then in the same row as the 200, there are 3 numbers counting from the left up to the cell itself. 12+3 = 15
15/8 has a remainder of 7 so do not colour that cell .
However, for E4, the count would be 16 and 16/8 leaves a remainder of 0 so colour it.

 

[cotech_10]

Re: Number selection

Hi Peter,
Thank you for creating a formula for Horizontal analysis, it is much appreciated.

I am still stuck on implementing the Conditional Format formula.
1. New Formatting Rule Dialog Box
- Which cell should I be in when creating the CF ?
- Under the New Rule option "Select a Rule Type" I have selected >> Use a formula to determine which cells to format" option
- In the field "Format values where this formual is true" I had copied in your formula as below:
" 1. / Formula is =AND(B2<>"",MOD(COUNT($B$1:$K1)+COUNT($B2:B2),$L$1)=0)"
- Selected Format option >> Fill for Colour Green
The "nth" cells that have been defined in Cell L1 are not highlighted

Thanks and Regards


COTECH_10

 

[Peter_SSs]

Re: Number selection

For the layout in my sample you need to select from B2 to K20, so that the whole range is selected but B2 is the active cell. The other steps you have described appear to be correct (so long as you clicked OK twice at the end of that process. )

You said that the "nth" cells were not highlighted. Were other cells highlighted? If so, which ones when using the sample data in my post?

 

[Peter_SSs]

Re: Number selection

BTW, here are some shorter formulas for the list of values.

<b>Every Nth (Vertical)</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:35px;" /><col style="width:40px;" /><col style="width:33px;" /><col style="width:40px;" /><col style="width:40px;" /><col style="width:40px;" /><col style="width:33px;" /><col style="width:29px;" /><col style="width:29px;" /><col style="width:29px;" /><col style="width:29px;" /><col style="width:26px;" /><col style="width:26px;" /></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></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:center; ">8</td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">1</td><td style="font-size:10pt; text-align:right; ">5</td><td style="font-size:10pt; text-align:right; ">56</td><td style="font-size:10pt; text-align:right; ">2</td><td style="font-size:10pt; text-align:right; ">67</td><td style="background-color:#92d050; font-size:10pt; text-align:right; ">51</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">16</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">100</td><td style="font-size:10pt; text-align:right; ">4</td><td style="font-size:10pt; text-align:right; ">59</td><td style="font-size:10pt; text-align:right; ">22</td><td style="font-size:10pt; text-align:right; ">71</td><td style="font-size:10pt; text-align:right; ">11</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">77</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">21</td><td style="font-size:10pt; text-align:right; ">21</td><td style="font-size:10pt; text-align:right; ">200</td><td style="font-size:10pt; text-align:right; ">432</td><td style="background-color:#92d050; font-size:10pt; text-align:right; ">72</td><td style="font-size:10pt; text-align:right; ">12</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">90</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">323</td><td style="font-size:10pt; text-align:right; ">21</td><td style="font-size:10pt; text-align:right; ">43</td><td style="font-size:10pt; text-align:right; ">433</td><td style="font-size:10pt; text-align:right; ">73</td><td style="font-size:10pt; text-align:right; ">13</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">72</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">24</td><td style="font-size:10pt; text-align:right; ">99</td><td style="font-size:10pt; text-align:right; ">43</td><td style="font-size:10pt; text-align:right; ">109</td><td style="font-size:10pt; text-align:right; ">102</td><td style="font-size:10pt; text-align:right; ">14</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">51</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">9</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">58</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">103</td><td style="font-size:10pt; text-align:right; ">25</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">15</td><td style="font-size:10pt; text-align:right; ">88</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">104</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="font-size:10pt; "> </td><td style="background-color:#92d050; font-size:10pt; text-align:right; ">16</td><td style="background-color:#92d050; font-size:10pt; text-align:right; ">77</td><td style="background-color:#92d050; font-size:10pt; text-align:right; ">90</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">105</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">7</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">40</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">117</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">50</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </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>Spreadsheet Formulas</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 >M2</td><td >=IFERROR(INDIRECT<span style=' color:008000; '>("R"&RIGHT<span style=' color:#0000ff; '>(AGGREGATE<span style=' color:#ff0000; '>(15,6,<span style=' color:#804000; '>(COLUMN<span style=' color:#ff7837; '>($B$2:$K$20)</span>*10^6+ROW<span style=' color:#ff7837; '>($B$2:$K$20)</span>)</span>/<span style=' color:#804000; '>($B$2:$K$20<>"")</span>,ROWS<span style=' color:#804000; '>(M$2:M2)</span>*$L$1)</span>,6)</span>&<br />"C"&AGGREGATE<span style=' color:#0000ff; '>(15,6,COLUMN<span style=' color:#ff0000; '>($B$2:$K$20)</span>/<span style=' color:#ff0000; '>($B$2:$K$20<>"")</span>,ROWS<span style=' color:#ff0000; '>(M$2:M2)</span>*$L$1)</span>,0)</span>,"")</td></tr></table></td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4



<b>Every Nth (Horizontal)</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:35px;" /><col style="width:40px;" /><col style="width:33px;" /><col style="width:40px;" /><col style="width:40px;" /><col style="width:40px;" /><col style="width:33px;" /><col style="width:29px;" /><col style="width:29px;" /><col style="width:29px;" /><col style="width:29px;" /><col style="width:26px;" /><col style="width:26px;" /></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></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:center; ">8</td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">1</td><td style="font-size:10pt; text-align:right; ">5</td><td style="font-size:10pt; text-align:right; ">56</td><td style="font-size:10pt; text-align:right; ">2</td><td style="font-size:10pt; text-align:right; ">67</td><td style="font-size:10pt; text-align:right; ">51</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">4</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">100</td><td style="background-color:#92d050; font-size:10pt; text-align:right; ">4</td><td style="font-size:10pt; text-align:right; ">59</td><td style="font-size:10pt; text-align:right; ">22</td><td style="font-size:10pt; text-align:right; ">71</td><td style="font-size:10pt; text-align:right; ">11</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">432</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">21</td><td style="font-size:10pt; text-align:right; ">21</td><td style="font-size:10pt; text-align:right; ">200</td><td style="background-color:#92d050; font-size:10pt; text-align:right; ">432</td><td style="font-size:10pt; text-align:right; ">72</td><td style="font-size:10pt; text-align:right; ">12</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">13</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">323</td><td style="font-size:10pt; text-align:right; ">21</td><td style="font-size:10pt; text-align:right; ">43</td><td style="font-size:10pt; text-align:right; ">433</td><td style="font-size:10pt; text-align:right; ">73</td><td style="background-color:#92d050; font-size:10pt; text-align:right; ">13</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">58</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">24</td><td style="font-size:10pt; text-align:right; ">99</td><td style="font-size:10pt; text-align:right; ">43</td><td style="font-size:10pt; text-align:right; ">109</td><td style="font-size:10pt; text-align:right; ">102</td><td style="font-size:10pt; text-align:right; ">14</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">90</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">9</td><td style="font-size:10pt; "> </td><td style="background-color:#92d050; font-size:10pt; text-align:right; ">58</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">103</td><td style="font-size:10pt; text-align:right; ">25</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">15</td><td style="font-size:10pt; text-align:right; ">88</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">104</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">16</td><td style="font-size:10pt; text-align:right; ">77</td><td style="background-color:#92d050; font-size:10pt; text-align:right; ">90</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">105</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">7</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">40</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">117</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">50</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </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>Spreadsheet Formulas</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 >M2</td><td >=IFERROR(INDIRECT<span style=' color:008000; '>(TEXT<span style=' color:#0000ff; '>(AGGREGATE<span style=' color:#ff0000; '>(15,6,<span style=' color:#804000; '>(ROW<span style=' color:#ff7837; '>($B$2:$K$20)</span>*10^6+COLUMN<span style=' color:#ff7837; '>($B$2:$K$20)</span>)</span>/<span style=' color:#804000; '>($B$2:$K$20<>"")</span>,ROWS<span style=' color:#804000; '>(M$2:M2)</span>*$L$1)</span>,"R000000C000000")</span>,0)</span>,"")</td></tr></table></td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4

 

[cotech_10]

Re: Number selection

Hi Peter,

Thank you for the additional formulas, that is an amazing feat..

I have got the CF to work now... that is appreciated as well.

Once again thank you for your efforts and I look forward in seeing
the responses for another variation to this piece of number analysis
in a different thread.


Thanking You


COTECH_10

 

[Peter_SSs]

Re: Number selection

Hi Peter,

Thank you for the additional formulas, that is an amazing feat..

I have got the CF to work now... that is appreciated as well.

You're welcome & glad you got that CF going.

I look forward in seeing the responses for another variation to this piece of number analysis in a different thread.

.. that you haven't started yet??

 

[cotech_10]

Re: Number selection

Hi Peter,

Thanks for the reply... I have jus post the new thread today...!!!!


Cheers


COTECH_10