# Alternate Number Positions

#### cotech_10

##### Board Regular
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
ABCDEFGHIJKLMNOPQRSTUVWX
1Every Nth Factor - AlternateOUTPUT
210047913152311103340Row23140231092633362477
3230Column1997720362213264240
417202223
556001024629344243Overlap77362640
69141626
7137
8323845
93399
10993641
11213139
1211
13
14
1524
16
176
18
19
20
21
22
23
2427
25
2677
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
ABCDEFGHIJKLMNOPQRST
1Every Nth Factor - AlternateOUTPUT
210047913152311103340Row22043163631
3230Column216997926231
417202223
556001024629344243Overlap
69141626
7137
8323845
93399
10993641
11213139
1211
13
14
1524
16
176
18
19
20
21
22
23
2427
25
2677
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

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

#### Marc L

##### Well-known Member
Hi,​
colors are just for your elaboration or do you really need the cells to be coloured ? (faster without)

#### cotech_10

##### Board Regular
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

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

M.

#### cotech_10

##### Board Regular

ADVERTISEMENT

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

##### MrExcel MVP
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

##### Board Regular

ADVERTISEMENT

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

##### Well-known Member
According to your attachment whatever the scenario a VBA demonstration to paste to the worksheet module :​
VBA Code:
``````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

##### MrExcel MVP
Maybe...

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

Testes 06052021.xlsm
MNOPQRST
1SCENARIO1Step16Step23
2Row
3Column
4Overlap
5
6SCENARIO2Step110Step23
7Row
8Column
9Overlap
Plan2

Run the Sub Main that calls Sub GetValues
VBA Code:
``````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
MNOPQRSTUVW
1SCENARIO1Step16Step23
2Row23140231092633362477
3Column1997720362213264240
4Overlap
5
6SCENARIO2Step110Step23
7Row22043163631
8Column216997926231
9Overlap
Plan2

Hope this helps

M.

Last edited:

#### Marc L

##### Well-known Member
Hi Marcelo !​
'Overlap' means common values in each result … See my VBA demonstration in post #8.​

Replies
0
Views
100
Replies
10
Views
287
Replies
2
Views
46
Replies
2
Views
156
Replies
6
Views
117

Threads
1,132,887
Messages
5,655,806
Members
418,237
Latest member
Tric19999

### Share this page ### We've detected that you are using an adblocker.

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

### Which adblocker are you using?    ### Disable AdBlock

Follow these easy steps to disable AdBlock

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

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

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

### Disable uBlock

Follow these easy steps to disable uBlock

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