Convert Matrix To 3-column Table ('reverse Pivot', 'unpivot', 'flatten', 'normalize')


Answer :

To “reverse pivot”, “unpivot” or “flatten”:




  1. For Excel 2003: Activate any cell in your summary table and choose Data - PivotTable and PivotChart Report:



    SO20541905 first example




For later versions access the Wizard with Alt+D, P.



For Excel for Mac 2011, it's ⌘+Alt+P (See here).




  1. Select Multiple consolidation ranges and click Next.



    SO20541905 second example


  2. In “Step 2a of 3”, choose I will create the page fields and click Next.



    SO20541905 third example


  3. In “Step 2b of 3” specify your summary table range in the Range field (A1:E5 for the sample data) and click Add, then Next.



    SO20541905 fourth example


  4. In “Step 3 of 3”, select a location for the pivot table (the existing sheet should serve, as the PT is only required temporarily):



    SO20541905 fifth example


  5. Click Finish to create the pivot table:



    SO20541905 sixth example


  6. Drill down (ie double-click) on the intersect of the Grand Totals (here Cell V7 or 7):



    SO20541905 seventh example


  7. The PT may now be deleted.


  8. The resulting Table may be converted to a conventional array of cells by selecting Table in the Quick Menu (right-click in the Table) and Convert to Range.





There is a video on the same subject at Launch Excel which I consider excellent quality.



Another way to unpivot data without using VBA is with PowerQuery, a free add-in for Excel 2010 and higher, available here: http://www.microsoft.com/en-us/download/details.aspx?id=39379



Install and activate the Power Query add-in. Then follow these steps:



Add a column label to your data source and turn it into an Excel Table via Insert > Table or Ctrl - T.



enter image description here



Select any cell in the table and on the Power Query ribbon click "From Table".



enter image description here



This will open the table in the Power Query Editor window.



enter image description here



Click the column header of the first column to select it. Then, on the Transform ribbon, click the Unpivot Columns drop-down and select Unpivot other columns.



For versions of Power Query that don't have the Unpivot other columns command, select all columns except the first one (using Shift-click on the column headers) and use the Unpivot command.



enter image description here



The result is a flat table. Click Close and Load on the Home ribbon and the data will be loaded onto a new Excel sheet.



enter image description here



Now to the good part. Add some data to your source table, for example



enter image description here



Click on the sheet with the Power Query result table and on the Data ribbon click Refresh all. You will see something like:



enter image description here



Power Query is not just a one-time transformation. It is repeatable and can be linked to dynamically changing data.



All of the solutions so far involve VBA, PowerQuery, etc. which are great, but are "one-time" events. To make it more dynamic, consider using INDEX(MATCH(...)). This will allow for dynamic updates to the table.



enter image description here



Comments

Post a Comment

Popular posts from this blog

Converting A String To Int In Groovy

Answer : Use the toInteger() method to convert a String to an Integer , e.g. int value = "99".toInteger() An alternative, which avoids using a deprecated method (see below) is int value = "66" as Integer If you need to check whether the String can be converted before performing the conversion, use String number = "66" if (number.isInteger()) { int value = number as Integer } Deprecation Update In recent versions of Groovy one of the toInteger() methods has been deprecated. The following is taken from org.codehaus.groovy.runtime.StringGroovyMethods in Groovy 2.4.4 /** * Parse a CharSequence into an Integer * * @param self a CharSequence * @return an Integer * @since 1.8.2 */ public static Integer toInteger(CharSequence self) { return Integer.valueOf(self.toString().trim()); } /** * @deprecated Use the CharSequence version * @see #toInteger(CharSequence) */ @Deprecated public static Integer toInteger(String self) { return toInteg
Read more

"Cannot Create Cache Directory /home//.composer/cache/repo/https---packagist.org/, Or Directory Is Not Writable. Proceeding Without Cache"

Answer : if anyone pass through here, this is shorter solution: sudo chown -R $USER $HOME/.composer it seems to me the group information is missing in your command sudo chown -R <user> /home/<user>/.composer/cache/repo/https---packagist.org Shoud be sudo chown -R <user>:<group> /home/<user>/.composer/cache/repo/https---packagist.org But to avoid other permission issues, I would rather advise: sudo chown -R <user>:<group> /home/<user>/.composer/cache (you'll need access to other folders in there) and sudo chown <user>:<group> /home/<user>/.composer To make sure your user has permissions enough on the global composer folder. Mind the missing recursion so the user don't own keys created by root. If you need to find out the group: groups <user>
Read more

Android How Can I Convert A String To A Editable

Answer : As you probably found out, Editable is an interface so you cannot call new Editable() . However an Editable is nothing more than a String with Spannables, so use SpannableStringBuilder: Editable editable = new SpannableStringBuilder("Pass a string here"); If you only want to change the Spannables, never the text itself, you can use a basic SpannableString. Use Editable.Factory.getInstance().newEditable(str) From the android documentation: Returns a new SpannedStringBuilder from the specified CharSequence. You can override this to provide a different kind of Spanned.
Read more