I'm Trying To Find An Excel Formula....

[rhythmcrazy]

...which will extract amounts higher than 0 from a master list on one worksheet, and list them on a different sheet in the same workbook, so that only amounts higher than 0 will be listed and all zero amounts will be ignored. If I knew how to upload an Excel example I would. I hope someone can help. Thank you

[jim360]

Not sure of the exact formula to generate the list in a simple way but some sort of test that initially goes "= If(A1=0,"",A1)" would be a good start. That does leave annoying gaps, though, so the best way might simply be to sort the list in decreasing value in the first place so that all zeros are at the end.

[bazwillrun]

Best way is a VBA script

Obviously I dont know where on your sheets your data is but this script looks at data in Sheet1 column A and anything over 0 it copies to Sheet2 column A
I've assumed Column headers in Sheet2, if no headers there will just be a blank line at the top.

to use this in Excel go to View>ViewMacros> give it a name then Create
copy and paste the following after the line of asterix below.
once youve pasted it delete the line Sub test() and the last End Sub.

Save it and make sure you save as an .xlsm file

to run it from you workbook , Macros > View Macros, select it and hit Run

*****************************************
Sub test()
Dim LR As Long, i As Long
With Sheets("Sheet1")
LR = .Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
With .Range("A" & i)
If .Value > 0 Then .Copy Destination:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
End With
Next i
End With
End Sub

[bazwillrun]

forgot to mention, you can add a button for the Macro via the Quick Access toolbar

[rhythmcrazy]

Thanks everybody for your answers, I will have a look at them - need some time to do that. Will come back if I have any more problems.

[rhythmcrazy]

Jim I think I'll go with your method, it seems quick and easy. Thank you, and thanks also to bazwillrun for your input.

[jim360]

It might be quick and easy but it won't quite achieve what you are hoping for, I think -- baz's looks to be far better if more tricky to do.