Restructuring data into a table format

P

Patrick020

New Member
Joined
Mar 20, 2018
Messages
15
Hi everyone <o:p></o:p>
<o:p></o:p>
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:p></o:p>
<o:p></o:p>
I would like to change the structure of this data so that it is in the table format seen below in A1:H5. <o:p></o:p>
<o:p></o:p>
The data is currently structured as below in A8:C20. <o:p></o:p>
<o:p></o:p>
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:p></o:p>
<o:p></o:p>
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:p></o:p>
<o:p></o:p>
[TABLE="class: MsoTableGrid"]
<tbody>[TR]
[TD][/TD]
[TD="width: 118, bgcolor: transparent"]A<o:p></o:p>
[/TD]
[TD="width: 65, bgcolor: transparent"]B<o:p></o:p>
[/TD]
[TD="width: 65, bgcolor: transparent"]C<o:p></o:p>
[/TD]
[TD="width: 65, bgcolor: transparent"]D<o:p></o:p>
[/TD]
[TD="width: 65, bgcolor: transparent"]E<o:p></o:p>
[/TD]
[TD="width: 65, bgcolor: transparent"]F<o:p></o:p>
[/TD]
[TD="width: 65, bgcolor: transparent"]G<o:p></o:p>
[/TD]
[TD="width: 65, bgcolor: transparent"]H<o:p></o:p>
[/TD]
[/TR]
[TR]
[TD="width: 65, bgcolor: transparent"]1<o:p></o:p>
[/TD]
[TD="width: 118, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"]1<o:p></o:p>
[/TD]
[TD="width: 65, bgcolor: transparent"]2<o:p></o:p>
[/TD]
[TD="width: 65, bgcolor: transparent"]3<o:p></o:p>
[/TD]
[TD="width: 65, bgcolor: transparent"]4<o:p></o:p>
[/TD]
[TD="width: 65, bgcolor: transparent"]5<o:p></o:p>
[/TD]
[TD="width: 65, bgcolor: transparent"]6<o:p></o:p>
[/TD]
[TD="width: 65, bgcolor: transparent"]7<o:p></o:p>
[/TD]
[/TR]
[TR]
[TD="width: 65, bgcolor: transparent"]2<o:p></o:p>
[/TD]
[TD="width: 118, bgcolor: transparent"]Scheme A <o:p></o:p>
[/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 65, bgcolor: transparent"]3<o:p></o:p>
[/TD]
[TD="width: 118, bgcolor: transparent"]Scheme B<o:p></o:p>
[/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 65, bgcolor: transparent"]4<o:p></o:p>
[/TD]
[TD="width: 118, bgcolor: transparent"]Scheme C<o:p></o:p>
[/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 65, bgcolor: transparent"]5<o:p></o:p>
[/TD]
[TD="width: 118, bgcolor: transparent"]Scheme D<o:p></o:p>
[/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 65, bgcolor: transparent"]6<o:p></o:p>
[/TD]
[TD="width: 118, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 65, bgcolor: transparent"]7<o:p></o:p>
[/TD]
[TD="width: 118, bgcolor: transparent"]Input data <o:p></o:p>
[/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 65, bgcolor: transparent"]8<o:p></o:p>
[/TD]
[TD="width: 118, bgcolor: transparent"]Scheme A<o:p></o:p>
[/TD]
[TD="width: 65, bgcolor: transparent"]3<o:p></o:p>
[/TD]
[TD="width: 65, bgcolor: transparent"]Text 1<o:p></o:p>
[/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 65, bgcolor: transparent"]9<o:p></o:p>
[/TD]
[TD="width: 118, bgcolor: transparent"]Scheme A<o:p></o:p>
[/TD]
[TD="width: 65, bgcolor: transparent"]4<o:p></o:p>
[/TD]
[TD="width: 65, bgcolor: transparent"]Text 2<o:p></o:p>
[/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 65, bgcolor: transparent"]10<o:p></o:p>
[/TD]
[TD="width: 118, bgcolor: transparent"]Scheme A<o:p></o:p>
[/TD]
[TD="width: 65, bgcolor: transparent"]5<o:p></o:p>
[/TD]
[TD="width: 65, bgcolor: transparent"]Text 3<o:p></o:p>
[/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 65, bgcolor: transparent"]11<o:p></o:p>
[/TD]
[TD="width: 118, bgcolor: transparent"]Scheme B<o:p></o:p>
[/TD]
[TD="width: 65, bgcolor: transparent"]2<o:p></o:p>
[/TD]
[TD="width: 65, bgcolor: transparent"]Text 4<o:p></o:p>
[/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 65, bgcolor: transparent"]12<o:p></o:p>
[/TD]
[TD="width: 118, bgcolor: transparent"]Scheme B<o:p></o:p>
[/TD]
[TD="width: 65, bgcolor: transparent"]3<o:p></o:p>
[/TD]
[TD="width: 65, bgcolor: transparent"]Text 5<o:p></o:p>
[/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 65, bgcolor: transparent"]13<o:p></o:p>
[/TD]
[TD="width: 118, bgcolor: transparent"]Scheme B<o:p></o:p>
[/TD]
[TD="width: 65, bgcolor: transparent"]4<o:p></o:p>
[/TD]
[TD="width: 65, bgcolor: transparent"]Text 6<o:p></o:p>
[/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 65, bgcolor: transparent"]14<o:p></o:p>
[/TD]
[TD="width: 118, bgcolor: transparent"]Scheme B<o:p></o:p>
[/TD]
[TD="width: 65, bgcolor: transparent"]5<o:p></o:p>
[/TD]
[TD="width: 65, bgcolor: transparent"]Text 7<o:p></o:p>
[/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 65, bgcolor: transparent"]15<o:p></o:p>
[/TD]
[TD="width: 118, bgcolor: transparent"]Scheme C<o:p></o:p>
[/TD]
[TD="width: 65, bgcolor: transparent"]6<o:p></o:p>
[/TD]
[TD="width: 65, bgcolor: transparent"]Text 8<o:p></o:p>
[/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 65, bgcolor: transparent"]16<o:p></o:p>
[/TD]
[TD="width: 118, bgcolor: transparent"]Scheme C<o:p></o:p>
[/TD]
[TD="width: 65, bgcolor: transparent"]7<o:p></o:p>
[/TD]
[TD="width: 65, bgcolor: transparent"]Text 9<o:p></o:p>
[/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 65, bgcolor: transparent"]17<o:p></o:p>
[/TD]
[TD="width: 118, bgcolor: transparent"]Scheme D <o:p></o:p>
[/TD]
[TD="width: 65, bgcolor: transparent"]1<o:p></o:p>
[/TD]
[TD="width: 65, bgcolor: transparent"]Text 10<o:p></o:p>
[/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 65, bgcolor: transparent"]18<o:p></o:p>
[/TD]
[TD="width: 118, bgcolor: transparent"]Scheme D <o:p></o:p>
[/TD]
[TD="width: 65, bgcolor: transparent"]2<o:p></o:p>
[/TD]
[TD="width: 65, bgcolor: transparent"]Text 11<o:p></o:p>
[/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 65, bgcolor: transparent"]19<o:p></o:p>
[/TD]
[TD="width: 118, bgcolor: transparent"]Scheme D <o:p></o:p>
[/TD]
[TD="width: 65, bgcolor: transparent"]3<o:p></o:p>
[/TD]
[TD="width: 65, bgcolor: transparent"]Text 12<o:p></o:p>
[/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 65, bgcolor: transparent"]20<o:p></o:p>
[/TD]
[TD="width: 118, bgcolor: transparent"]Scheme D <o:p></o:p>
[/TD]
[TD="width: 65, bgcolor: transparent"]4<o:p></o:p>
[/TD]
[TD="width: 65, bgcolor: transparent"]Text 13<o:p></o:p>
[/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 65, bgcolor: transparent"]21<o:p></o:p>
[/TD]
[TD="width: 118, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 65, bgcolor: transparent"]22<o:p></o:p>
[/TD]
[TD="width: 118, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 65, bgcolor: transparent"]23<o:p></o:p>
[/TD]
[TD="width: 118, bgcolor: transparent"]Correct Output<o:p></o:p>
[/TD]
[TD="width: 65, bgcolor: transparent"]1<o:p></o:p>
[/TD]
[TD="width: 65, bgcolor: transparent"]2<o:p></o:p>
[/TD]
[TD="width: 65, bgcolor: transparent"]3<o:p></o:p>
[/TD]
[TD="width: 65, bgcolor: transparent"]4<o:p></o:p>
[/TD]
[TD="width: 65, bgcolor: transparent"]5<o:p></o:p>
[/TD]
[TD="width: 65, bgcolor: transparent"]6<o:p></o:p>
[/TD]
[TD="width: 65, bgcolor: transparent"]7<o:p></o:p>
[/TD]
[/TR]
[TR]
[TD="width: 65, bgcolor: transparent"]24<o:p></o:p>
[/TD]
[TD="width: 118, bgcolor: transparent"]Scheme A <o:p></o:p>
[/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"]Text 1<o:p></o:p>
[/TD]
[TD="width: 65, bgcolor: transparent"]Text 2<o:p></o:p>
[/TD]
[TD="width: 65, bgcolor: transparent"]Text 3<o:p></o:p>
[/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 65, bgcolor: transparent"]25<o:p></o:p>
[/TD]
[TD="width: 118, bgcolor: transparent"]Scheme B<o:p></o:p>
[/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"]Text 4<o:p></o:p>
[/TD]
[TD="width: 65, bgcolor: transparent"]Text 5<o:p></o:p>
[/TD]
[TD="width: 65, bgcolor: transparent"]Text 6<o:p></o:p>
[/TD]
[TD="width: 65, bgcolor: transparent"]Text 7<o:p></o:p>
[/TD]
[/TR]
[TR]
[TD="width: 65, bgcolor: transparent"]26<o:p></o:p>
[/TD]
[TD="width: 118, bgcolor: transparent"]Scheme C<o:p></o:p>
[/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"]Text 8<o:p></o:p>
[/TD]
[TD="width: 65, bgcolor: transparent"]Text 9<o:p></o:p>
[/TD]
[/TR]
[TR]
[TD="width: 65, bgcolor: transparent"]27<o:p></o:p>
[/TD]
[TD="width: 118, bgcolor: transparent"]Scheme D<o:p></o:p>
[/TD]
[TD="width: 65, bgcolor: transparent"]Text 10<o:p></o:p>
[/TD]
[TD="width: 65, bgcolor: transparent"]Text 11<o:p></o:p>
[/TD]
[TD="width: 65, bgcolor: transparent"]Text 12<o:p></o:p>
[/TD]
[TD="width: 65, bgcolor: transparent"]Text 13<o:p></o:p>
[/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
<o:p></o:p>
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Sort by date Sort by votes
I think this ought to do it.

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


Book1
ABCDEFGH
1Correct Output1234567
2Scheme A Text 1Text 2Text 3
3Scheme BText 4Text 5Text 6Text 7
4Scheme CText 8Text 9
5Scheme DText 10Text 11Text 12Text 13
Sheet64
Cell Formulas
RangeFormula
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.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
rjplante
  • Question Question
VBA to extract users name from outlook
Replies
2
Views
1K
Jaffirahamed
J
E
  • Question Question
Adding a custom message.
Replies
1
Views
645
Micron
M
SanjayGMusafir
  • Solved
Text function not returning correct month
Replies
7
Views
161
Alex Blakenburg
A
T
  • Solved
Formula Causes Memory Leak...Sometimes?!? Help me diagnose
Replies
5
Views
289
Tarkemelion
T

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,637
Latest member
Ezio2866

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top