Oct 10 2020 09:30 AM - edited Oct 13 2020 02:46 AM
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 the below table (where 1 represents the person attending on that particular day) :
And I want to get to another table, like the below from this data (the order doesn't matter and I don't have to have the name necessarily) :
Does anyone have any suggestions on the best way or ways to be able to quickly achieve this?
Oct 10 2020 10:00 AM
With your permission, if I can recommend. It can help us all if you upload an Excel file (without sensitive data), no picture.
Even if it is said that a picture can say a thousand words, it is certainly not in the case of Excel, on the contrary in some cases.
You could get a precise solution much faster with a file (w/out sensitive data).
This would also be a blessing for all of us, as we can understand the problem much better, a win-win situation for everyone.
Some users (like me :) who want to help do not understand English well and the translation does not always give what you want to say, only what it can translate in an emotionally neutral way.
This is also one of the important reasons to always add a file with the description of your operating system and Excel version.
Thank you for your understanding and patience
Nikolino
I know I don't know anything (Socrates)
Oct 11 2020 12:23 AM - edited Oct 11 2020 01:13 AM
Solution@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).
Oct 13 2020 02:47 AM
@NikolinoDE Thanks for your reply - I have attached a file with the data now - basically I want to know how can I get from the data in yellow to the data in green! Thank you,
Oct 13 2020 02:50 AM
@Riny_van_Eekelen Thank you for this! I am going through this to learn a bit about powerquery to see if I can use this!
Oct 19 2020 06:24 AM
@Riny_van_Eekelen This response was perfect. Solves my issue exactly and learnt something new too! Thank you so much for your help.
Oct 11 2020 12:23 AM - edited Oct 11 2020 01:13 AM
Solution@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).