Splitting Data

Hi, I have an excel spreadsheet with 4000 rows. Sample data from the Amount
column is as follows:

Amount
£0.00
£400.00
-£20.00
-£20.00
£100.00
-£50.00
-£8.99
-£44.57
-£30.00
-£9.39
-£20.00
£100.00

Is there a quick way to seperate this data into positve and negative amounts
to give:

Credit Debit
£0.00
£400.00
-£20.00
-£20.00
£100.00
-£50.00
-£8.99
-£44.57
-£30.00
-£9.39
-£20.00
£100.00

Many Thanks

"jez123456" <jez123456@discussions.microsoft.com> wrote in message
news:5374575C-BE0D-4B61-A278-C5D06558F306@microsoft.com...
> Is there a quick way to seperate this data into positve and negative
amounts

How quick? In the first column to the right, use

=IF($A1>=0,$A1,"")

& copy down. In the second column to the right,

=IF($B1="",$A1,"")

& copy down. Select & copy the 2 columns, then Edit -- Paste Special --
Values.

Rgds,
Andy

Hi,

Assuming your data is in column A, then just put into column B
=IF (A1>=0,A1,"")
and in column C
=IF(A1<0,A1,"")

Or put whatever else you want as the third parameter.

HTH,

David Jessop

"jez123456" wrote:

> Hi, I have an excel spreadsheet with 4000 rows. Sample data from the Amount
> column is as follows:
>
> Amount
> £0.00
> £400.00
> -£20.00
> -£20.00
> £100.00
> -£50.00
> -£8.99
> -£44.57
> -£30.00
> -£9.39
> -£20.00
> £100.00
>
> Is there a quick way to seperate this data into positve and negative amounts
> to give:
>
> Credit Debit
> £0.00
> £400.00
> -£20.00
> -£20.00
> £100.00
> -£50.00
> -£8.99
> -£44.57
> -£30.00
> -£9.39
> -£20.00
> £100.00
>
> Many Thanks
>

Many thanks, that works great. How do I now get a running Balance in the next
column? I.E.

Credit Debit Balance
£0.00 £0.00
£400.00 £400.00
-£20.00 £380.00
-£20.00 £360.00
£100.00 £460.00
-£50.00 £410.00




"David Jessop" wrote:

> Hi,
>
> Assuming your data is in column A, then just put into column B
> =IF (A1>=0,A1,"")
> and in column C
> =IF(A1<0,A1,"")
>
> Or put whatever else you want as the third parameter.
>
> HTH,
>
> David Jessop
>
> "jez123456" wrote:
>
> > Hi, I have an excel spreadsheet with 4000 rows. Sample data from the Amount
> > column is as follows:
> >
> > Amount
> > £0.00
> > £400.00
> > -£20.00
> > -£20.00
> > £100.00
> > -£50.00
> > -£8.99
> > -£44.57
> > -£30.00
> > -£9.39
> > -£20.00
> > £100.00
> >
> > Is there a quick way to seperate this data into positve and negative amounts
> > to give:
> >
> > Credit Debit
> > £0.00
> > £400.00
> > -£20.00
> > -£20.00
> > £100.00
> > -£50.00
> > -£8.99
> > -£44.57
> > -£30.00
> > -£9.39
> > -£20.00
> > £100.00
> >
> > Many Thanks
> >

"jez123456" <jez123456@discussions.microsoft.com> wrote in message
news:AB236E13-97F0-4022-B4BC-D23DACC7F456@microsoft.com...
> Many thanks, that works great. How do I now get a running Balance in the
next
> column? I.E.

Assuming a start figure in C2, use

=C2+SUM(A3:B3)

in C3 & copy down.

Rgds,
Andy