SOLVED

How can I create table from registration table

%3CLINGO-SUB%20id%3D%22lingo-sub-1767640%22%20slang%3D%22en-US%22%3EHow%20can%20I%20create%20table%20from%20registration%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1767640%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20table%20which%20contains%20registration%20data%20which%20then%20want%20to%20be%20able%20to%20convert%20it%20into%20another%20table%20of%20different%20format%20automatically%20or%20with%20minimal%20steps.%26nbsp%3B%20For%20example%2C%20I%20have%20the%20below%20table%20(where%201%20represents%20the%20person%20attending%20on%20that%20particular%20day)%20%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Samirv_0-1602346962614.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F225746i456D72D3629D0617%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Samirv_0-1602346962614.png%22%20alt%3D%22Samirv_0-1602346962614.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EAnd%20I%20want%20to%20get%20to%20another%20table%2C%20like%20the%20below%20from%20this%20data%20(the%20order%20doesn't%20matter%20and%20I%20don't%20have%20to%20have%20the%20name%20necessarily)%20%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Samirv_1-1602347031000.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F225747i033A65E8B205CE03%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Samirv_1-1602347031000.png%22%20alt%3D%22Samirv_1-1602347031000.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EDoes%20anyone%20have%20any%20suggestions%20on%20the%20best%20way%20or%20ways%20to%20be%20able%20to%20quickly%20achieve%20this%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1767640%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1767738%22%20slang%3D%22de-DE%22%3ESubject%3A%20How%20can%20I%20create%20table%20from%20registration%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1767738%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F347560%22%20target%3D%22_blank%22%3E%40Samirv%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWith%20your%20permission%2C%20if%20I%20can%20recommend.%20It%20can%20help%20us%20all%20if%20you%20upload%20an%20Excel%20file%20(without%20sensitive%20data)%2C%20no%20picture.%3C%2FP%3E%3CP%3EEven%20if%20it%20is%20said%20that%20a%20picture%20can%20say%20a%20thousand%20words%2C%20it%20is%20certainly%20not%20in%20the%20case%20of%20Excel%2C%20on%20the%20contrary%20in%20some%20cases.%3C%2FP%3E%3CP%3EYou%20could%20get%20a%20precise%20solution%20much%20faster%20with%20a%20file%20(w%2Fout%20sensitive%20data).%3C%2FP%3E%3CP%3EThis%20would%20also%20be%20a%20blessing%20for%20all%20of%20us%2C%20as%20we%20can%20understand%20the%20problem%20much%20better%2C%20a%20win-win%20situation%20for%20everyone.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3ESome%20users%20(like%20me%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%20%20who%20want%20to%20help%20do%20not%20understand%20English%20well%20and%20the%20translation%20does%20not%20always%20give%20what%20you%20want%20to%20say%2C%20only%20what%20it%20can%20translate%20in%20an%20emotionally%20neutral%20way.%3C%2FSPAN%3E%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EThis%20is%20also%20one%20of%20the%20important%20reasons%20to%20always%20add%20a%20file%20with%20the%20description%20of%20your%20operating%20system%20and%20Excel%20version.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20understanding%20and%20patience%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1768454%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20I%20create%20table%20from%20registration%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1768454%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F347560%22%20target%3D%22_blank%22%3E%40Samirv%3C%2FA%3E%26nbsp%3BPowerQuery%20would%20be%20the%20easiest.%20collect%20your%20data%20in%20a%20structured%20table%2C%20query%20it%20and%20unpivot%20the%20date%20columns%20and%20then%20create%20the%20three%20column%20table%20as%20you%20described.%20Attached%20an%20example.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAppend%20some%20new%20data%20to%20the%20table%20in%20Sheet1.%20Table%20expands%20automatically.%3C%2FP%3E%3CP%3EGo%20to%20the%20Table1%20tab%2C%20right-click%20anywhere%20inside%20the%20table%20and%20select%20refresh%2C%20or%20select%20%22Refresh%20all%22%20in%20the%20Data%20ribbon.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20re%20new%20to%20PQ%2C%20this%20is%20a%20good%20place%20start%20(though%20there%20are%20many%20sites%20providing%20similar%20tutorials).%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fexceloffthegrid.com%2Fpower-query-introduction%2F%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fexceloffthegrid.com%2Fpower-query-introduction%2F%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1774199%22%20slang%3D%22en-US%22%3EBetreff%3A%20How%20can%20I%20create%20table%20from%20registration%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1774199%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F722750%22%20target%3D%22_blank%22%3E%40Nikolino%3C%2FA%3E%26nbsp%3BThanks%20for%20your%20reply%20-%20I%20have%20attached%20a%20file%20with%20the%20data%20now%20-%20basically%20I%20want%20to%20know%20how%20can%20I%20get%20from%20the%20data%20in%20yellow%20to%20the%20data%20in%20green!%26nbsp%3B%20Thank%20you%2C%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1774203%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20I%20create%20table%20from%20registration%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1774203%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3BThank%20you%20for%20this!%26nbsp%3B%20I%20am%20going%20through%20this%20to%20learn%20a%20bit%20about%20powerquery%20to%20see%20if%20I%20can%20use%20this!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1795482%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20I%20create%20table%20from%20registration%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1795482%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3BThis%20response%20was%20perfect.%26nbsp%3B%20Solves%20my%20issue%20exactly%20and%20learnt%20something%20new%20too!%26nbsp%3B%20Thank%20you%20so%20much%20for%20your%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

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) :

 

Samirv_0-1602346962614.png

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) :

Samirv_1-1602347031000.png

Does anyone have any suggestions on the best way or ways to be able to quickly achieve this?

6 Replies
Highlighted

@Samirv 

 

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)

Highlighted
Best Response confirmed by Samirv (Occasional Contributor)
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).

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

Highlighted

@Nikolino 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,

Highlighted

@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!

Highlighted

@Riny_van_Eekelen This response was perfect.  Solves my issue exactly and learnt something new too!  Thank you so much for your help.

Highlighted