Matching Debits And Credits
Oct 14, 2008
Not sure whether this is possible, but it's always worth asking.
Is there a macro I can run that will go through each line and check if the invoice value in column I (rental amount) has a corresponding payment (shown in red).
It would need to match a positive to a negative value and check the Lessee name matches, then return a 'Y' in column J. Oh, and if there is a 'Y' in column J already, that line obviously cannot be matched again.
After that I can set something up to remove those tagged lines to a separate worksheet, leaving me with just the unpaid invoices.
Lessee NameLeasee #Invoice #Lease #Payment MethodDue DateCurrencySales Tax AmountRental AmountMatchBobCoC1cash5874CQ26/09/2008EUR(234.75)(1,235.50)
YBobCoC1cash5874CQ26/09/2008EUR(234.75)(1,235.50)YBobCoC1A15874DD01/09/2008EUR234.741,235.50YBobCoC1B15874DD01/09/2008EUR234.741,235.50YSmithCoC2A25615DD01/08/2008EUR293.931,547.00
YJonesCoC3A35611CQ01/09/2008EUR767.264,038.20JonesCoC4A45614CQ01/09/2008EUR127.88673.04SmithCoC2B25615CQ01/09/2008EUR293.931,547.00SmithCoC2Cash5615CQ30/09/2008EUR(293.93)(1,547.00)Y
View 11 Replies
ADVERTISEMENT
Jul 24, 2007
Q:How to delete reversal entries?
I have debits & credits in the same excel column and i want to delete the matching amounts but with opposite signs.
Example:
A B
Name Amount
1)Mr. A 2000
2)Mr. B 6000
3)Mr. A -2000
4)Mr. D 4000
5)Mr. A 2000
Now i want to matching amount of Mr. A of row 1 & 3 as these two entries are reversing each other. I am poor in english but hope that i have clarified the problem
View 7 Replies
View Related
Jan 18, 2009
how many debits can take place in my Savings Account if I provide the Current Balance and the No of Debits,Amount of Debits,Frequency of Each Debit.
Lets say, I have Rs 42978/- in my savings account at this moment and I have 2 different Debits taking place on different dates of the Month.
First Debit of Rs 750/- (12th of Month)
Second Debit of Rs 584/- ( 27th of Month)
I am also planning to add one more debit EMI (Equated Onthly Installments)for Rs 1127/- every month.
I need to know the No of Months I can go without paying my Savings Account as well as the Month and the year.
I have tried doing it the regular way but it becomes quite cumbersome, I am looking for help in terms of a better design or a Template as some single-cell (hopefully) formula which can incorporate the number of Debits,Amounts etc.
One very important thing is to also keep a track of the Balance not going below an "X" amount and that is Rs 1500/- as thats the Bank's Minimum Balance requirement..
The no of Installments are as mentioned below:
Debit--- Amount--- Start Month--- No of Installments
I Debit--- 750--- Jan-09--- 36
II Debit--- 584--- Feb-09--- 27
III Debit--- 1127--- Mar-09--- 60
View 14 Replies
View Related
Mar 31, 2014
I work in a HR department and I'm trying to create a spreadsheet to track the amount of extra-duty hours each worker has. For every extra hour they work today, they can use this hour next time.
Screen Shot 2014-03-31 at 9.25.22 pm.png
This is my current spreadsheetCredit hours means hours added to their 'account'.Debit means hours taken out.Hours will expire in X days if not utilised.Right now, each worker has their own spreadsheet in the same workbook.I have about 25 workers.Is this the best way to manage this?How can I create a 'bank account' system to track their hours?
hourstracking.xlsx
View 1 Replies
View Related
May 31, 2012
Is there a quicker way to match the amounts in debits with credits. for example the amount that reverses the transaction in the accrual account for the debit column is after 4 or 5 or sometimes 10 transactions in the credit column.
I have tried using conditional formatting - Highlight - duplicates, but it does not give the matching reversals and include any item(s) with duplicated values.
View 2 Replies
View Related
Oct 26, 2006
For the small database in my example workbook, I would like to apply credits earned at a later date to the oldest charges and create a "To Date" balance. After creating the "To Date" balance, I would like to select the smallest "To Date" balance as a way to identify the first charge that has no payment. Please see the attached for more clarity and additional information. The last column shows the desired results.
View 9 Replies
View Related
Jan 20, 2013
My task is to combine two large databases into one spreadsheet by extending the number of columns. The data from each database is 90% matching based on an identification number, however occasionally there are additional rows or unmatched identification numbers that need to be kept for analysis.
When this happens, there needs to be a blank row inserted to represent the missing data in the rest of the corresponding row.
I am having trouble finding a quick way to do this because I have approximately 12,000 rows (and columns up to DV when combined).
for example:
p1
data
data
data
data
p2
data
data
data
data
[Code] .....
needs to become:
p1
data
data
data
data
[Code] .....
I am guessing I will need a macro of sorts, So far I have made one column that tell me if the ID's are matching or not (1 or 0) and if they are not matching (0) I manually insert the rest of the row that is missing or make space for the duplicate data (which needs to remain).
View 5 Replies
View Related
Nov 19, 2009
programming 2 macros in a bank reconciliation sheet I am trying to create.
Basically the data consists of two sets: the ledger side and bank side. Both sides consists of multiple columns that include date, description and amount.
The following two situations can occur and which need to be covered with a macro.
Many to one matching
When we make payments to say 100 different suppliers at once, the ledger side will show 100 lines with the different amounts. However on the bank side it will only show one line with a total amount for the transaction. As the description on the ledger side is the same for all transactions done at the same time, it should be possible to have a macro add these lines and compare the total with entries on the bank side. Once a matching amount has been found, the macro should place an ‘x’ next to all entries on the ledger and bank side, in order to show that these transactions have been reconciled.
Partial cell matching
In this case we are only dealing with one line on both the ledger and bank side. The issue is that the description does not always perfectly match with one another. The ledger side might say ‘Brown Corp.’ and the bank side might say ‘Brown Corporation’. I want to come up with a macro that can recognize this partial match and still reconcile the lines by placing a ‘x’ to both the ledger and bank entry. The match however should be done with a combination of description, date and amount as several different bookings might be done for the same supplier in the same month.
View 12 Replies
View Related
Feb 27, 2014
I have two tables, Table1 one has only customer codes in it, and I have Table2 with plenty of customer codes and those customer name, surname, age, location...
And I want to match and copy each of those customer codes in Table 1 all the information which is on Table2
Table 1:
50025
50026
50086
Table 2: Considering that name, age and location is each in separate cell
50025JohnSecond25Location 1
60085EmilyThird 20Location 2
45454Wilhelm Fourth35Location 3
10000RoseFifth 60Location 4
50086JohnySixth 45Location 5
65501JacobSeventh18Location 6
50026Jackie Eighth22Location 7
And the outcame should be that it finds the value from Table1 in Table2 and copy's information in Table 1 like this:
50025 JohnSecond25Location 1
50026 Jackie Eighth22Location 7
50086 Johny Sixth 45Location 5
View 2 Replies
View Related
Feb 20, 2014
I have two worksheet "ABX" and "ACX" from which I want to find the matching data for specific columns B, D and E only.
Matching data will be in sheet "Match" and vice versa.
Data are present dynamically.
View 1 Replies
View Related
Jan 27, 2014
see attachment below for easier comprehension of what I need to be done. Note that I have around 20 subjects (2 in example) and about 15000 values per subject (5 per subject shown) Basically, I want matching rows to be aligned (see grey) and non-matching rows to be deleted, throughout the 20 or so subjects.
Exemple.xlsx
View 6 Replies
View Related
Jan 28, 2010
I have a cell (A1) that contains text.
That text will match a single value in a reference list (i.e. range of cells e.g. Z1:Z26).
When the match occurs (e.g. A1 = Z16), I want the format of the reference cell (fill color, text color) to apply to the cell of interest (A1).
The reference cells are manually formatted.
I know that I could create a long list of conditional format formulas instead of using the reference, but it seems that there must be some way to tell excel: if the cell value contents are equivalent, the formatting should be as well.
View 9 Replies
View Related
Dec 6, 2013
Attaching the excel (QOS- ARP- Applications.xlsx) - Formula I am trying in D27, "X" value is there in the row and the corresponding application (Header ) should be the return in D27.
View 1 Replies
View Related
Jan 10, 2009
I'm trying to get column Z4:Z14 to add automatically from information from Cells A15:Y17 under the correct name of each person. I have created an example sheet.
View 2 Replies
View Related
Apr 23, 2009
I have a long table with duplicating id numbers but different values which i need to match. Basically the initial state is like this:
ID1 | example 1 |
ID2 | example 2 |
ID1 | example 3 |
and what i need is this:
ID1 | example 1 | example 3|
ID2 | example 2 |
View 4 Replies
View Related
Feb 10, 2010
I need to produce three different formats of accounts data monthly. I attached a file with each accounts description in Columns A, E and G. Is there a way to match the data with those accounts name in a faster way than manual linking?
View 13 Replies
View Related
Jun 10, 2014
I do have a workbook has many worksheets. I would like to be able to match (or search) for first name and / or surname between one worksheet (Sheet,1, which has all the names I would like to match with the other worksheets) and the other sheets in the same workbook (the data is very sensitive). Is the possible way of doing this using VBA.
View 14 Replies
View Related
Feb 26, 2009
I have some text in $a$1.
In Column B I have all kinds of stuff, including the text in $a$1.
How do I find and select the matching cell in Column B?
View 6 Replies
View Related
May 6, 2009
I want to add these numbered steps onto the macro I already have, which I pasted below.
1. Starting in Row 2 in the worksheet named Report 1 look at contents in Column A then look at the contents in Column C, if the contents in Column C do not match exactly the contents in Column A then copy the contents in the range column C:E from that row till the last row in Column C:E.
2. Now we will have a range that is made up of cells from C:E. I would like to move that range down a row at a time, until the first cell in Column C from the range Column C:E matches exactly the contents in Column A. Then I would like to continue this pattern for every row in the worksheet.
View 2 Replies
View Related
Jul 30, 2009
i have cell N1 witch is a number example "8832"
cell O1 is text example "state street"
now i have A1 thur A10 as an address "number"
now E1 thur E10 is street address "text"
i need to take n1 match it to A1-A10
then take that row number and see if E1 of that row numbers matches
O1 if it does that that row numer and get g of that number if not keep checking to i find a match
View 6 Replies
View Related
Sep 11, 2009
I have two spreadsheets, both contain matchable data. First one with amounts. Next one hs customer name, period and amount columns.
View 3 Replies
View Related
Oct 23, 2009
I have a list of company names and I need to find the duplicates. Of course pivot table finds the exact matches, but how do I find duplicates when only 1 character is off. Like punctuation or "s" or a mistype of a single character?
Example:
Jo Blo inc
Jo Blo inc.
Jo Blo inc
Jo Blo icn
Joe Blo inc
Jo Blo inc
View 4 Replies
View Related
Dec 21, 2009
If the following macro matchs two ID numbers from column B in column C it shoud highlight them green.
Column C may has multiple entries of column B, however the macro i have written appears to only match the first ID number found in column C from B.
I think this may have something to do with my loop, can anyone suggest a solution?
View 9 Replies
View Related
Jan 27, 2006
getting correct formula and normally there are people much smarter then I in the
discussion group. What I have is a workbook with 4 worksheets and on on of the worksheets I have a layout like this. (sheet 1)
A B C
1 Name Origin Driver #
2 Mrs Soandso 125 anywhere
3 Mr. Someone 327 anywhere
Drver numbers are blank
On another worksheet(sheet 3) I have a list of subscriptions (I.e drivers
numbers already filled in) I have this set up as a list.
A B C
1 Name Origin Driver #
2 Mrs Soandso 125 anywhere 37
3 Mr. Someone 327 anywhere 39
What I am looking for is a way to pull the drivers numbers from sheet3 into sheet1. so basically in sheet 1 if a1 and b1 match the name + origin in sheet 3 it will pull the driver number from that row into c1 sheet1 . If nothing matches then it should leave it blank. The data in sheet 1 changes everyday for me as I get a manifest on who to deliver wheels on wheels to, my problem is while I know all the correct drivers go to what people, I can never have a day off as someone filling in for me has to go through 400 addresses and look each one up and put coresponding drivers number into it.
View 13 Replies
View Related
Oct 22, 2007
I have to find the maximum value of a column (J16:J115) and refer to the name in column A corresponding with the number found... everything works perfectly with :
=INDEX(A16:A115;MATCH(LARGE(J16:J115;1);J16:J115;0))
But when there are two maximum values it only gives me the first one... is there a way I can get both (or more) names in different cells ?
View 9 Replies
View Related
Nov 29, 2012
I am trying to find a function which will do the trial and error bit for me. I have for example 5 numbers an want to fit the answer to the puzzle.
For instance
3 5 X
2 3 Y
1 2 4
Is there a way to find "X"? Then as a result "Y". I can't just go back up the equation as it is not hat simple. Is there a way excel will try an find what fits?
View 1 Replies
View Related
Oct 3, 2013
I need to match the first column with the second column, and if is the same value, copy the value in third column to the forth column.
View 1 Replies
View Related
Sep 20, 2007
i have a worksheet which i use to maintain complaints of service stations.
i need to match values in 4 columns if they are the same the fifth column should display a text.
since i log compliants, if the same complaint reoccurs again the fifth column should say return job.
View 9 Replies
View Related
Nov 9, 2007
I have 10 separate columns with an unlimited (but right now 354 rows) and I would like to search for two matching numbers going row by row, giving me the answer in one field that would say how many rows the two matching numbers are in, is there a way?
View 9 Replies
View Related
May 19, 2008
I need to have a string comparison done in a macro,
I have a files with names similar to "TEVT_GURUPRASAD_WEEK08" and a array of strings having names "Guruprasad,AnilKumar,....etc." I Need to match the name in the array and the name in the filename.
View 9 Replies
View Related
