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.
View 6 Replies
ADVERTISEMENT
Jun 29, 2006
How can I write a For-Next Loop and have the counter skip values in between. For example:
For i = 8 to 17
When that is completed, the counter jumps to 21:
For i = 21 to 30
When that is completed, the counter jumps to 34:
For i = 34 to 43
View 3 Replies
View Related
Sep 7, 2012
Creating a formula that basically has a big IF statement. I hope this makes sense, so here goes. I am looking to find the "age" or "elapsed days" of an ID from when they created an account to when they stopped. This might be very complex and if there's not a way to calculate the Elapsed days (even though that's what I prefer), perhaps there is some formula that can return a value like a "1" or "True" or something that lets me know the statement is true.
original formula from post 3261273
{=IF(D2>0,A2-INDEX(A$2:D$16,MATCH(B2&"1",B$2:B$16&C$2:C$16,0),1),"")}
New formulas needed or revised from the above:
Formula 1: If the ID in column 2 are the same, and if there is a value in column 3 (Create) and a value in Column 4 (Suspend) AND there is no value in Column 5 (Stopped), then subtract the dates in column 1 for Create and Suspend. My manual calculation result is in the column called "Elapsed Days (Create to Suspend but NO Stopped)
Formula 2: If the ID in column 2 are the same, and if there is a value in column 3 (Create) and a value in Column 4 (Suspend) AND there is no value in Column 5 (Stopped) OR Column 6 (Unsuspend), then subtract the dates in column 1 for Create and Suspend. My manual calculation result is in the column called "Elapsed Days (Suspend Only, No stopped or Unsuspend)"
Formula 3: The ID in column 2 does not have to be the same, but if there is a value in column 3 only (Create) and there is no value in the Suspend, Stopped, or Unsuspend columns, then place a "1" in the cell. My manual calc is the "Create Only" column.
Event Date
ID
CREATE
Suspend
STOPPED
Unsuspend
Elapsed Days (Create to Suspend but NO Stopped)
Elapsed Days (Suspend Only, No stopped or Unsuspend)
[Code] .........
View 1 Replies
View Related
Aug 21, 2009
I have two sheets. First contains Department # 0002549, and the other one contains 5 more digits(#02001 0002549). I will need to find the exact match for the first sheet to contain all 12 digits. THey are all in different order, so I probably have to match them first then add whatever the first 5 digits number. How should I tackle this issue? I was thinking to use vlookup but how could you use vlook up if only last 7 digit matches and add addtional digits to exisiting number?
View 9 Replies
View Related
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.
View 1 Replies
View Related
Feb 16, 2008
I need to sort all my pay pal transactions I need all my debits in one row and credit in another.
View 14 Replies
View Related
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.
View 9 Replies
View Related
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.
View 5 Replies
View Related
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).
View 4 Replies
View Related
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"
View 12 Replies
View Related
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 ?
View 1 Replies
View Related
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)))
View 9 Replies
View Related
May 6, 2014
Find attached , formula on d2 and e2 , raw data sheet1
Attached Files : counting seller paid.xls
View 3 Replies
View Related
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?
View 6 Replies
View Related
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?
View 1 Replies
View Related
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
View 7 Replies
View Related
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 ....
View 9 Replies
View Related
Dec 11, 2012
We've got a bug in our finance system where it can't handle any transactions that have sales but no related commission. The BI team provides a CSV file separately with this information and the sales team has to manually input it. I know how to create a template that can be uploaded into the system but don't know how to pull the data into the template from the CSV file.
I've created the attached example and what i'd like is a drop down box in cell B1 (template tab) listing all the customer codes in column B on the data tab and then based on your selection all the related transaction lines pull into columns A to F (starting on row 4).
Manual Invoicing Query.xlsx
View 1 Replies
View Related
Jun 7, 2009
Can someone tell me what I'm doing wrong for the weekly sums in this spreadsheet? The monthly sums work fine.
PS I can't use pivot tables. This spreadsheet is a quite small part of a more expansive set of worksheets, from which I am pulling data.
View 7 Replies
View Related
Feb 21, 2014
How to form sub-totals quarterly in a typical list of transactions. Twist being it is not calendar year quarters but quarters from from 5 April to 5 July, 6July to 5 October etc. Typical table columns looks like:
Date Amount Transcation
5/4/14 200.50 bolts
7/5/14 50.10 screws
6/6/14 10.00 bolts and nuts
--------------------------------------------
SubTotal 260.60
12/7/14 10.00 bolts and nuts
10/8/14 40.00 rivets
4/10/15 10.00 screws
--------------------------------------------
Sub Total 60.00
View 1 Replies
View Related
Jun 28, 2009
I have this spreadsheet that has over 20,000 rows. I was asked to build a search page to will bring back all transactions based on a primary key (account number). Here is a sample:
Account NumberDateComments2343566/2/2009 $ 111.43 3453465/1/2009 $ 89.34 5676552/5/2008 $ 643.23 8078989/3/2008 $1,245.34 12543612/5/2008 $ 56.65 2343562/2/2009 $ 343.54 3482459/9/2008 $ 78.76 9345641/2/2009 $ 356.22 2343565/3/2008 $ 529.66
The idea is to enter an account number like 234356 click a button and bring back:
Account NumberDateComments2343566/2/2009 $ 111.43 2343562/2/2009 $ 343.54 2343565/3/2008 $ 529.66
I got the button part done and using vlookup it brings back the first line. The problem is that it won't bring back all the rows just the first one.
View 9 Replies
View Related
Dec 16, 2011
So I'm trying to create a balance ledger to track my transactions at different locations.
This is basically what I have:
C4 = numerical value for site A
D4 = balance for site A
E4 = numerical value for site B
F4 = balance for site B
G4 = total balance of both sites
Values for C4 and E4 are manually entered.
D4: =IF(OR(ISBLANK(D3), ISBLANK(C4)), "", D3+C4)
F4: =IF(OR(ISBLANK(F3), ISBLANK(E4)), "", F3+E4)
G4: =IF(AND(ISBLANK(D4), ISBLANK(F4)), "", D4+F4)
I have these formulas auto-filled to the bottom of the sheet of each column. The problem I'm having is that with this setup, the return on the G column is giving me
#VALUE!
for all rows that do not have any values entered yet. Is there any way to fix the formula in column G so that it reads the value of the cell instead of the formula in the targeted cell?
I am using Office 2010 on Windows 7.
View 2 Replies
View Related
Feb 3, 2010
I want to use the macro to run an XML file but I would like the images to run from 001 to what ever say 300 is this possible? So rather that 1,2,3 the counter would be 001, 002, 003 etc.
View 3 Replies
View Related
Apr 28, 2009
I would like to put a counter (number) in my excel sheet. Consider that like a bill number. Any time I open my Excel I want to have new number on top. For example 201, 202 so on and so forth. I would appreciate if anyone had this project before and willing share it with me.
View 9 Replies
View Related
Apr 13, 2007
I am using excel with system called PI, where i can retrive datas from network
I have proble with calculating day (With sum) becoz date is virying daily and data changes accordingly
Example
22-Mar-07 23:40:101
23-Mar-07 23:40:101
24-Mar-07 23:40:101
25-Mar-07 23:40:101
26-Mar-07 23:40:101
27-Mar-07 23:40:101
28-Mar-07 23:40:101
29-Mar-07 23:40:101
30-Mar-07 23:40:101
31-Mar-07 23:40:101
01-Apr-07 23:40:101
02-Apr-07 23:40:100
03-Apr-07 23:40:100
04-Apr-07 23:40:100
05-Apr-07 23:40:100
06-Apr-07 23:40:100
07-Apr-07 23:40:100
08-Apr-07 23:40:100
09-Apr-07 23:40:101
10-Apr-07 23:40:101
11-Apr-07 23:40:101
For this example Now i want to start counting from 9th April.
Sure i dont want to calculate from 22 march to 1st april
Sure this wont be same every time, becoz after 10-15 days this figure will become zero and stays for 0 for 10-12 days and again become 1 and keep continue
So every day automatically 1 day go out from top and 1 day will add on bottom with either 0 or 1 (But if zero it will continue for few days or 1 than also case is same)
So i like to start counting from where it changes from 0 to 1 again
Hope i explained question properly
View 7 Replies
View Related
Sep 15, 2014
Reset Counter to Zero:
I have Record ID on Column A: Auto Increment
I have a vendor Name on Column B: Vendor 1, Vendor 2, Vendor 3
I have a Code on Column C, Code A, Code B
What I want to happen is the Counter to count +1 each time. it sees the same vendor, same code and only to count to 4. As soon as it counts to 4; the next counter should be 0 (Zero).
Col A Col B Col C Col D
======= ====== ===== =======
Record ID Vendor Code Counter
======= ====== ===== =======
1 Vendor 1 Code A 1
2 Vendor 1 Code A 2
3 Vendor 1 Code A 3
4 Vendor 2 Code B 1
5 Vendor 1 Code A 4
6 Vendor 1 Code A 0
8 Vendor 2 Code B 2
9 Vendor 2 Code B 3
10 Vendor 1 Code B 4
12 Vendor 1 Code A 1
13 Vendor 1 Code A 2
14 Vendor 1 Code A 3
15 Vendor 2 Code B 0
It can be either in VBA or formula...
View 2 Replies
View Related
Mar 28, 2008
we have a spreadsheet which we encourage our employees to use but not sure if they are or not. is it possible to put a counter onto the spreadsheet so we can see how many times it is being opened
View 10 Replies
View Related
Jun 11, 2009
I'm copying values from one workbook to another. Each value has to be checked, so I do this in two steps.
View 2 Replies
View Related
Sep 22, 2011
I have a input worksheet and in this user have to manually enter the sequence no. The sequence no. heading is not always in one cell that can be in any between any cell from D14 to D21.
I need some code which start the sequence no. with 1 and everytime I click add button. It find the sequence no. in range between D14: D21 and add 1 in the relavant adjacent cell (which can be in from E14 to E21).
See Table below
Start withColumn DColumn ERow No. can be any from 14 to 21Sequence No.1Clicked AddDesiredColumn DColumn ERow No. can be any from 14 to 21Sequence No.2DesiredColumn DColumn ERow No. can be any from 14 to 21Sequence No.previous val +1 and so on
View 2 Replies
View Related
Feb 5, 2014
I am trying to use the sumproduct as a counter. I have done it numerous time with success; however, I am trying to use it where it incorporates a wildcard text to include or exclude as part of my count criteria:
=SUMPRODUCT((Sheet1!$A:$A=Sheet2!$A9)*(Sheet1!$C:$C="Fixed")*(Sheet1!$AG:$AG"*CCAP"))is
My issue is that it does not recognize the wildcard, and does not return the correct item.
View 7 Replies
View Related
