# Bank Transactions - Grouping / Totaling Like Titles

Jun 1, 2014

I'm trying to group a year's worth of bank transactions. The initial data that was cut from pdf files is a date, payee and amount

1) how can I search down col A and give the sum of all like Payees, then total each set of similar payees? Maybe if first 6 characters match, then total until it comes to a different set.
Total each set.

2) then, I need to assign a category to each set of payees, so if contains usps, then add category "postage"

3) formula to find all postage totals and combine for a grand total per category.

usps15.23postage
usps14.32postage
usps5.23postage
fedex5.25postage
fedex10.22postage
shell45.28fuel
shell99.38fuel
qt27.38fuel
qt44.88fuel

## Temporarily Hide Transactions In Bank Statement?

Feb 3, 2014

I have downloaded some of my bank statements in excel format but they are just static data - ie, they are just numbers in boxes and the BALANCE column does not react when I take out a transaction.

I have put in a formula for the BALANCE column so it does now take its value from the previous day plus or minus transactions, but now I want to do additional things.

- How would I, for example, categorise several transactions as "HOLIDAY" [URL] ....... and then temporarily make them disappear so that I can see the effect of that on my balance? I can see how to hide/unhide transactions but that doesn't actually seem to have any impact on the balance column.

- Second query: how do I make my current spreadsheet a template so that when I download the next bunch of bank statements I can just apply all the formulae in this one to it?

## Game Points Bank Account (running Total For The Bank)

Nov 5, 2009

Sheet6

80You Need 18,000 Pts.IntermediateYour Credit is 0 Pts

Excel tables to the web >> Excel Jeanie HTML 4

i have a "Game Points Bank Account" that keeps track of the points users earn or spend in the game...what i need help is creating a running total for the bank..ive tried everything nothing works..

(this is actually like a traditional bank account)

each player starts with 10,000 points

*Row 8 is the only row to have formulas*

*Rows 9-11 are typed in to show my desired results*

Here is the point value "Ranking System" for Column T

Sheet6

Excel tables to the web >> Excel Jeanie HTML 4

## Move Row Titles And Use As Column Titles

May 22, 2007

I have a spreadsheet that has a list in column A 219 rows long. what i want to do is, if possible is move them so that they run accross the top of the spreadsheet as column headings.

Exampl:
As is:
A B C D E F G
1 ?
2 ?
3 ?
4 ?
5 ?
6 ?

After:..................

## Tracking Paid Transactions

Mar 25, 2014

I want to create a spreadsheet that I can export my transactions from my credit card onto -- is there a way to make it so that the transactions that haven't been covered by my most recent payment(s) are red, while the ones that are paid are green without manually going through & doing it? I know there's the IF, TRUE, FALSE formulas, but I'm confused on how to use them.

Basically, if I spend \$1,000 between 5 transactions and make a \$400 payment, I want the oldest transactions totalling up to \$400 to turn green, while the remaining are stay red until a new payment is posted.

## Sorting Paypal Transactions

Feb 16, 2008

I need to sort all my pay pal transactions I need all my debits in one row and credit in another.

## Counter For Transactions Within Account

May 11, 2009

I need to have a running counter of transactions within an account.

Solved with: C2=COUNTIF(\$B\$4:B4,B4)
(and copy down)
Example:
ACCTNO AMT Trans#
100125.001
100121.002
100122.50 3
1001 2.00 4
1001 5.00 5
100127.00 6
1013 .50 1
1013 2.50 2
1013 13.00 3

I need to solve for the Trans# I've included it here for clarity, but I need to be able to get that number based on the ACCTNO. Notice the Counter Resets to 1 when the ACCTNO changes from 1001 to 1013.

## Counting Transactions Per Agent

Sep 3, 2009

I have a group of users in cell C1 and i wanted to count how many times they have process a payment as long as its value in Cell D1 is more then or equal to 1.

I tried sumif, but its totalling the amount. but i wanted is the number of transaction.

## Can't Copy Bank Statement

Oct 12, 2013

So I want to copy my bank statement into excel, but when I do, rather than separating each line into a row of separate cells, it separate it into a column of separate cells. and it does this for each row in the same column.

So:
A1 is in the right spot
A2 should be B1
A3 should be C1
A4 should be A2
A5 should be B2
A6 should be C2
etc....

Any way I can sort this out at the touch of a button? (or a few buttons)

## UK Bank Holiday Calculator

May 2, 2006

I found a link to a website on one of the forum pages. I had a look in the website and it showed a formula for calculating when Easter falls - I didn't know it could be worked out, but it can!!

I therefore decided to investigate further. I picked up another formula to calculate the first MOnday in May and I have now put together a little spreadsheet that will calculate all bank holidays in the year entered in cell B1. It also takes into account additional bank holidays that exist when Christmas Day and/or Boxing Day fall on a weekend.

## Bank Interest Calculator

Dec 1, 2006

I'm an amateur to macro as I'm only in a low level class at a university... But I'm attempting to make a macro for a bank interest calculator. It asks your type of account(which then assigns an interest rate to it), how much money is in the account, and also how long th emoney will be in the account. I used a "Select Case" for the account types, but I seem to be struggling for it to work, it won't put the value of the total into the assigned cell, or it's just not computing it(as I get "0" each time I run it)...

Public Function BankCalculator()
'Bank Calculator for different accounts
'declare variable
Dim shtBank As Workbook, strAct As String, intMon As Integer, strLong As Integer, intTotal As Double
Set shtBank = Application.Workbooks("Bank Calculator.xls")
'input box for amount of money, assign address
intMon = InputBox(prompt:="How much money do you currently have in the account?", _ ......................................

## Compare Combinations Of Values / Transactions

Jan 25, 2014

I need to compare in and out of money in multiple bank accounts.

Imagine in row one i have all the "INs"
and in row two i have all the "OUTs"

Now, how do i compare say first transaction in row one to say 5 transactions in row 2 and find the relationship
It can be:

1. Transaction IN 1 = Transaction OUT 3
2. Transaction IN 1 = Transaction OUT 2+4
3. Transaction IN 1 = Transaction OUT 3, or Transaction OUT 2+4

So if its a direct relation it just displays where they are equal, if they aren't how it will display which multiple transactions will be equal, and if there are 2 different possibilities it will show both answers.

If its only In = Out its pretty straight forward, but how do i code it to search for combinations of transactions say 1+2+5 efficiently.

## Keep Track Of Bank Transaction In Budget - In And Out

Mar 19, 2014

Solution to keep track of bank transaction in a budget - both in and out

With:

=SUMPRODUCT(--(Table[[CAT]:[CAT]]=\$C5);--(Month(Table[[DATE]:[DATE]])=G\$3);--(Table[[IN]:[IN]]))

Table1:
DATE | CAT | IN | OUT
----------------------

The transaction CATegory and Date place the amount from table - column IN to the corresponding cell in my budget.
I would like my bank account to reflect both income an expenses from 2 columns in Table: IN and OUT

Workbook example: [URL] ...........

## Conditional Cell Color Changes And Transactions

Sep 7, 2009

1.1 In columns N and O, color the numbers in both the N and O cells green if, and only if, (a)the N cell's number is greater than O's, AND (b) both N and O cells' values are greater than the preceeding N and O cells' (i.e. a great value than one row higher in the column).

1.2 In columns N and O, color the numbers in both the N and O cells red if, and only if, (a)the N cell's number is less than O's, AND (b) both N and O cells' values are lesser than the preceeding N and O cells'(i.e. lesser values than one row higher in the column).

## Finding Difference In Timing Between Transactions

Oct 13, 2009

I have approximately 40 seperate sheets in one workbook. Each sheet is a unique part #. Each part has 6 different types of transactions possible. Let's say A-F. A-F each have a date associated when them of when the transaction occured. The transactions are sorted by date. I would like to write a formula that when Transaction A occurs what is the diffence in days until D transaction occurs. Or the time differnce between when B occured and the next F occured.

below is my datedif formula, but it obviously only works in a sequential order from top to bottom.

=IF(DATEDIF(M5,M6,"y")=0,"",DATEDIF(M52,M6,"y")&" years ")&IF(DATEDIF(M5,M6,"ym")=0,"",DATEDIF(M5,M6,"ym")&" months ")&DATEDIF(M5,M6,"md")&" days"

## Info For Transactions - Calculate Commissions

Aug 23, 2013

I have a spreadsheet with detail info for transactions. There are multiple columns...but these are the ones I'm concerned with. ie

Cell A4 has the date range ( 1 month) ie "04/01/2013 - 04/30/2013"

below starts on B5
Cell B - Cell D - Cell I
vendor - Qty - Profit
H20 Month \$50 2 7.00
H20 \$30 Mo Unl T&T 2 4.20
H20 Month \$60 21 88.20
Page Plus Unl \$55 3 22.29
PagePlus Unl \$39.95 6 32.34
etc...
Cell A32 has the date range ( 1 month) ie "05/01/2013 - 05/312013"

and the vendor, qty and profit like above again....

The above

To the right of this I have :

Cell L2 Cell M2 Cell N2 Cell O2 Cell P2 Cell Q2 Cell R2
Company - April - Topup profit - May Topup Profit - June - Topup profit
H20 29 110.60 71 261.10 93 342.95
PagePlus 19 55.05 25 106.72 14 44.70
etc...

at the bottom, I have the totals for each cell from M2 thru R2.

How can i get L2 thru R2 to sum up the detail amounts on Column I by Column B ?

## SUM Monetary Transactions Based On Two Criteria

Feb 20, 2014

I want to SUM monetary transactions based on two criteria.

1.) if the transaction occurred within a certain month (Jan)
2.) based on the transactions category ("Obligations")

I have two formulas which successfully validate the data individually but I need to combine them so that both criteria must be met before data is summed.

=SUMIF(REGISTER!E3:E1000,"Obligations",REGISTER!G3:G1000)

=SUM((IF(MONTH(REGISTER!\$B\$3:\$B\$1000)=1,REGISTER!\$G\$3:\$G\$1000,0)))

## Insert Bank Rows After Each Occurences

Sep 22, 2007

I want to insert two blank after each broker. I am copying each broker's information to another spreadsheet. each broker information may take several rows...after copy one broker information, I want to automatically insert two blank rows...how can I do that in VBA excel?

## Counting Paid Transactions Per Seller Database?

May 6, 2014

Find attached , formula on d2 and e2 , raw data sheet1

Attached Files : counting seller paid.xls

## Accessing Bank Balances Automatically With Excel?

May 31, 2014

Is it possible to have my Excel cash flow sheet access my bank checking account and automatically update with my current balance?

## Transactions Over A Month Broken Down To Each Product Type

Apr 27, 2009

I have a set of sales data which shows the dates of transactions and also the product type that was sold. I want to see the monthly sales for each product type. I can get a total for all product types over the months using the following:

=COUNTIF(Licences!E2:E9999, "<39600")-COUNTIF(Licences!E2:E9999,"<39569")

36900 = Jun-08
39569 = May-08

But I need it to also break it down for product a, b, c so I need something else to add to the formula?

## Count The Closed Transactions Month Wise

Dec 4, 2008

I would like to be able to count all the closed transactions for the month of May and then add Column B if they match with May ....

## Auto Totaling

Mar 4, 2009

I have a column data on a worksheet, the PO number for specific purchases where a PO number may be listed multiple times. One a separate worksheet within the same workbook, I have a PO summary section where PO numbers are manually entered and the next column sums the the total value of each PO.

Is there a way I could have the Summary section identify POs and automatically total them without having them manually entered?

## Totaling Various Cells

Apr 15, 2009

Picture a bank statement (several in fact) in Excel format. In addition to all the typical bank statement fields, like date, description, amount, etc., there is also a type field, e.g., food, gas, etc. I am trying to total all amounts of each type, e.g., gas total = \$200.00, in one cell automatically. So far, I can't figure out a formula to do this nor a macro.

## Totaling Sub Totals

Apr 20, 2009

Let me first explain what my worksheet looks like.

## Using Lookup, Then Totaling The Row

May 27, 2009

I'm trying to use Lookup to find a particular name, then when finding that name I need to total what's in that row (numbers). The Lookup is successful, but I can't get any totals out of it. Tried using Offset to begin summing once I got to the row I was Looking for but that wasn't doing it (or doing it wrong)

## Totaling Columns

May 29, 2007

I am tracking sales commissions on a daily basis for each month. In column B are the daily dollar totals. In column C is the % to the quota that has thus far been reached. Column D has the average daily dollar amount needed to reach your quota based on cumulative daily revenue. It is this column that has me stumped.

Once the % to Quota (column C) reaches 100%, I want column D to stop calculating the average daily dollar amount needed.

The quota is \$322,786 (C2)

Column B (Daily Totals) = \$100,000
Column C (% to Quota) = 31%
Column D (Daily Avg. Needed for quota) = C\$2/work days left in month)

## Totaling Textboxes

Jan 23, 2009

I have about 21 textboxes that are going to be watched by 1 textbox called totaltime on a userform.
I created the code below to add up those 21 textboxes, but it's not showing anything even though they are populated, some with zeros and some with one through eight.....

## Autofill Dates Excluding Bank Holidays & Weekends?

Feb 26, 2014

Is there a way to autofill dates in excel to exclude weekends and bank holidays?

## Find Specific Withdraw Entries From Bank Statements

Jan 6, 2010

I use online banking for paying my monthly bills. I get different confirmartion for each pay transection. Pls look attached Excel file. Now I want to find specific bills expense with amount from all my 12 months bank statements. How can I get this task done.

Note: Each time get different confirmation which appears on my bank statement beside paid bill name. for example January 2009 statement GAS Z8A.... Feb 2009 GAS S2W. I hope this clarify. What I have to do if I want to get all my 12 months GAS money withdraw from bank statement. I use csv file for bank statement.