Alternate Number Positions

[cotech_10]

Hi All I would like to do the following


SCENARIO 1:

I would like to produce output based on alternate step factors for identifying and collecting data from a Numbers Table


From this Table I would like to produce an output based on an alternate step factor

For example in the Figure below I have a Numbers Table that has 25 Rows and 9 columns (please note that a Table can be any number of Rows and Columns)
and I am using what is called a step factor of 6:3 movement

alternate_excelboard.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1					Every Nth Factor - Alternate									OUTPUT
2		100	4	79	13	15	231	110	33	40			Row	231	40	23	10	9	26	33	36	24	77
3		2	30										Column	1	99	77	20	36	22	13	26	42	40
4		17	20	22	23
5		5	600	10	246	29	34	42	43				Overlap	77	36	26	40
6		9	14	16	26
7		1	37
8		32	38	45
9		33	99
10		99	36	41
11		21	31	39
12		11
13
14
15		24
16
17		6
18
19
20
21
22
23
24		27
25
26		77
27
Sheet1

The analysis works in this way:

Initially we move in a horizontal or row direction from left to right in the first cell of the Table and the first 6th factor position is identified, in this example that number is 231 which is highlighted and coloured in yellow. Then processing of the next step factor of 3 is done which will move 3 number positions and the resultant number 40 is highlighted and coloured yellow. The step process returns back to the first step factor of 6 and moves 6 positions in a horizontal direction so the next number to be highlighted will be 40 and coloured yellow. The step factor of 3 is computed and will result in the number 23 identified and highlighted in yellow…. This continues on until the table has been completed resulting in the following number group:



Row: 231, 40, 23, 10, 9, 26, 33, 36, 24, 77

Output can be written to the right of the Table as per Figure 1

The next part of this exercise is for this process to be run in vertical or column mode. Applying the same step factor of 6:3 and calculating in the same way as it has done for horizontal/row mode (except the colouration of numbers will be green) the following Output Number group would be as follows:

Column: 1, 99, 77, 20, 36, 22, 13, 26, 42, 40

Where numbers overlap in a horizontal and vertical mode they have been coloured blue. In this example the numbers 77, 36, 26, 40 have been coloured blue as they overlap.



The overlap numbers output will also be written to the left of the Table as per Figure 1

Overlap: 77, 36, 26, 40



SCNEARIO 2:

I would like to be able also to apply any step factor configuration, however in some step factor combinations the division will not be even and will result in “leftover” numbers

If I have a Numbers Table that has 45 numbers and I apply the same step factor process as outlined in scenario 1 BUT I use a step factor of 10:3 the following Table scenario would look like Figure 2 :

alternate_excelboard.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1					Every Nth Factor - Alternate									OUTPUT
2		100	4	79	13	15	231	110	33	40			Row	2	20	43	16	36	31
3		2	30										Column	21	6	99	79	26	231
4		17	20	22	23
5		5	600	10	246	29	34	42	43				Overlap
6		9	14	16	26
7		1	37
8		32	38	45
9		33	99
10		99	36	41
11		21	31	39
12		11
13
14
15		24
16
17		6
18
19
20
21
22
23
24		27
25
26		77
27
Sheet1 (2)

All horizontal row numbers calculated have been highlighted in yellow

All vertical column numbers have been highlighted in green

There are no overlapping numbers

In this example the output of this calculation would result in the following number groups

Row	2​	20​	43​	16​	36​	31​
Column	21​	6​	99​	79​	26​	231​
Overlap

I look forward in hearing from someone

Much appreciated

 

[Marc L]

Hi,​

​

colors are just for your elaboration or do you really need the cells to be coloured ? (faster without)​

 

[cotech_10]

Hi,​

​

colors are just for your elaboration or do you really need the cells to be coloured ? (faster without)​

Hi Marc,

Colors are not a show stopper, if you could do without colors that would be appreciated.

Cheers

 

[Marcelo Branco]

I did not understand how to get Overlap values. Please, clarify.

M.

 

[cotech_10]

I did not understand how to get Overlap values. Please, clarify.

M.

Hello Marco,



Thanks for your interest,



Overlaps can be explained as follows:



We are using the Same Number Table for Horizontal and Vertical analysis:

• 
  
• When we move in ether a horizontal or vertical direction and we identify the numbers that meet
• one of the Step Factor criteria it may occur that a number will be common to both horizontal and vertical
• analysis.
• 
  
• 
  
• For example: using a 6:3 step factor
• 
  
• Horizontal :
• 
  
• Just to recap
• 
  
• From the Numbers Table – Figure 1, Table Col1 = Col B , Table Col2=Col C, Table Col3=ColE…. Etc
• In a vertical analysis we start off at Cell B2 ColB and move downwards until we reach the end of the column table and continue on with the analysis at the top of Cell C2.
• 
  
• 
  
• In a horizontal analysis we start off at Cell B2 ColB but we move across to end of ColJ which is the end of the Table Row. We then move down to the next Row3 and continue on with the analysis.
• 
  
• Horizontal analysis:
• 
  
• The first part of this exercise will identify the number 231 as the 6th position and the number 40 as the 3rd position. So it moves accordingly to the step factor of 6:3
• 
  
• The number 40 in this example is an overlap number for the following reason:
• 
  
• Vertical analysis:
• 
  
• When we do a Vertical analysis the number 40 is identified as the 3rd position from the step factor of 6:3
• 
  
• The number 40 in this example common to both Horizontal and Vertical analysis and is a common/overlapping number.
• 
  
• I hope I have explained this more clearly…this is mainly required for coloration

 

[Marcelo Branco]

cotech_10

I understood perfectly how to get the values using 6:3 and looking for by rows or columns - no problems here, a macro can do this.

But i didn't understand the logic to get Overlap values.
For example, what is the logic to get 77, 36, 26, 40 in Scenario1?

By the way, my name is Marcelo, not Marco

M.

 

[cotech_10]

Hi Marcelo,

Apologies for the name confusion.

no need to worry about the identification of the overlap numbers..

But if you can assist in the other requirements that would be appreciated


Thanks and Regards

 

[Marc L]

According to your attachment whatever the scenario a VBA demonstration to paste to the worksheet module :​

Sub Demo1()
        Dim M$, V, L&, F%(1), N&(), K%, P%, X%, R&, C%, H&()
    Do
        M = InputBox(vbLf & M & vbLf & vbLf & " Enter the factors movement :", "Alternate Numbers", "6:3")
        If M = "" Then Exit Sub
        V = Split(M, ":")
        L = UBound(V) = 1:  If L Then L = IsNumeric(V(0)) And IsNumeric(V(1))
        If Not L Then M = " We have a winner !  Same player shoot again …": Beep
    Loop Until L
        [A1].Value2 = " " & M
        F(0) = V(0):  F(1) = V(1)
    With [M4].CurrentRegion.Columns
        If .Count > 1 Then [N2].Resize(.Rows.Count - 1, .Count - 1).Clear
    End With
        V = UsedRange.Columns("B:J").Value2
        L = Application.Count(V):  If L < F(0) + F(1) Then Beep: Exit Sub
        ReDim N(1 To L)
    For K = 0 To 1
        L = 0:  P = 0:  X = 0
    For R = 1 To UBound(V)
    For C = 1 To UBound(V, 2)
        If Application.IsNumber(V(R, C)) Then P = P + 1: If P = F(X) Then L = L + 1: N(L) = V(R, C): P = 0: X = 1 - X
    Next C, R
        If K = 0 Then ReDim Preserve N(1 To L): H = N: V = Application.Transpose(V)
        Cells(2 + K, 14).Resize(, L).Value2 = N
    Next
        V = Filter(Application.IfError(Application.Match(N, H, 0), False), False, False)
        If UBound(V) > -1 Then [N5].Resize(, UBound(V) + 1).Value2 = Application.Index(H, 1, V)
End Sub

​

 

[Marcelo Branco]

Maybe...

Before macro
Observe the steps in cells O1 and Q1 (SCENARIO1) and O6 and Q6 (SCENARIO2)

Testes 06052021.xlsm
	M	N	O	P	Q	R	S	T
1	SCENARIO1	Step1	6	Step2	3
2	Row
3	Column
4	Overlap
5
6	SCENARIO2	Step1	10	Step2	3
7	Row
8	Column
9	Overlap
Plan2

Run the Sub Main that calls Sub GetValues

Sub Main()
    'Calls the Sub GetValues using the parameters
    'DataRange, ByRows or not, FirstStep, SecondStep, DestinationRange (first cell)
   
    'Scenario1: Step1=6 (Cell O1), Step2=3 (Cell Q1)
    GetValues Range("B2:J26"), bByRows:=True, lStep1:=[O1], lStep2:=[Q1], rDestRng:=Range("N2") '<-- by rows
    GetValues Range("B2:J26"), bByRows:=False, lStep1:=[O1], lStep2:=[Q1], rDestRng:=Range("N3") '<-- by columns
   
    'Scenario2: Step1=10 (Cell O6) , Step2=3  (Cell Q6)
    GetValues Range("B2:J26"), bByRows:=True, lStep1:=[O6], lStep2:=[Q6], rDestRng:=Range("N7") '<-- by rows
    GetValues Range("B2:J26"), bByRows:=False, lStep1:=[O6], lStep2:=[Q6], rDestRng:=Range("N8") '<-- by columns

End Sub

Sub GetValues(rData As Range, bByRows As Boolean, lStep1 As Long, lStep2 As Long, rDestRng As Range)
    Dim dic As Object
    Dim rRng As Range, rCell As Range
    Dim lCounter1 As Long, bSt1 As Boolean
    Dim lCounter2 As Long, bSt2 As Boolean
   
    Set dic = CreateObject("Scripting.Dictionary")
    bSt1 = True
    bSt2 = False
   
    For Each rRng In IIf(bByRows, rData.Rows, rData.Columns)
        For Each rCell In rRng.Cells
            If rCell <> "" Then
                If bSt1 Then
                    lCounter1 = lCounter1 + 1
                    If lCounter1 = lStep1 Then
                        dic(rCell.Address) = rCell.Value
                        bSt1 = False
                        bSt2 = True
                        lCounter1 = 0
                    End If
                ElseIf bSt2 Then
                    lCounter2 = lCounter2 + 1
                    If lCounter2 = lStep2 Then
                        dic(rCell.Address) = rCell.Value
                        bSt1 = True
                        bSt2 = False
                        lCounter2 = 0
                    End If
                End If
            End If
        Next rCell
    Next rRng
    rDestRng.Resize(1, dic.Count) = dic.items
End Sub

After macro

Testes 06052021.xlsm
	M	N	O	P	Q	R	S	T	U	V	W
1	SCENARIO1	Step1	6	Step2	3
2	Row	231	40	23	10	9	26	33	36	24	77
3	Column	1	99	77	20	36	22	13	26	42	40
4	Overlap
5
6	SCENARIO2	Step1	10	Step2	3
7	Row	2	20	43	16	36	31
8	Column	21	6	99	79	26	231
9	Overlap
Plan2

Hope this helps

M.

 

[Marc L]

Hi Marcelo !​

​

'Overlap' means common values in each result … See my VBA demonstration in post #8.​