How can I create table from registration table

Hello, I have a table which contains registration data which then want to be able to convert it into another table of different format automatically or with minimal steps. For example, I have th...

registration - how can i get from yellow data to green data.xlsx39 KB

excel

Formulas and Functions

Riny_van_Eekelen
Oct 11, 2020
Samirv PowerQuery would be the easiest. collect your data in a structured table, query it and unpivot the date columns and then create the three column table as you described. Attached an example.

Append some new data to the table in Sheet1. Table expands automatically.
Go to the Table1 tab, right-click anywhere inside the table and select refresh, or select "Refresh all" in the Data ribbon.

If you re new to PQ, this is a good place to start (though there are many sites providing similar tutorials).
https://exceloffthegrid.com/power-query-introduction/
samirv.xlsx18 KB

Riny_van_Eekelen

Platinum Contributor

Oct 11, 2020

Samirv PowerQuery would be the easiest. collect your data in a structured table, query it and unpivot the date columns and then create the three column table as you described. Attached an example.

Append some new data to the table in Sheet1. Table expands automatically.

Go to the Table1 tab, right-click anywhere inside the table and select refresh, or select "Refresh all" in the Data ribbon.

If you re new to PQ, this is a good place to start (though there are many sites providing similar tutorials).

https://exceloffthegrid.com/power-query-introduction/

samirv.xlsx18 KB