Re: Number selection

cotech_10 · Oct 23, 2019

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......…

	B	C	D	E	F	G	H
1	Every 8th Number green highlighted
2	1	5	56	2	67	51
3	100	4	59	22	71	11
4	21	21	200	432	72	12
5	323	21	43	433	73	13
6	24	99	43	109	102	14
7	9		58		103	25
8	15	88			104
9	16	77	90		105
10		7			40
11	117				50

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 · Oct 24, 2019

Re: Number selection

cotech_10 said:
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.

Code:

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

Code:

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

cotech_10 said:
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 · Oct 25, 2019

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 · Oct 26, 2019

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 · Oct 27, 2019

Re: Number selection

cotech_10 said:
Hi Peter,

Sorry to get to you so late...

No problem.

cotech_10 said:
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

cotech_10 said:
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 · Oct 28, 2019

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 · Oct 28, 2019

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 · Oct 28, 2019

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 · Oct 30, 2019

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 · Oct 30, 2019

Re: Number selection

cotech_10 said:
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.

cotech_10 said:
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 · Oct 31, 2019

Re: Number selection

Hi Peter,

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

Cheers

COTECH_10

Re: Number selection

cotech_10

Board Regular

Rick Rothstein

MrExcel MVP

Excel Facts

cotech_10

Board Regular

cotech_10

Board Regular

Peter_SSs

MrExcel MVP, Moderator

cotech_10

Board Regular

Peter_SSs

MrExcel MVP, Moderator

Peter_SSs

MrExcel MVP, Moderator

cotech_10

Board Regular

Peter_SSs

MrExcel MVP, Moderator

cotech_10

Board Regular

Similar threads

Forum statistics

Share this page

Re: Number selection

Board Regular

MrExcel MVP

Excel Facts

Board Regular

Board Regular

MrExcel MVP, Moderator

Board Regular

MrExcel MVP, Moderator

MrExcel MVP, Moderator

Board Regular

MrExcel MVP, Moderator

Board Regular

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