Issue using Offset to sort data

[jamur87]

Hi Everyone,
I have a large amount of data imported into a spreadsheet. The eventual goal is to check if the number in the value column is between The Tol_HIGH and Tol_Low. I am trying to organize the data into a columns as shown below and then maybe use conditional formatting to find the values outside of the range. Every eighth column that starts with a time is a new data set and should be a new row.

Timestamp

Block

Step

Mnemonic

Value

Tol_Low

Tol_High

23:03.5​

50​

1​

red

25​

55​

60​

23:03.5​

50​

1​

yellow

65​

37​

40​

23:03.5​

50​

1​

blue

18​

5​

30​

00:00.0​

50​

1​

orange

64​

53​

73​

I am using this offset function to transpose the values into columns.
=OFFSET(A$2,0,ROW()*(ROW()-1)+ROW()-2)

After the second iteration the values do not line up. The value in column 1 of the third data set seems skip the cell after cell with the value 30 in it. Can someone please explain why this is happening? Is there a more efficient way to compare the data or is what I'm doing ok (I can do the basics in excel and learning the more powerful function right now)?

23:03.5​	50​	1​	yellow	65​	37​	40​
23:03.5​	50​	1​	blue	18​	5​	30​
1​	orange	64​	53​	73​	23:03​	50​

thanks in advance

 

[Fluff]

For 365, how about

+Fluff 1.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	Y	Z	AA	AB
1	Timestamp	Block	Step	Mnemonic	Value	Tol_Low	Tol_High
2	23:03.5	50	1	red	25	55	60	23:03.5	50	1	yellow	65	37	40	23:03.5	50	1	blue	18	5	30	00:00.0	50	1	orange	64	53	73
3
4	23:03.5	50	1	red	25	55	60
5	23:03.5	50	1	yellow	65	37	40
6	23:03.5	50	1	blue	18	5	30
7	00:00.0	50	1	orange	64	53	73
8
9
01-Sep

Cell Formulas
Range	Formula
A4:G7	A4	=INDEX(A2:AB2,1,SEQUENCE(COLUMNS(A2:AB2)/7,,0,7)+SEQUENCE(,7))
Dynamic array formulas.

 

[jamur87]

For 365, how about

+Fluff 1.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	Y	Z	AA	AB
1	Timestamp	Block	Step	Mnemonic	Value	Tol_Low	Tol_High
2	23:03.5	50	1	red	25	55	60	23:03.5	50	1	yellow	65	37	40	23:03.5	50	1	blue	18	5	30	00:00.0	50	1	orange	64	53	73
3
4	23:03.5	50	1	red	25	55	60
5	23:03.5	50	1	yellow	65	37	40
6	23:03.5	50	1	blue	18	5	30
7	00:00.0	50	1	orange	64	53	73
8
9
01-Sep

Cell Formulas
Range	Formula
A4:G7	A4	=INDEX(A2:AB2,1,SEQUENCE(COLUMNS(A2:AB2)/7,,0,7)+SEQUENCE(,7))
Dynamic array formulas.

Thank you. I can work with this as the data given to me in is in rows but some rows have more columns of data . I appreciate your help

 

[Fluff]

Is this what you want

+Fluff 1.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	Y	Z	AA	AB
1	Timestamp	Block	Step	Mnemonic	Value	Tol_Low	Tol_High
2	23:03.5	50	1	red	25	55	60	23:03.5	50	1	yellow	65	37	40	23:03.5	50	1	blue	18	5	30	00:00.0	50	1	orange	64	53	73
3	23:03.5	50	1	red	25	55	60	23:03.5	50	1	yellow	65	37	40	23:03.5	50	1	blue	18	5	30
4
5	23:03.5	50	1	red	25	55	60
6	23:03.5	50	1	yellow	65	37	40
7	23:03.5	50	1	blue	18	5	30
8	00:00.0	50	1	orange	64	53	73
9	23:03.5	50	1	red	25	55	60
10	23:03.5	50	1	yellow	65	37	40
11	23:03.5	50	1	blue	18	5	30
12
01-Sep

Cell Formulas
Range	Formula
A5:G11	A5	=LET(Rng,A2:AB3,C,7,Cols,COLUMNS(Rng)/C,Qty,SEQUENCE(ROWS(Rng)*Cols,,0),Ary,INDEX(Rng,INT(Qty/Cols)+1,MOD(Qty,Cols)*C+SEQUENCE(,C)),FILTER(Ary,INDEX(Ary,,1)<>""))
Dynamic array formulas.