• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

convert matrix to list

Ellasheba

Member
Hi

Is there an easy way to convert a matrix type table into a list in Excel?
I have uploaded an example worksheet. The yellow shaded section is the matrix and the blue shaded section is the list I would like to achieve.
I think it may be VBA if anything???
many thanks for any help.
 

Attachments

  • EXAMPLE.xlsx
    8.6 KB · Views: 14
Hi, @Ellasheba!

You could try:
[A10] : =IF(ROWS(A$10:A10)>COUNTIF(B$2:F$7,"X"),"",LOOKUP(ROWS(A$10:A10)-1,COUNTIF(OFFSET(B$1:F$1,,,ROW(A$2:A$7)-ROW(A$1)),"X"),A$2:A$7))

[B10] : =IF(A10="","",INDEX(B$1:F$1,AGGREGATE(15,6,COLUMN(B$2:F$7)-COLUMN(A$2:A$7)/(INDEX(B$2:F$7,MATCH(A10,A$2:A$7,),)="X"),COUNTIF(A$10:A10,A10))))

Drag it down both. Check file. Blessings!
 

Attachments

  • EXAMPLE (12).xlsx
    10.2 KB · Views: 12
Or an alternative via a simple "unpivot other columns" with power query.
I've added the code, but it's all done via the UI.
Code:
let
  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
  ChangeAll2Txt = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"B", type text}, {"C", type any}, {"D", type text}, {"E", type text}, {"F", type text}}),
  UnpivotOtherCols = Table.UnpivotOtherColumns(ChangeAll2Txt, {"Column1"}, "Attribute", "Value"),
  RemoveXs = Table.RemoveColumns(UnpivotOtherCols,{"Value"})
in
  RemoveXs
 

Attachments

  • PQ_Unpivot EXAMPLE.xlsx
    18.1 KB · Views: 7
@GraH - Guido
Guilty as charged! The early symptoms were showing in 2006 when I developed a client workbook that comprised 16 sheets with one formula per sheet, that being one of a sequence of 50x10 multi-cell array formulas (it started by calculating the motion of a ship in the North Atlantic and its dependence upon sea state and heading).

It is only over the last 5 years that I have treated all direct cell references as errors though. I could expand and open the idea up for critical evaluation but I suspect that the back end of a specific enquiry is not the place for such a discussion.
 
Even by the standards of Excel, the shear variety of approaches that may be taken to solve this problem is surprising. My vote would be for Power Query but it is a close run race.
 
@GraH - Guido
I could expand and open the idea up for critical evaluation but I suspect that the back end of a specific enquiry is not the place for such a discussion.
See ya in the lounge, Peter:).
Even by the standards of Excel, the shear variety of approaches that may be taken to solve this problem is surprising. My vote would be for Power Query but it is a close run race.
I'll second that. It's one on the really fun stuff to be a member of this forum. To see all that variety of dealing with Excel challenges and learn so much from those threads. Some do bring so much value (for free!) it is even daunting. I thought I knew a little of Excel. Now I'm positive it's just a little.:DD
 
Back
Top