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.
Basically I have two sets of data. One will be new each week. I'd like to use the non-changing data as a base to compare new data to. The formula would need to match multiple values, including a 'time between', and then return whether a minimum rate has been met.
in another column, i have 400 rows filled with different numbers, from 1.4 to 23000,7840.
I need to find out all the possible combinations of which numbers from that 400 can make up 23753.2570.
so if my number was 40,000, and i had 4 cells with 10,000 in them, the outcome will be:
"There are 4 possible combination to your value" then paste it into a new worksheet with the results [cell1] = 10000 + [cell2] = 10000 + [cell3] = 10000 + [cell4] = 10000 Total = 40000
i found this code, but i tested it on the example above and it said "All Combinations exhausted" which they wasn't?
Sub findsums()
'This *REQUIRES* VBAProject references to 'Microsoft Scripting Runtime 'Microsoft VBScript Regular Expressions 1.0 or higher
Const TOL As Double = 0.000001 'modify as needed Dim c As Variant
Have a monthly report process in which I need to count to combinations of values in 2 columns. Example:
A 1 A 1 A 2 A 3 B 2 B 1 B 3 B 3 B 4
Count results: A&1 = 2, A&2 = 1, etc. I'd like to get a summary table of the results. I see how to use sumproduct to count combinations, but that requires a sumproduct formula for every combination. The 2 colums are lengthy and the contents vary each month, so building the formulas each month will be a fair amount of work. Is there another way?
I'm trying to create a table of all possible combinations based on 4 values (8,4,2,-2) into 6 columns. An example is below of what the table should look like
Field 1Field 2Field 3Field 4Field 5Field 688888888888488884488844488444484444488888288882288822288222282222222222288888-244-2-2-2-2444444884422
I have a list in excel with ten values, but I need to get all combinations possible, picking 4 of 10 each combination. This must be as a combination and no permutation or variations either, I mean, values can't be repeated in a single combination.
I'm struggling to do this in a Macro but I don't get it.
The aim is to find those combinations of variable values which generate highest total gain. I attached the spreadsheet which shows the variables (A through K) and a Gain column. I created 5 additional tabs which show all possible 2,3,4 and 5-member combinations of the variables. These tabs are like coordinates of which variable combinations should be examined. As an example I used the first combination from the second tab = A and B. If you look at these two columns on the EXAMPLE CALCULATION tab you will see 7,7 in the Number combination which is the first number pair for these two variables. The headings of the red and the yellow columns calculate the total count for this number pair and the total gain. These were recorded on a separate EXAMPLE RESULTS tab along with some other pairs which appear afterwards (these were recorded only from the first 39 rows of the AB data). I need a macro which will cycle through each variable pair (only using the combinations from the tab 2 for now, annd later from 3,4 and 5 tabs) collecting statistics for each unique number combination it encounters (printing to a separate sheet one after one), such as shown on the EXAMPLE RESULTS.
I have .csv file from which the data is importing to master schedule. i have a column in .csv file which is spitted into multiple columns. and my need is i have to compare this each individual cell value with the range of header values in master schedule and if match found i have to place that cell in the row.
I am adding 2 attachments one is .csv and other is master schedule.
I couldn't able to find where i have to attach my files.
In sheet1, I have the borrowers and outstanding per employee. In the Area Avg sheet, I have the average borrowers and outstanding in each area as this is the standard for comparison (I got this through the subtotal function).
Now what I have to do is in sheet1, format(change the color) each cell under borrowers which is greater than the average of the relevant area. Since, (in my actual data) the name of the area may be duplicate in different regions, the reference value (average borrowers of the area) from "Area Avg" sheet has to be traced through multiple criteria which are: Division, Region and Area. The same also has to be done for outstanding but the solution can be provided for borrowers only.
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.
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.
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.
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).
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"
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.
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:
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?
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.
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).
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
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:
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?
