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

	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

<tbody>
</tbody>

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

Give this macro a try (set the skip value in the red highlighted line of code)...

Sub ColorEveryNthCellColumnByColumn()
  Dim R As Long, C As Long, Nth As Long, Cnt As Long
  [B][COLOR="#FF0000"]Nth = 8[/COLOR][/B]
  For C = 1 To Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Column
    For R = 2 To Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
      If Len(Cells(R, C).Value) Then
        Cnt = Cnt + 1
        If Cnt = Nth Then
          Cells(R, C).Interior.Color = vbGreen
          Cnt = 0
        End If
      End If
    Next
  Next
End Sub

 

[cotech_10]

Re: Number selection

Hi Rick,
Thank you for your response, it is greatly appreciated. What I was looking to achieve was to produce the results as output somewhere on the right side of that table.

So for example if we look at the table of numbers provided I would like to have
the output of that analysis copied to another cell/column.
The output would look like this:
16
77
90
72
51
Please note that this analysis would be undertaken in a Vertical direction.
I would like another option to allow the analysis to be undertaken Horizontally
or by row basis.
From the Table supplied the output would look like this :
4
432
13
58 90
Also, does this script require the table to be pre-selected ?

Look forward in hearing back from you


Thanks and Regards

COTECH_10

 

[Rick Rothstein]

Re: Number selection

...I would like to have the output of that analysis copied to another cell/column.
The output would look like this:
16
77
90
72
51

Would it be okay for the output to all be within a single cell?

Also, does this script require the table to be pre-selected ?

That depends on your answer to these questions...

1) Will there be other data on the worksheet?

2) If so, will the table be separated from the other data by a minimum of one row and one column all around?

3) Within the table will there every be an entire row or an entire column of blank cells?



Extra Question: Did you want the cells colored green in the original table or did you just do that to make it easier for us to see which cells you were referring to?

 

[Peter_SSs]

Re: Number selection

Would a formula solution be considered?

This formula copied down as far as might ever be needed, after making the range big enough (I've used B2:K20).
If your data range might be very large some of the other numbers in the formula might also need adjustment.
Change cell L1 if you want to skip a different number.

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

 

[cotech_10]

Re: Number selection

Hi Rick,

Once again thank you for the quick response..

Would it be okay for the output to all be within a single cell?

I would prefer to have them copied into individual cells
if you could, otherwise I will have to extract the numbers
through some other excel functions.

1) Will there be other data on the worksheet?

1. There is no other data on the worksheet

2) If so, will the table be separated from the other data by a minimum of one row and one column all around?

2. Since there is no data on the worksheet there
won't be an issue about the separation of the table from
other data.

3) Within the table will there every be an entire row or an entire column of blank cells?

3. There could be some tables that present themselves
with an entire row or column of blank cells. If you can
adjust for that that would be appreciated, but if it is too
time consumimg or complex to do so I can massage the table
to clear out those total blanlk rows ot columns.

Extra Question: Did you want the cells colored green in the original table or did you just do that to make it easier for us to see which cells you were referring to?

Yes if you could colour those cells in that colour suggestion (green)
that would be appreciated, also would allow Visual analysis

Looking forward in hearing back from you..



Thanks and Regards

COTECH_10

 

[cotech_10]

Re: Number selection

Hi Peter,

Thank you also for the response provided, I am not sure how this formula works but if you could provide some details
that would be greatly appreciated.


Thanks and Regards


COTECH_10

 

[Rick Rothstein]

Re: Number selection

Okay, here are three macros that work hand-in-hand. The ColorEveryNthCellCountingColumnByColumn macro counts down a column before moving to the next column (this was your original request) and colors the cells green... the ColorEveryNthCellCountingRowByRow macro counts across a row before moving down to the next row and colors the cells light blue. You may run these macros one after the other if you wish. If you run both macros one after the other and the two macros end up coloring the same cell, that cell will be colored yellow to reflect this double coloring. Both macros assume your data is on Sheet1 starting at Column A and laid-out like your original post shows. The output from both functions is placed on Sheet2 (side-by-side if both are run one after the other)... this was necessary so that the output from one did not screw up the location of the last column's location. You control how many cells to skip by setting the Nth variable in each macro (meaning the skip for each macro can be different from each other if desired). The third macro named ClearColorsAndOutput does exactly what its name says... it removes the colors from the cells on Sheet1 and clears any output on Sheet2 (so you can run the macros again after changing the values assigned to the Nth variables in each macro).

Sub ColorEveryNthCellCountingColumnByColumn()
  Dim R As Long, C As Long, Nth As Long, Cnt As Long, Indx As Long, LR As Long, LC As Long, Nums As Variant
  Nth = 8
  With Sheets("Sheet1")
    LR = .Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
    LC = .Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Column
    ReDim Nums(1 To Int(.Range("A2", .Cells(LR, LC)).SpecialCells(xlConstants).Count / Nth), 1 To 1)
    For C = 1 To LC
      For R = 2 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, LR As Long, LC As Long, Nums As Variant
  Nth = 8
  With Sheets("Sheet1")
    LR = .Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
    LC = .Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Column
    ReDim Nums(1 To Int(.Range("A2", .Cells(LR, LC)).SpecialCells(xlConstants).Count / Nth), 1 To 1)
    For R = 2 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

Sub ClearColorsAndOutput()
  With Sheets("Sheet1")
    .Range("A2", .Cells(.Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row, .Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Column)).ClearFormats
  End With
  With Sheets("Sheet2")
    .UsedRange.Clear
  End With
End Sub

 

[Peter_SSs]

Re: Number selection

I am not sure how this formula works but if you could provide some details
that would be greatly appreciated.

Well, as you can see it is a rather complex formula but I will try to explain with this much smaller example where we are looking for every 3rd number in the yellow range.
First though, I have made a small change to the formula, highlighted bold red.

Excel Workbook
	B	C	D	E	F	G
1					3
2	1		56			4
3	100	4				200
4		21	200
5
Every Nth (2)

The formula builds a number for each cell in the range based on what column and what row within the range the cell is. The number is made by multiplying the column number by 1 million and adding the row number.
So cell B2 (row 1 and column 1 as far as the relevant range is concerned) gets 1*1000000 + 1 = 1000001
B3 (row 2, col 1): 1*1000000 + 2 = 1000002
B4: 1000003
C2 (row 1 col 2): 2*1000000 + 1 = 2000001
C3: 2000002
C4: 2000003
D2: 3000001
D3: 3000002
D4: 3000003

These numbers are then divided by 1 if the cell contains a number and divided by 0 (creating an error) if the cell is empty. So our series of values become:
1000001
1000002
#DIV/0!
#DIV/0!
2000002
2000003
3000001
#DIV/0!
3000003

For the formula in G2, the AGGREGATE function then takes the 3rd smallest of these numbers (because ROWS(G$2:G2)*$F$1 = 1 * 3 = 3)
2000002
and splits off the right 3 digits ("002") & adds 0 to covert to the number 2. This gives the row number within the table of the first number we want.

Next the AGGREGATE again takes the 3rd smallest number
2000002
and divides by 1000000 (=2.000002) and takes the whole number part of that (=2). This gives the column number within the table of the first number we want.

Row 2, column 2 in the table = 4 hence that is the first number returned.

Hope you can follow that and extend to see how next (cell G3) it uses the 6th highest number to get the 2nd result.



If you consider following the formula path, you can also do the highlights without vba.
For the moment I have assumed that the cells in column A to the left of the data are not numerical (if they are, then we can alter the following to accommodate that).
For the result below, I selected my range of interest (B2:K20) and applied the Conditional Formatting rule shown.
If the value in L1 is altered, the highlights will automatically change to show the new results.
Note again the changed value in the column M formula.

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

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2	1. / Formula is =AND(B2<>"",MOD(COUNT($A$2:A$20)+COUNT(B$2:B2),$L$1)=0)	Abc

 

[cotech_10]

Re: Number selection

Hi Rick,

Great effort once again and thank you for the three pieces of code..
Much of it works but there are a few issues:

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

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"

Excel 2016 (Windows) 64 bit

	A	B	C	D	E	F	G	H
1	2	15	16	30	Col By Col
2	1	13	26	32	37	43	44	18
3	17	29	33					10
4	27							26
5	7	10	20	25	45			32
6	11	19	40					44
7	5	24	28	36	39
8	14	35	41
9	18	22
10	6	8	38
11
12
13
14	31	42
15	34
16
17	3	4	12	23
18	21

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1
​

Look forward in hearing back from you soon.


Thanks and Regards


COTECH_10