Alternate Number Positions

cotech_10

Board Regular
Joined
Sep 11, 2010
Messages
113
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Apr 5, 2021
Messages
927
Office Version
  1. 2010
Platform
  1. Windows
Hi,​
colors are just for your elaboration or do you really need the cells to be coloured ? (faster without)
 

cotech_10

Board Regular
Joined
Sep 11, 2010
Messages
113
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Aug 23, 2010
Messages
16,558
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
I did not understand how to get Overlap values. Please, clarify.

M.
 

cotech_10

Board Regular
Joined
Sep 11, 2010
Messages
113
Office Version
  1. 2016
Platform
  1. Windows

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
Joined
Aug 23, 2010
Messages
16,558
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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
Joined
Sep 11, 2010
Messages
113
Office Version
  1. 2016
Platform
  1. Windows

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
Joined
Apr 5, 2021
Messages
927
Office Version
  1. 2010
Platform
  1. Windows
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
Joined
Aug 23, 2010
Messages
16,558
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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
Joined
Apr 5, 2021
Messages
927
Office Version
  1. 2010
Platform
  1. Windows
Hi Marcelo !​
'Overlap' means common values in each result … See my VBA demonstration in post #8.​
 

Watch MrExcel Video

Forum statistics

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

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
Top