How to split numbers into columns with negative and positive values, without empty cells?

Question

Given positive and negative values in column A of an Excel sheet, I want column B to list the positive values from column A, and column C to show the negative values, in the order in which they appear in column A.

However, I don't want any empty cells in columns B and C.

So, I do NOT want:

   A     B     C
----------------
 All   >=0    <0
 100   100
-300        -300
-800        -800
 900   900
 700   700
-200        -200
 900   900

But instead, I want:

   A     B     C
----------------
 All   >=0    <0
 100   100  -300
-300   900  -800
-800   700  -200
 900   900
 700
-200
 900

Answer 1

Not on a windows box. I bet there is a formula that would do it. VBA for sure: In VB Editor click Insert Module then type:

Sub posInBnegInC(ByRef aList as range)
    Dim cll as Range
    For each Cll in AList
        If cll.value2 > 0 then
            Sheet2.Range("B65000").end(xlup).offset(1,0).value2 = cll.value2
        Else
            Sheet2.Range("C65000").end(xlup).offset(1,0).value2 = cll.value2
        End If
    Next cll
End Sub

You can add the logic to handle value of exactly 0 if such exists. As it is the code will copy the zeros to column C. If there is non-numeric data in column A it won't work, but i assume all A numeric. Then you can run in a variety of ways, including:

Sub DoIt()
    call posInBnegInC(Sheet1.Range("A2:A65000"))
End Sub

Answer 2

You're basically asking how to get the n-th filtered value from some column into the n-th row of another column.

Microsoft explains getting the n-th filtered value in Excel: Finding the nth Value That Meets a Condition. This uses "array formulas" to perform calculations on items in an array (a list). Microsoft's example can be extended to the get the value matching a row number. If your array is in A2:A8, and you want to only show values >= 0, then the following can be used in B2:B8 (not tested):

=iferror(index(A2:A8, small( if(A2:A8 >= 0, row()-1, ""), row()-1) ), "")

Be careful to enter this as an array formula: first select cells B2 thru B8, then paste the above into the formula bar, and save it by pressing Ctrl+Shift+Enter (or Command+Return in Office on a Mac, or Command+Shift+Return in OpenOffice on a Mac).

For C2:C8, repeat with the >= 0 condition replaced with < 0.

To decipher this step-by-step, I've added the following formulas to an OpenOffice Calc worksheet, with its results below. Note that in OpenOffice one needs a semicolon to separate function parameters, while in Excel one might need a comma, hence the semicolons in the next formulas.

In D2:D8:

=if(A2:A8 >= 0; row()-1; "")

This shows the row number minus 1 if the value in the same row in A2:A8 is positive, or an empty cell otherwise. Subtracting 1 converts rows 2 thru 8 into a position of 1 to 7, to ignore the first header row.

So, now we know that the 1st, 4th, 5th and 7th values from A2:A8 are positive.

Next, in E2:E8:

=small( if(A2:A8 >= 0; row()-1; ""); row()-1 )

This takes the earlier results as the input for small(..., k) which takes the k-th smallest number shown earlier in D2:D8. (That list happens to be sorted from 1 to 7, but that is not even required for the small function.) Here, we set k to the current row number minus 1 to ignore the header row again. So for the 3rd row, we get the position of the 2nd positive value in A2:A8. But for E6 and down no value is found, showing an error.

In F2:F8:

=index(A2:A8; small( if(A2:A8 >= 0; row()-1; ""); row()-1 ))

This takes the positions 1, 4, 5 and 7 (and the errors) from the previous step as input for index(..., k), which finds the k-th value from A2:A8, or an error if k is invalid.

Now, F2:F8 still shows an error for the last rows. In Excel 2007 and later, one can use iferror to show nothing in case of an error, like used in the very first formula above. OpenOffice does not support that, but needs if(iserror(...); ""; ...), replacing both ... with the formula from F2:F8. Not nice.

Alternatively, use index(A2:A9; ...) to include cell A9 when getting the actual value from the first column, and then somehow make OpenOffice use small(...; 8) if no more values are found. Like in G2:G8:

=if(A2:A8 >= 0; row()-1; 8)

This still searches positive numbers in the 7 values from A2:A8, but now returns 8 if not positive.

In H2:H8:

=small( if(A2:A8 >= 0; row()-1; 8); row()-1 )

And finally in I2:I8:

=index(A2:A9; small( if(A2:A8 >= 0; row()-1; 8); row()-1 ))

Here, the value 8 maps to the value in A9. In the example screenshot A9 holds some dots, which are then shown in H6:H8 as no more positive numbers are found.

Like Microsoft writes: if you really want to master formulas in Excel, you need to know how to use array formulas. Enjoy.

Answer 3

Arjan already mentioned this twice so but I'm trying to post this as an actual answer instead of a comment. He said the post is locked but I'm so fresh to Stack Exchange that I don't get why it's locked but I can still post this as an answer. Anyway, Microsoft has an exact solution for this. I would only modify it slightly by wrapping it in IFERROR to return blank instead of #NUM! errors.

---

http://support.microsoft.com/kb/101167 As the KB article states, be sure to enter them as array formulas

For the positives:

{=IFERROR(INDEX($A$2:$A$8,SMALL(IF($A$2:$A$8>0,ROW($A$2:$A$8),""),ROW()-1)-ROW($A$2:$A$8)+1),"")}

For the negatives:

{=IFERROR(INDEX($A$2:$A$8,SMALL(IF($A$2:$A$8<0,ROW($A$2:$A$8),""),ROW()-1)-ROW($A$2:$A$8)+1),"")}