convert matrix to list

[Ellasheba]

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.

 

[John Jairo V]

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!

 

[GraH - Guido]

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.

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

 

[Lori]

One more: Pivot Table (Alt+D+P -> Multiple Consolidation Ranges)

 

[Peter Bartholomew]

Combines row and column numbers to form a single sequence.

 

[GraH - Guido]

I thought I was a sucker for the name manager. Peter man, you do cultivate it.

 

[Peter Bartholomew]

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

 

[Peter Bartholomew]

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]

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