Combining multiple horizontal rows into a vertical list (macro free!)

[St Jimmy]

I have a helper worksheet that automatically populates horizontal lists from different criteria as seen below

	A	B	C	D	E	F	G	H
1	Test1	L0123	L0453	L0222	L0051	-	-	-
2	Test2	L0978	L0444	L0124	-	-	-	-
3	Test3	-	-	-	-	-	-	-
4	Test4	L0743	-	-	-	-	-	-

<tbody>
</tbody>

The columns will always fill left to right, and should always be "-" or should always start with "L0"
What I want is a column that combines the rows with "L0" so it would look like:

	AA	AB
1		8
2		List of Test1 - Test4
3	1	L0123
4	2	L0453
5	3	L0222
6	4	L0051
7	5	L0978
8	6	L0444
9	7	L0124
10	8	L0743

<tbody>
</tbody>

I'd use a macro, but it would require a lot of back and forth from the wksht to vba and I'd like to minimize that for speed concerns. Wrkbk is 30mb...

My code so far is:

=If($AA3>$AB1,"-",if(Left(B1,2)="L0",B1,"-"))

The code works great for single rows, but when multiple tests populate it misses the data in the rows below...
I'm not married to the code either, so if there is something fundamentally wrong or a much easier fix I'm all ears!

Thank you for your time!

 

[alansidman]

A few steps in Power Query will do this for you.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    UnpivoteColumns = Table.UnpivotOtherColumns(Source, {"Column1"}, "Attribute", "Value"),
    RemoveBlankRows = Table.SelectRows(UnpivoteColumns, each ([Value] <> "-")),
    RemovedColumns = Table.SelectColumns(RemoveBlankRows,{"Value"}),
    AddedIndexColumn = Table.AddIndexColumn(RemovedColumns, "Index", 1, 1),
    MoveIndexColumn = Table.ReorderColumns(AddedIndexColumn,{"Index", "Value"})
in
    MoveIndexColumn

To learn how to use this code: Power Query-How to use Mcode