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?
Neal FreedFounder & 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.

MarsMCommented:
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
Gašper KamenšekExcel 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
Gašper KamenšekExcel 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
Ingeborg Hawighorst (Microsoft MVP / EE MVE)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.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Spreadsheets

From novice to tech pro — start learning today.