# I have a spreadsheet with a column with positive and negative numbers. How can I separate them into 2 separate columns?

I have a spreadsheet with a column with rows of positive and negative numbers in no set order. I want to move all of the negative numbers to an adjacent column so that I end up with two columns, one with positive numbers in all of the rows and one with negative numbers in all of the rows. How can I do this?
Founder & Managing PartnerAsked:
###### Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
Hi,
you can make 2 new columns.
in the first column (B) , top cell use:
=IF(A1<0,A1,"")
and in the second column (C) , top cell use:
=IF(A1>=0,A1,"")
Assumptions:
column A has the original values.
You then drag the formulas down so it fills the rest of the cells with the correct functions
0
Excel MVPCommented:
Hi,

this is a VBA solution. It assumes your values begin in A1 and are in column A. The positive numbers will go to column B and Negative to column C...

Sub PositiveAndNegative()
Dim LastRow As Long, MyRange As Range, i As Integer, j As Integer
Range("A1").Select
Selection.End(xlDown).Select
LastRow = ActiveCell.Row

For j = 1 To LastRow
If Range("A" & j).Value > 0 Then
Range("B" & j).Value = Range("A" & j).Value
End If
Next

For i = 1 To LastRow
If Range("A" & i).Value < 0 Then
Range("C" & i).Value = Range("A" & i).Value
End If
Next
End Sub

Have a nice day and good luck.
PosAndNeg.xlsm
0
Excel MVPCommented:
Hi,

i just modified it a bit, it now deletes all the blank cells in columns B and C and gives only the values...

If you needed the values to stay in the same row, than this will be of no value to you, but if you just needed the values, this is a much cleaner solution than the previous.

Sub PositiveAndNegative()

Dim LastRow As Long, MyRange As Range, i As Integer, j As Integer

Range("A1").Select
Selection.End(xlDown).Select
LastRow = ActiveCell.Row

For j = 1 To LastRow
If Range("A" & j).Value > 0 Then
Range("B" & j).Value = Range("A" & j).Value
End If
Next

For i = 1 To LastRow
If Range("A" & i).Value < 0 Then
Range("C" & i).Value = Range("A" & i).Value
End If
Next

Rng = "B1:C" & LastRow

Range(Rng).Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.Delete Shift:=xlUp
Range("A1").Select

End Sub

Good luck...
PosAndNeg.xlsm
0
Microsoft MVP ExcelCommented:
Hello,

here is another formula approach. Unlike the suggestion in the first comment, this approach will not leave any blank cells.

You need two helper columns that return the row number if the number in column A is positive (or negative respectively). Then you can use a formula like

=IFERROR(INDEX(\$A\$1:\$A\$45,SMALL(\$B\$2:\$B\$45,ROW(A1))),"")

to return all the positive numbers in column A. Copy down, of course. See attached for a working example. Hit F9 to refresh the numbers and see the columns update. You can hide the helper columns if you want to keep the spreadsheet tidy.

cheers, teylyn
Book1.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.