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

 

[Marcelo Branco]

Hi Marcelo !​

​

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

Thank you!
Everything is clear now ?

M.

 

[cotech_10]

H

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

​

Hi Marcelo !​

​

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

Hi Marcelo,

I am getting a VB error as attached

 

[Marcelo Branco]

Hi Marcelo,

I am getting a VB error as attached

Did you use a layout exactly like the one I showed in "Before Macro"? 6 in O1, 3 in Q1, 10 in O6, 3 in Q6?
This type of error indicates that no cell was found (???)
For me it worked perfectly - something must be different in your spreadsheet.

M.

 

[cotech_10]

Hi Marcelo,

Yes I have created the same text in the fields you have put text in.

it works perfectly..!!!!!!


Amazing... thank you so much

Your efforts are greatly appreciated.

 

[Marc L]

​

Have you ever try at least my post #8 demonstration ?‼ (with overlap …)​

 

[cotech_10]

​

Have you ever try at least my post #8 demonstration ?‼ (with overlap …)​

Will do that now

 

[cotech_10]

Hi Marcel,

I get the following error message when I run the code in Post # 8

 

[Marc L]

​

cotuck_0, your bad as you did not follow the dark red direction of post #8 !​

​

The VBA procedure must be located in the worksheet module rather than a standard one. No issue on my side …​

 

[Marcelo Branco]

Hi Marcelo,

Yes I have created the same text in the fields you have put text in.

it works perfectly..!!!!!!


Amazing... thank you so much

Your efforts are greatly appreciated.

You're welcome. Glad to help

M.

 

[cotech_10]

Hi Marcelo,

Not sure if I am still doing something not right...but I am unable to get that Overlap working

I have copied the code to the "Thisworkbook" module