Importing monthly credit card transaction CSV file into budgeting tool...

[Alps]

Dear MrExcel Gurus,

I am using a web based budgeting solution and I have a credit card transaction file that is in CSV format that I will be importing monthly. As shown in the screenshot below, the Type column shows types as either Payments or Purchases. The Amount column shows Payments as a negative number and Purchases as a positive number. I would like to change this in Excel to show:

Payment=Positive Number
Purchase=Negative Number

As I will be repeating this on a monthly basis, I am looking for the most automated method to manage this.

With kind regards | Mit freundlichen Grüßen | Meilleures salutations

Alps

 

[Jeffrey Mahoney]

Enter a -1 in a cell. Copy that Cell. Highlight all the payment and purchase values you want to change. Press Ctrl-Alt-V. Select Multiply and click OK

 

[Jeffrey Mahoney]

I added a button on my sheet and added code like this. Highlight all the values you want to change then run this code.

Private Sub ChangeSign_Btn_Click()
  Dim Cel As Range
  
  Application.Calculation = xlCalculationManual
  For Each Cel In Selection
    Cel.Value = Cel.Value * -1
  Next Cel
  
  Application.Calculation = xlCalculationAutomatic
End Sub

 

[Alps]

Thank you Jeffrey. I am familiar with the manual method your first described. I was thinking would it be possible to use VLOOKUP with Absolute value function (ABS) to change negative payment values to positive and with the same formula positive purchase values to negative.

With kind regards | Mit freundlichen Grüßen | Meilleures salutations

Alps

 

[Jeffrey Mahoney]

You don't need VLOOKUP, you would have to have a huge list. If you're going to just change the sign with a formula then why not take each cell value *-1

Book3
	D	E	F
4	Payment	-267.16	267.16
5	Purchase	46.13	-46.13
6	Payment	-0.01	0.01
7	Payment	-4.51	4.51
8	Payment	-81.41	81.41
9	Payment	-1601.24	1601.24
10	Payment	24.45	-24.45
11	Payment	15.42	-15.42
12	Payment	11	-11
13	Purchase	5.78	-5.78
14	Purchase	1.14	-1.14
15	Purchase	3.56	-3.56
16	Purchase	61.89	-61.89
17	Purchase	901.45	-901.45
18	Purchase	4.01	-4.01
Sheet1

Cell Formulas
Range	Formula
F4:F18	F4	=E4*-1

 

[Alps]

Thanks Jeffrey. I have 100+ transactions per file, but I agree that your method will work for what I need. Thank you again for your help.

With kind regards | Mit freundlichen Grüßen | Meilleures salutations

Alps