Splitting Data

[jez123456]

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

 

[Andy Brown]

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

 

[David Jessop]

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]

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

 

[Andy Brown]

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