Merging credit card and banking history amount polarity issue

I need to track past history and created OCR scans of old credit card statements to analyze alongside banking history. Dropped the CC data into a spreadsheet and then into Tiller.
Problem: all CC charges are showing as positive dollar amount and payments are negatives. Bank data is the opposite. How can I make a bulk change to the credit card data I have already incorporated into a Tiller Transactions sheet?

Hereā€™s how I would do it:

  • Filter your transaction list so it only shows the credit card data.
  • Copy the credit card transactions (all rows and columns of filtered data) out to a blank sheet.
  • Insert a new column next to your ā€œAmountā€ Column (eg. if ā€œAmountā€ is column E, insert a new column F).
  • In the top cell of the new column, add the formula =E1*-1, and copy that formula down for every row.
  • Select column F that now contains the new ā€˜reversedā€™ values and Copy them.
  • Click in the top cell of column E (which already contains a value) and from the Edit menu choose ā€œPaste Special/Paste Values Onlyā€, which will overwrite the old values with the new reversed values.
  • Delete column F.
  • Copy the transactions and paste them back into your Transactions sheet.
2 Likes

Thatā€™s a good solution from @jpfieber. The only thing I would do different is do it all in the main Transactions sheet. Iā€™d just add the new column next to Amount, drag down the formula, ā€œpaste specialā€ the values over to the Amount column, then delete the extra column.

@randy I just posted a very similar question. This solution occurred to me, but this amounts to treating the credit card account like an asset, and not a liability. Will this not throw off the calculations for my Balance Sheet? i.e. how would this work for reconciling the transfers from my chequing account to pay off my credit card?
Garth