How to covert a Matrix to a List in Columns?
I have attached my Excel Spreadsheet.The sheet Matrix is from A1:M31... 30 rows of data with headers. How can I convert this data into the result seen on the second sheet called List?
Also, the sheet Matrix is a living file, so whenever I ADD or REMOVE an "X" or "SME" or "ASME", the next sheet automatically updates. Is this possible? If not, I am ok with simply clicking a button that is assigned to a Macro so that I can rerun the list and have it up to date.
In B2 of "List"
Formula:
Please Login or Register to view this content.
Copy across and down
If you are on later version of Excel, please update your profile as there may alternative solutions: certainly with Excel 365.
Last edited by JohnTopley; 09-15-2023 at 01:52 PM.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
I get the same name on WHICHEVER cell i populate that answer too... "Ryan Kevin".... whether it's B2, B1, A5, C4, whether Row 1 has titles or not. Once i copy across and down, it gets glitchy. I do get the right amount of names, but for some reason, RYAN KEVIN is ALWAYS the first name.
See attached: and it should be A2! my error.
MatrixData =Matrix!$B$2:$M$31
MatrixHeaders =Matrix!$B$1:$M$1
MatrixNames =Matrix!$A$2:$A$31
**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
Please Login or Register to view this content.
Ben Van Johnson
Here is an alternative means to the end with Power Query. If you make any changes to the source document, then all you need to do is click on refresh all to update.
Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").
It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.
- Follow this link to learn how to install Power Query in Excel 2010 / 2013.
- Follow this link for an introduction to Power Query functionality.
- Follow this link for a video which demonstrates how to use Power Query code provided.
Please Login or Register to view this content.
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
To which cell(s) do these 4 different formulas go to?Originally Posted by protonLeah
MatrixData =Matrix!$B$2:$M$31
MatrixHeaders =Matrix!$B$1:$M$1
MatrixNames =Matrix!$A$2:$A$31
**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).Please Login or Register to view this content.
Thanks Alan.
I have a few questions.
1) If i add a new X or remove an X, how do i refresh the data so that my column lists are updated?
2) Can these columns of list of people be instead on a different tab? Or will it mess up the coding?
I'm sorry. The first three, "Matrix...", are named ranges you must create: Formula menu, define name, ...
The formula goes into the results sheet A2 and copied down and across.
to refresh your query, on the Data Tab, click on Refresh All
Are you referring to source information or data output.2) Can these columns of list of people be instead on a different tab? Or will it mess up the coding?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks