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]

​

No as this is not the worksheet module !​

​

Or add Activesheet. just before UsedRange …​

 

[cotech_10]

Hi Marcelo,

That works...

 

[Marc L]

Hi cotuck_0​

​

did you try mine ?​

 

[cotech_10]

Hi Marcelo,

I tried your recommendation and added "Activesheet". just before UsedRange …

That seemed to work... not fully but enough to get what I want out of this...

 

[Habtest]

[Edit: OK jokes on me not realizing your office version, this works only in O365.]

Formula option:

Test.xlsx
	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			Steps	6	3
3		2	30										Row	231	40	23	10	9	26	33	36	24	77
4		17	20	22	23								Column	1	99	77	20	36	22	13	26	42	40
5		5	600	10	246	29	34	42	43
6		9	14	16	26								Overlap	40	26	36	77
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
Sheet1

Cell Formulas
Range	Formula
N3:W3	N3	=LET( n,INT(COUNT($B$2:$J$26)/SUM($N$2:$O$2))*2+INT(MOD(COUNT($B$2:$J$26),SUM($N$2:$O$2))/$N$2), Strg,TEXTJOIN(",",1,IF($B$2:$J$26<>"",TEXT($B$2:$J$26,"0000"),"")), VALUE(MID(Strg,((INT(SEQUENCE(,n)/2)+MOD(SEQUENCE(,n),2))*$N$2+(INT((SEQUENCE(,n)-1)/2)+MOD(SEQUENCE(,n)-1,2))*$O$2-1)*5+1,4)) )
N4:W4	N4	=LET( n,INT(COUNT($B$2:$J$26)/SUM($N$2:$O$2))*2+INT(MOD(COUNT($B$2:$J$26),SUM($N$2:$O$2))/$N$2), Strg,TEXTJOIN(",",1,IF(TRANSPOSE($B$2:$J$26)<>"",TEXT(TRANSPOSE($B$2:$J$26),"0000"),"")), VALUE(MID(Strg,((INT(SEQUENCE(,n)/2)+MOD(SEQUENCE(,n),2))*$N$2+(INT((SEQUENCE(,n)-1)/2)+MOD(SEQUENCE(,n)-1,2))*$O$2-1)*5+1,4)) )
N6:W6	N6	=IFERROR(INDEX(N3#,,AGGREGATE(15,6,COLUMN(N3#)/ISNUMBER(MATCH(N3#,N4#,0)),SEQUENCE(,COUNT(N3#)))-COLUMN()+1),"")
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:J26	Expression	=ISNUMBER(MATCH(B2,$N$6#,0))	text	NO
B2:J26	Expression	=ISNUMBER(MATCH(B2,$N$4#,0))	text	NO
B2:J26	Expression	=ISNUMBER(MATCH(B2,$N$3#,0))	text	NO

 

[Marc L]

Hi Marcelo,

I tried your recommendation and added "Activesheet". just before UsedRange …

That seemed to work... not fully but enough to get what I want out of this...

First well read at least as I'm not Marcelo ‼​

​

As my code fully works according to your initial post getting same results as yours …​