Returning a compact list without gaps ?

[akardar1]

Hi !!

In column A, I have PnL values (both positive & negative numbers)
In column B, I have a formula that returns a value only if a value in A is positive value: =IF(A2<0,"",A2)
In column C, I have a formula that returns a value only if a value in A is negative value: =IF(A2>0,"",A2)
Therefore coloum B and C, either have values of blank cells (with formulas in them).

What formula can i put in coloum D that can return the contents in coloumn B but without all the gaps ?
(a condensed list tat returns all the positive values without any gaps)
I have attached a screenshot showing an example in green of the desired outcome (manually entered)

I am using Excel 365

 

[hagia_sofia]

Hello, one way could be:

=FILTER(B2:B8,ISNUMBER(B2:B8))

 

[Peter_SSs]

Or you could possibly go straight from the col A data without needing col B

=FILTER(A2:A8,A2:A8>0,"")

 

[Fluff]

I am using Excel 365

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[akardar1]

Hello, one way could be:

=FILTER(B2:B8,ISNUMBER(B2:B8))

This worked - thank you !

 

[Peter_SSs]

This worked - thank you !

If you do still want to use column B rather than get the results directly from column A, you could also use

=FILTER(B2:B8,B2:B8<"","")

You should also note in both of my suggested formulas there is an extra argument ( ,"") at the end. Something like that would be needed if there was no numbers in column B (that is, all numbers in col A are negative)