Convert a vertical list of values

[MrTomB]

I have a list of RAL DESIGN colours like this:

Row

1. Code
2. Gernman
3. English
4. Code
5. German
6. English
7. Code
8. German
9. English

I need is displayed:
A B C
1. Code German English
2. Code German English

All the codes are unique ans displayed like this 000 60 00

I'm struggling to get a formulae to copy down, there are thousands of codes

 

[AlanY]

here is a workaround

you start with

Book1
	A
1	1. Code
2	2. Gernman
3	3. English
4	4. Code
5	5. German
6	6. English
7	7. Code
8	8. German
9	9. English
Sheet1

copy A2:A9 to Col B and A3:A9 to Col C

Book1
	A	B	C
1	1. Code	2. Gernman	3. English
2	2. Gernman	3. English	4. Code
3	3. English	4. Code	5. German
4	4. Code	5. German	6. English
5	5. German	6. English	7. Code
6	6. English	7. Code	8. German
7	7. Code	8. German	9. English
8	8. German	9. English
9	9. English
Sheet1

copy formula down from D1

Book1
	A	B	C	D
1	1. Code	2. Gernman	3. English	1
2	2. Gernman	3. English	4. Code	2
3	3. English	4. Code	5. German	0
4	4. Code	5. German	6. English	1
5	5. German	6. English	7. Code	2
6	6. English	7. Code	8. German	0
7	7. Code	8. German	9. English	1
8	8. German	9. English		2
9	9. English			0
10				1
11				2
12				0
Sheet1

Cell Formulas
Range	Formula
D1	=MOD(ROW(A1),3)

then put a filter in Row 1, and delete all the 0 & 2 and clear the filter

Book1
	A	B	C	D
1	1. Code	2. Gernman	3. English	1
2	4. Code	5. German	6. English	2
3	7. Code	8. German	9. English	0
4				1
Sheet1

 

[MrTomB]

There was me racking my brain for a complex formula!

Simple and effective, thanks given

 

[AlanY]

you're welcome

 

[steve the fish]

Heres a formula if you did want one:

=INDEX($A$1:$A$9,ROWS($A$1:A1)*3+COLUMNS($A$1:A1)-3)