How to repeating sort in excel

[ingkafi]

Hi everyone, i'm struggling to sort in Excel. I have a labelled data from 1-5. What i want is the data sorted with repeated number. To help you visualize see the picture below. Is there any way to resolve this? Thank you

 

[JamesCanale]

MrExcelPlayground4.xlsx
	F	G	H	I	J
1	1	Data 1		1	Data 1
2	1	Data 2		2	Data 3
3	2	Data 3		3	Data 5
4	2	Data 4		4	Data 7
5	3	Data 5		5	Data 9
6	3	Data 6		1	Data 2
7	4	Data 7		2	Data 4
8	4	Data 8		3	Data 6
9	5	Data 9		4	Data 8
10	5	Data 10		5	Data 10
Sheet25

Cell Formulas
Range	Formula
I1:J10	I1	=INDEX(F1:G10,MOD(SEQUENCE(COUNT(F1:F10),1,1,2),COUNT(F1:F10))+IF(SEQUENCE(COUNT(F1:F10))>COUNT(F1:F10)/2,1,0),{1,2})
Dynamic array formulas.

 

[ingkafi]

Thank you for the answer, but it looks like i'm having trouble using the formula as seen below

How to solve this?

 

[JamesCanale]

What version of excel do you have? I thought it said 365 before - and it says 2021 now.

See if your excel recognizes the sequence function or the {}

 

[Fluff]

@JamesCanale Xl2021 has all the latest functions, so that shouldn't be a problem.

@ingkafi what do you normally use to separate the arguments in a function? Is it a comma, or semi-colon?

 

[ingkafi]

What version of excel do you have? I thought it said 365 before - and it says 2021 now.

See if your excel recognizes the sequence function or the {}

Yes, sorry before. I use Excel 2021

 

[ingkafi]

Yes, sorry before. I use Excel 2021

Wait sorry again, i use excel 2019

 

[ingkafi]

@JamesCanale Xl2021 has all the latest functions, so that shouldn't be a problem.

@ingkafi what do you normally use to separate the arguments in a function? Is it a comma, or semi-colon?

i think my office version do not support dynamic array formula

 

[Marc L]

Is there any way to resolve this?

Hi, easily doable via a VBA procedure …​

 

[JamesCanale]

Probably a better way than this:

MrExcelPlayground4.xlsx
	V	W	X	Y	Z
1	1	Data 1		1	Data 1
2	1	Data 2		2	Data 3
3	2	Data 3		3	Data 5
4	2	Data 4		4	Data 7
5	3	Data 5		5	Data 9
6	3	Data 6		1	Data 2
7	4	Data 7		2	Data 4
8	4	Data 8		3	Data 6
9	5	Data 9		4	Data 8
10	5	Data 10		5	Data 10
Sheet25

Cell Formulas
Range	Formula
Y1:Y10	Y1	=INDEX($V$1:$W$10,MOD((ROW()-ROW($V$1)+1)*2-1,ROWS($V$1:$V$10))+IF((ROW()-ROW($V$1)+1)>ROWS($V$1:$V$10)/2,1,0),1)
Z1:Z10	Z1	=INDEX($V$1:$W$10,MOD((ROW()-ROW($V$1)+1)*2-1,ROWS($V$1:$V$10))+IF((ROW()-ROW($V$1)+1)>ROWS($V$1:$V$10)/2,1,0),2)