Efficient way to convert table into database friendly data

[gwoolley0302]

Hello,

To help build some effective power pivot tables, I *think* I need to convert my data from Format A to Format B (demonstrated below). You can also see my spreadsheet that my data currently feeds through into Format A in sheet number 5.

If I wanted to translate sheet 5 into a format like shown in Table B below - is there a quick way to do this? (I can't see past a very laborious and time consuming method!!)

Any help is most appreciated.

Thanks, Greg

Table A

Student ID	Metric 1 (Baseline score)	Metric 1 (Post-programme score)	Metric 1 (change in score)	Metric 2 (Baseline score)	Metric 2 (Post programme score)	Metric 2 (Change in score)	Metric 3 (etc...)
xxxx001	0	2	2	1	1	0
xxxx002	1	1	0	1	2	1

Table B

Student ID	Score type	Metric 1	Metric 2
xxxx001	Baseline	0	1
xxxx001	Post programme	2	1
xxxx001	Change	2	0
xxxx002	Baseline	1	1
xxxx002	Post programme	1	2
xxxx002	Change	0	1

 

[gwoolley0302]

Would I need to change the table headers (cells A2 - AE2) in sheet 4 so they correspond to the 'baseline, post programme, Change' rows in the new sheet 5 (when created)?

 

[sandy666]

your description is not clear before and now
re-check example on sheet TEST post#6

 

[sandy666]

something went wrong, here is a link to the new example

 

[gwoolley0302]

something went wrong, here is a link to the new example

Hi, yes thats the right structure thank you!

So then how does the next step work? In essence, getting the matching data from the rows in sheet 5 into the fourth column of that new table you've put together?

 

[sandy666]

second green table was created from 5. Response score conversion and two blue tables on TEST sheet so you don't need Table from sheet 4
example from post#13

 

[gwoolley0302]

Thank you so much, this is looking great!

But just a couple of things:
1. The second table on 'TEST sheet' seems to have 6 rows for each ID, rather than 3. E.g. there are two rows for Baseline, two rows for Post programme, two rows for Change - there should just be 1 row for each of these for each ID.
2. The 'values' don't seem to match up to the figures in Sheet 5.
2. You mentioned that sheet 4 is not needed, but for your awareness I do still require that as the user of this document will change cells in Sheet 4, which then flow through to Sheet 5 (using Lookups).

 

[sandy666]

check again
btw. VLOOKUP() only shows the first value found and not all

sheet 4 is not needed

to create this table

 

[gwoolley0302]

Hi again, thank you. But I don't understand why there are 3 numbers in each value cell now? There should just be a single number. I.e. B3 in Sheet 5 should show in E3 in Test, C3 in Sheet 5 should show in E4 in Test, D3 in Sheet 5 should show in E5 in Test etc etc.

Thanks for the note on Vlookup, but this shouldn't be a problem as in sheet 4 and sheet 5, there will only ever be one row per individual Student ID.

 

[sandy666]

Book1
	A	B	C	D
2	ID	Engaged reader & enjoys book (Ba)	Engaged reader & enjoys book (End)	Engaged reader & enjoys book (Change)
3	To-RR-20-1001	3	3	0
5. Response score conversion

as you can see you have three: To-RR-20-1001 - Engaged reader & enjoys book with three values 3,3,0
one for each: Baseline, Post programme and Change
and so on for the all next

maybe try to explain your dream and logic

 

[gwoolley0302]

Book1
	A	B	C	D
2	ID	Engaged reader & enjoys book (Ba)	Engaged reader & enjoys book (End)	Engaged reader & enjoys book (Change)
3	To-RR-20-1001	3	3	0
5. Response score conversion

as you can see you have three: To-RR-20-1001 - Engaged reader & enjoys book with three values 3,3,0
one for each: Baseline, Post programme and Change
and so on for the all next

maybe try to explain your dream and logic

Haha.
The "dream" is to have create a way for each row of data in Sheet 5 to be displayed in a flat data structure. So for example, essentially the cells B3:AE3 in Sheet 5 are simply transposed into a new table, into a vertical formation (like cells E3:E47 in TEST sheet). When transposed, they stay in the same order as B3:AE3 in Sheet 5, whilst the adjacent two columns in TEST sheet label their technical sub-category and the value type. This transposing in repeated for every row of data, e.g. B4:AE4 in Sheet 5 tranposes to E48:E92 in TEST sheet etc etc.

(And the other important part as discussed, is that the flat table currently is TEST needs to automatically update as data is updated by the user in Sheet 4.)

My logic, is that every baseline score should have its own cell, a post programme score should its own cell, and the change score you have its own cell. For each student ID, there should be a total of 45 values (which all require an individual cell). The 45 values comprise of 3 scores for each of the 15 "technical categories" (3 x 15 = 45). The 3 scores per technical category are baseline, post programme and change.

So in summary I agree that there are three values for category (e.g. Engaged reader & enjoys book) but I thought the inclusion of Column D in TEST sheet (Baseline, Post programme, Change, Baseline, Post programme, Change etc etc) allows for those to be "recalled" in individual cells.

Does that help?