How to translate data into a longitudinal database

[Ian_4-learnin]

Hello! I've been stuck on this problem for hours. I'm trying to convert data into a longitudinal data-set, and have built an equation that almost does the trick. BTW if there is an easier way to do this, I would love to learn it. In the attached document I've made 3 tables. Table 1 is the data you start with, Table 2 is the table I want, and Table 3 is the table I have. I've highlighted the parts that are broken, the rest I have figured out in my actual file.

My big problem is that I'm searching on two variables, person_id and quiz_year, and I cant figure out how to make excel search through a range. For example, if a period_id of 1 has 3 quiz_years, my formula will not look beyond the first quiz. I think I'm just rambling now...Please take a look at the attached document. I've done my best to demonstrate the problem.

Thank you for any help or advice you might have.

Update: What I really need to know is how to use match/index to search look at two columns of data, person_id and quiz_year. I need the formula to get all the way down to the correct quiz_year and THEN begin checking for the correct person_id. My order of operations is wrong and I dont know how to fix it.

[#2]

@Ian_4-learnin 

With a little help from Power Query.

[#3]

@Detlef_Lewin 

Thanks for the response. I imagine I need to install power query for this work? I don't know if you meant to send me that file. It has a new table, but doesnt show how you got that new table. I did find the Power Query feature and am playing with it now, but cant figure out how to duplicate what you did.

[#4 · Accepted solution]

@Ian_4-learnin 

Load Table1 into the PQ editor.

Select columns person_id and quiz_year. Right click -> unpivot other columns.

Select column Attribut. Right click -> replace value -> enter quiz_reponse and qr.

Select column quiz_year. Click on the ABC icon in the header and change the data type into Text.

Add user defined column. Type this formula: "year_"&[quiz_year]&"_"&[Attribut]

Select columns quiz_year and Attribut. Right click -> Remove.

Select column Benutzerdefiniert. Transform -> Pivot column. Select Value column: Wert.

Select more options: Do not aggregate.

The wording might differ because I use a non-english version and tried to translate.

Besides "right click" the commands are also available in the ribbon.

[#5]

@Detlef_LewinThank you for your help! This works just fine.

[#6]

@Ian_4-learnin 

Glad to help.