Restructuring data into a table format

[Patrick020]

Hi everyone <o></o>
<o></o>
I have a large spreadsheet containing data around different schemes. Each scheme has a number of phases up to phase 7 and text is added around the status of each phase. <o></o>
<o></o>
I would like to change the structure of this data so that it is in the table format seen below in A1:H5. <o></o>
<o></o>
The data is currently structured as below in A8:C20. <o></o>
<o></o>
Just to make the request clear I have put the correct output in A23:H27 below. As you can see all we are doing is moving the data into a table format where the text updates are aligned to the correct phase of each scheme. <o></o>
<o></o>
If there is a formula that I can drag from B2:H5 which pulls this data that would be brilliant (the spreadsheet has over 1,000 rows so ideally not a manual job!). <o></o>
<o></o>

	A<o></o>	B<o></o>	C<o></o>	D<o></o>	E<o></o>	F<o></o>	G<o></o>	H<o></o>
1<o></o>		1<o></o>	2<o></o>	3<o></o>	4<o></o>	5<o></o>	6<o></o>	7<o></o>
2<o></o>	Scheme A <o></o>
3<o></o>	Scheme B<o></o>
4<o></o>	Scheme C<o></o>
5<o></o>	Scheme D<o></o>
6<o></o>
7<o></o>	Input data <o></o>
8<o></o>	Scheme A<o></o>	3<o></o>	Text 1<o></o>
9<o></o>	Scheme A<o></o>	4<o></o>	Text 2<o></o>
10<o></o>	Scheme A<o></o>	5<o></o>	Text 3<o></o>
11<o></o>	Scheme B<o></o>	2<o></o>	Text 4<o></o>
12<o></o>	Scheme B<o></o>	3<o></o>	Text 5<o></o>
13<o></o>	Scheme B<o></o>	4<o></o>	Text 6<o></o>
14<o></o>	Scheme B<o></o>	5<o></o>	Text 7<o></o>
15<o></o>	Scheme C<o></o>	6<o></o>	Text 8<o></o>
16<o></o>	Scheme C<o></o>	7<o></o>	Text 9<o></o>
17<o></o>	Scheme D <o></o>	1<o></o>	Text 10<o></o>
18<o></o>	Scheme D <o></o>	2<o></o>	Text 11<o></o>
19<o></o>	Scheme D <o></o>	3<o></o>	Text 12<o></o>
20<o></o>	Scheme D <o></o>	4<o></o>	Text 13<o></o>
21<o></o>
22<o></o>
23<o></o>	Correct Output<o></o>	1<o></o>	2<o></o>	3<o></o>	4<o></o>	5<o></o>	6<o></o>	7<o></o>
24<o></o>	Scheme A <o></o>			Text 1<o></o>	Text 2<o></o>	Text 3<o></o>
25<o></o>	Scheme B<o></o>				Text 4<o></o>	Text 5<o></o>	Text 6<o></o>	Text 7<o></o>
26<o></o>	Scheme C<o></o>						Text 8<o></o>	Text 9<o></o>
27<o></o>	Scheme D<o></o>	Text 10<o></o>	Text 11<o></o>	Text 12<o></o>	Text 13<o></o>

<tbody>
</tbody>

<o></o>

 

[DRSteele]

I think this ought to do it.

Copy B2 across and down. Note that your Correct Output table is incorrect for Scheme B.

Book1
	A	B	C	D	E	F	G	H
1	Correct Output	1	2	3	4	5	6	7
2	Scheme A			Text 1	Text 2	Text 3
3	Scheme B		Text 4	Text 5	Text 6	Text 7
4	Scheme C						Text 8	Text 9
5	Scheme D	Text 10	Text 11	Text 12	Text 13
Sheet64

Cell Formulas
Range	Formula
B2	{=IFNA(INDEX($C$8:$C$20,MATCH($A2&B$1,$A$8:$A$20&$B$8:$B$20,0)),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Patrick020]

I think this ought to do it.

Works perfectly - thank you very much.

 

[DRSteele]

​Super, You're welcome.