Positive and Negative Lists

[PaulFogel123]

I have a list of 30 values. Some are negative and some positive. Leaving them where they are, I wish to extract and sort them into two separate columns on a different sheet. What's a simple way to do this?
Paul

 

[shrivallabha]

If Sheet1 column A houses your list then you can use following to generate negative list.
=IF(SMALL(Sheet1!A:A,ROWS($A$1:A1))<0,SMALL(Sheet1!A:A,ROWS($A$1:A1)),"")
Positive list
=IF(LARGE(Sheet1!A:A,ROWS($A$1:A1))>0,LARGE(Sheet1!A:A,ROWS($A$1:A1)),"")

Drag the formulas down until you get blanks and some more if numbers are supposed increase in future.

 

[Khalid NGO]

Hi Paul,

Or you can use the filter like this:



Enter 0 in greater than filed and hit okay, than simply copy/paste.

Similarly, use the less than for negative values.

Regards,

 

[PaulFogel123]

If Sheet1 column A houses your list then you can use following to generate negative list.
=IF(SMALL(Sheet1!A:A,ROWS($A$1:A1))<0,SMALL(Sheet1!A:A,ROWS($A$1:A1)),"")
Positive list
=IF(LARGE(Sheet1!A:A,ROWS($A$1:A1))>0,LARGE(Sheet1!A:A,ROWS($A$1:A1)),"")

Drag the formulas down until you get blanks and some more if numbers are supposed increase in future.

Thank you! Works just as I wanted it to. As a bonus, the list is sorted from highest to lowest, which is also what I wanted.

 

[PaulFogel123]

Thanks K

Hi Paul,

Or you can use the filter like this:

View attachment 35272

Enter 0 in greater than filed and hit okay, than simply copy/paste.

Similarly, use the less than for negative values.

Regards,

Thanks Khalid. As this list will be used by outside users, I cannot assume they have any skills in Excel. The formulas LARGE and SMALL will work better for my user group.
Paul