If F2 is -, then move that number to G2

M

mrsellshomes

New Member
Joined
Sep 23, 2017
Messages
8
I have bank statements that have some withdrawals (column G) in the deposit column (F). See example below. Can you PLEASE help me get these -$ figures into column G?! THANK YOU!!!

[TABLE="width: 1195"]
<colgroup><col><col><col><col><col><col span="3"></colgroup><tbody>[TR]
[TD]Post Date[/TD]
[TD]Transaction Description[/TD]
[TD]Account Number[/TD]
[TD]Account NumberTransaction Description[/TD]
[TD]Withdrawal[/TD]
[TD]Deposit [/TD]
[TD]Balance[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jan 01 2016[/TD]
[TD]Previous Balance[/TD]
[TD][/TD]
[TD]Previous Balance[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$1141.11[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Jan 03 2016[/TD]
[TD]Withdrawal Debit Card Signature Debit[/TD]
[TD]Withdrawal Debit Card Signature Debit[/TD]
[TD="align: right"][/TD]
[TD] -6[/TD]
[TD="align: right"]$1135.11[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Sort by date Sort by votes
Welcome to the MrExcel board!

mrsellshomes said:
I have bank statements that have some withdrawals (column G) in the deposit column (F). See example below. Can you PLEASE help me get these -$ figures into column G?! THANK YOU!!!
Click to expand...
I presume that red G was meant to be an E. If so, try this in G3 and copy down

=SUM(G2,E3:F3)
 
Last edited:
Upvote 0
Re: If E2 is -, then move that number to E2

My apologies! Yes. Withdrawals are in E. Deposits are in F. Some withdrawals are showing up in column F as - numbers. I need those to be moved to column E on the same row on if the number is negative in column F. Otherwise I need it to stay there. Tried the formula but it didn't work.
 
Upvote 0
Re: If E2 is -, then move that number to E2

mrsellshomes said:
I need those to be moved to column E on the same row on if the number is negative in column F. Otherwise I need it to stay there.
Click to expand...
Since a formula cannot remove a number from where it is, you must be looking for a macro?

Can you show us a slightly larger set of sample data so that we can see the variation in how it is arranged?

Also please show us the expected final result from that sample data so we know exactly what we are aiming for.
 
Last edited:
Upvote 0
Re: If F2 is -, then move that number to E2

I would be good with them copied to a third column or moved to E. Either way. You'll see the -3.04 and -79.46 that appear in column F. Those should be in column E or copied to a third column. As long as that third takes the negatives already in E and the negatives in F (there will never be a negative in E and F on the same row). You can see the desired result in the third column below. Again, there isn't a negative in E AND F in any row. I have thousands of rows :/

Example

[TABLE="width: 566"]
<colgroup><col><col><col span="2"><col><col></colgroup><tbody>[TR]
[TD]Post Date[/TD]
[TD]Transaction Description[/TD]
[TD]Account Number[/TD]
[TD]Account NumberTransaction Description[/TD]
[TD]Withdrawal[/TD]
[TD]Deposit [/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]CU 9200 SE 82ND AVE HAPPY VALLEY OR[/TD]
[TD="colspan: 3"]CU 9200 SE 82ND AVE HAPPY VALLEY OR[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Jan 22 2016[/TD]
[TD]Withdrawal POS #602303083491 POS SPACE AGE NO. 14[/TD]
[TD]Withdrawal POS #602303083491 POS SPACE AGE NO. 14[/TD]
[TD="align: right"]-10.21[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]CLACKAMAS OR[/TD]
[TD][/TD]
[TD="colspan: 2"]CLACKAMAS OR[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Jan 23 2016[/TD]
[TD]Withdrawal POS #602319735645 POS FRED MEYER FRED[/TD]
[TD]Withdrawal POS #602319735645 POS FRED MEYER FRED[/TD]
[TD="align: right"]-76.39[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]MEYER 143 HILLSBORO OR[/TD]
[TD="colspan: 2"]MEYER 143 HILLSBORO OR[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Jan 23 2016[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]-202.95[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Jan 23 2016[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]-82.95[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]SW BASELINE RUS HILLSBORO OR[/TD]
[TD="colspan: 3"]SW BASELINE RUS HILLSBORO OR[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Jan 23 2016[/TD]
[TD]Withdrawal POS #602402397261 POS SPACE AGE NO. 14[/TD]
[TD="colspan: 2"]Withdrawal POS #602402397261 POS SPACE AGE NO. 14[/TD]
[TD="align: right"]-3.04[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]CLACKAMAS OR[/TD]
[TD][/TD]
[TD="colspan: 2"]CLACKAMAS OR[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Jan 24 2016[/TD]
[TD]Withdrawal Debit Card Signature Debit[/TD]
[TD="colspan: 2"]Withdrawal Debit Card Signature Debit[/TD]
[TD="align: right"]-79.46[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]6.02E+09[/TD]
[TD="align: right"]6.02E+09[/TD]
[TD][/TD]
[TD]

[/TD]
[/TR]
</tbody>[/TABLE]



Desired result

[TABLE="width: 576"]
<colgroup><col span="9"></colgroup><tbody>[TR]
[TD]Post Date[/TD]
[TD]Transaction Description[/TD]
[TD]Account Number[/TD]
[TD]Account NumberTransaction Description[/TD]
[TD]Withdrawal[/TD]
[TD]Deposit [/TD]
[TD][/TD]
[TD][/TD]
[TD]or add to another column[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]CU 9200 SE 82ND AVE HAPPY VALLEY OR[/TD]
[TD="colspan: 4"]CU 9200 SE 82ND AVE HAPPY VALLEY OR[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Jan 22 2016[/TD]
[TD]Withdrawal POS #602303083491 POS SPACE AGE NO. 14[/TD]
[TD]Withdrawal POS #602303083491 POS SPACE AGE NO. 14[/TD]
[TD="align: right"]-10.21[/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"]-10.21[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]CLACKAMAS OR[/TD]
[TD="colspan: 2"]CLACKAMAS OR[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Jan 23 2016[/TD]
[TD]Withdrawal POS #602319735645 POS FRED MEYER FRED[/TD]
[TD]Withdrawal POS #602319735645 POS FRED MEYER FRED[/TD]
[TD="align: right"]-76.39[/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"]-76.39[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]MEYER 143 HILLSBORO OR[/TD]
[TD="colspan: 3"]MEYER 143 HILLSBORO OR[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Jan 23 2016[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]-202.95[/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"]-202.95[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Jan 23 2016[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]-82.95[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]-82.95[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]SW BASELINE RUS HILLSBORO OR[/TD]
[TD="colspan: 4"]SW BASELINE RUS HILLSBORO OR[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Jan 23 2016[/TD]
[TD]Withdrawal POS #602402397261 POS SPACE AGE NO. 14[/TD]
[TD="colspan: 3"]Withdrawal POS #602402397261 POS SPACE AGE NO. 14[/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"]-3.04[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]CLACKAMAS OR[/TD]
[TD="colspan: 2"]CLACKAMAS OR[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Jan 24 2016[/TD]
[TD]Withdrawal Debit Card Signature Debit[/TD]
[TD="colspan: 3"]Withdrawal Debit Card Signature Debit[/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"]-79.46[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Re: If F2 is -, then move that number to E2

Since you are happy to have them in a third column then a formula could be used, as shown in H3, copied down.


Book1
EFGH
1WithdrawalDeposit
2
3-10.21-10.21
4
5-76.39-76.39
6
7-202.95-202.95
8-82.95-82.95
9
10-3.04-3.04
11
12-79.46-79.46
Collect Negatives
Cell Formulas
RangeFormula
H3=IF(OR(E3<0,F3<0),MIN(E3:F3),"")
 
Upvote 0
Re: If F2 is -, then move that number to E2

Now I guess I just have to remove the negatives from F :) Forgot about that part :/
 
Upvote 0
Re: If F2 is -, then move that number to E2

mrsellshomes said:
Now I guess I just have to remove the negatives from F :) Forgot about that part :/
Click to expand...
Like this?


Book1
EFGHI
1WithdrawalDepositWithdrawalDeposit
2
3-10.21-10.21 
42525
5-76.39-76.39
6
7-202.95-202.95
8-82.95-82.95
9100100
10-3.04-3.04
112.232.23
12-79.46-79.46
Collect Negatives
Cell Formulas
RangeFormula
H3=IF(OR(E3<0,F3<0),MIN(E3:F3),"")
I3=IF(OR(F3="",F3<0),"",F3)
 
Upvote 0
You must log in or register to reply here.

Similar threads

O
  • Solved
Put amount in DEBIT,CREDIT columns based on matching part of item
Replies
9
Views
186
Omran Y
O
C
  • Question Question
Merge cells in a column based on values in other columns
Replies
1
Views
387
mumps
mumps
A
  • Solved
Extraction last row based on specific words and put in account column(debit,credit)
Replies
4
Views
192
Alaa mg
A
W
  • Question Question
sumifs dates
Replies
4
Views
620
whytewolves
W
MrDB4Excel
  • Question Question
Insert Value Based on Multiple Criteria
Replies
9
Views
1K
jtakw
jtakw

Forum statistics

Threads
1,223,911
Messages
6,175,327
Members
452,635
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top