Returning a compact list without gaps ?

akardar1

New Member
Joined
Dec 22, 2010
Messages
25
Office Version
  1. 365
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
 

Attachments

  • winsCompact.png
    winsCompact.png
    29.5 KB · Views: 6
Or you could possibly go straight from the col A data without needing col B

Excel Formula:
=FILTER(A2:A8,A2:A8>0,"")
 
Upvote 0
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’)
 
Upvote 0
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

Excel Formula:
=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)
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top