list values above 0 in a specific order

[HMX180]

I'm trying to come up with the folowing results. I have a list of items, and corresponding values. I'm attempting to find only the values above 0, but list them as shown (the items and the values need to be listed from the bottom up). Looking for a formula to make this happen.

 

[Cubist]

Try:

=FILTER(A1:A6,B1:B6>0)

 

[Joe4]

Cubist,

That was my first thought too, but I cannot figure out how to get that so that the last populated item in the original data (row 5) lines up with the last item of the filter, like they asked for and showed in their example. The only way I can think of is to use VBA.

 

[Cubist]

There are ways to do it. I found it odd that the OP didn't leave enough space for for it to spill when all of them are true i.e. I would expect the last row to be on F6. So I wanted to start something to get their feedback.

Book1
	A	B	C	D
1	Item A	200
2	Item B	800
3	Item C	0
4	Item D	0		Item A
5	Item E	100		Item B
6	Item F	0		Item E
Sheet5

Cell Formulas
Range	Formula
D1:D6	D1	=VSTACK(TEXTSPLIT(REPT(" ",ROWS(A1:A6)-COUNTIFS(B1:B6,">0")-1),," "),FILTER(A1:A6,B1:B6>0))
Dynamic array formulas.

 

[lrobbo314]

This seems to work.

Book12
	A	B	C	D	E	F	G
1	ITEM A	200
2	ITEM B	800
3	ITEM C	0			ITEM A		200
4	ITEM D	0			ITEM B		800
5	ITEM E	100			ITEM E		100
6	ITEM F	0
Sheet1

Cell Formulas
Range	Formula
E1:E5	E1	=LET(l,MAX((B1:B6>0)*ROW(B1:B6)),f,FILTER(A1:A6,B1:B6>0),SORT(EXPAND(f,l,,"")))
G3:G5	G3	=XLOOKUP(E3:E5,A1:A6,B1:B6)
Dynamic array formulas.

 

[Cubist]

lrobbo314​

That will work only if the column A is already sorted. If you swap Item B and A, it'll return Item A then B.

 

[Joe4]

Cubist, it looks like you are off one row.
They want the last row in the "new range" to match the last row in the original range with a value greater than 0.

Here is a very brute-force VBA method that seems to do exactly what they asked for.

Sub MyCopyData()

    Dim lr As Long
    Dim r As Long
    Dim fr As Long
    
    Application.ScreenUpdating = False
    
'   Find last row in column B with data
    lr = Cells(Rows.Count, "B").End(xlUp).Row
    
'   Loop from last row up to first row backwards
    For r = lr To 1 Step -1
'       Check to see if column B is greater than 0
        If Cells(r, "B") > 0 Then
'           See if found row has been set yet (if not, set it, otherwise subtract 1)
            If fr = 0 Then
                fr = r
            Else
                fr = fr - 1
            End If
'           Populate columns F and H
            Cells(fr, "F").Value = Cells(r, "A").Value
            Cells(fr, "H").Value = Cells(r, "B").Value
        End If
    Next r
        
    Application.ScreenUpdating = True
    
End Sub

Though I would love to see a formula method!

 

[Cubist]

It wasn't clear to me that was the requirement, but here's the updated version:

Book1
	A	B	C			F
1	Item A	200
2	Item B	800
3	Item C	0				Item A
4	Item D	0				Item B
5	Item E	100				Item E
6	Item F	0
Sheet5

Cell Formulas
Range	Formula
F1:F5	F1	=LET(v,VSTACK(EXPAND("",ROWS(A1:A6),,""),FILTER(A1:A6,B1:B6>0)),DROP(v,ROWS(v)-XMATCH(TRUE,B1:B6>0,,-1)))
Dynamic array formulas.

 

[Fluff]

Another option

Fluff.xlsm
	A	B	C	D	E	F	G	H
1	Item 1	200
2	Item 2	800
3	Item 3	0				Item 1		200
4	Item 4	0				Item 2		800
5	Item 5	100				Item 5		100
6	Item 6	0
Sheet5

Cell Formulas
Range	Formula
F1:F5	F1	=LET(r,ROW(XLOOKUP(TRUE,(B1:B6>0),B1:B6,,,-1)),f,FILTER(A1:A6,B1:B6>0),rf,ROWS(f),IF(r=rf,f,VSTACK(EXPAND("",r-rf,,""),f)))
H1:H5	H1	=XLOOKUP(F1#,A1:A6,B1:B6,"")
Dynamic array formulas.