i have two work sheets in one spreadsheet. they are from different systems
one feed to the second to create refunds. I have been forced to cut and
paste both sheets to a new worksheet and then sort by amount and then delete
like amounts to find refunds not carried over to the other system. Is there
an easier way to match and then delete matching amounts, therefore saving me
Similar Excel Video Tutorials
Keyboard Shortcut Tricks.
- Learn keyboard shortcuts for Row & Column Tricks: Hide, Delete, Insert, Select.
1) Keyboard shortcut for Inserting a Column is: Alt + I ...
Helpful Excel Macros
Delete Hidden Worksheets
- This macro will delete all hidden worksheets within a workbook. When you run this macro a warning window will pop up for
I'm looking for a Macro that will allow me to take information on two different sheets in a workbook, and delete out lines that match based on a "concatenate" formula. Each sheet has slightly different outputs as they are reports from two different systems. Based on the attached example, I'm looking for something that will allow the following:
1) Sys 1 RPT will concatenate columns A, E, F.
2) Sys 2 RPT will concatenate columns A, D, E.
The Marco would then go through the two sheets and delete out lines in which rows with matching concatenated data string matches leaving each sheet with only rows that need to be looked at in more detail to recon and find out why they are not on the other report.
I hope this makes sense, and I'm open to other methods, if there is something easier to work with.
I have a worksheet with 2,248 different dollar amounts from a checking statement that include Credits and Debits. I have been able to import the data into a workheet containing three columns (date, amount, credit/debit). What I need to do is weed out any amounts that do not have a match so I can research them and see where the offset is. Is there any formula or VBA coding that can do something like this? Get the matched amounts and move them to another worksheet or move the unmatched amounts to another worksheet.So far I have been able to sort by descending dollar amounts. This helps a bit but have to go through the whole spreadsheet and copy/paste and amounts that do not have a match, which is pretty long. If I am not being clear enough, please let me know and I will try my best to make things clearer.
I have two lists that needs to be matched every month. one is;given loans the other one is; gurantee letters for that amounts of loans. As an example; when it's given $102 loan to a customer, they shall bring a $102 gurantee letter from a bank. We have the loan amounts and letter amounts in two different lists. Matching them manually takes ages.. I need to find an easier way..
I can't use a vlookup formula, because there are 2500 items in a sheet and there can be several times the same amount. I have two main constraints; the currency of amounts should be same. Secondly; the date of the letter and date of loan should be very close (mostly one day after, but it s ok even if they are on the same month) Seldom if ever, same amounts are hitting the system on the same month. But it's ok, i can correct them manually..
Credit List :
ID Currency Amounts Date
200 CHF 885,00 06.01.2008
836 CHF 8833,00 24.05.2007
1 EUR 451944,0 30.07.2007
3 EUR 9541,00 03.01.2008
6 EUR 40200,00 07.08.2007
7 EUR 74955,00 01.08.1990
ID Currency Amount Date
1 TRY 7495,00 02.08.1990
2 TRY 26,00 24.08.1990
3 EUR 9541,00 01.08.1990
4 TRY 3763,00 17.09.1996
5 USD 41205,00 18.03.1997
6 EUR 35279,00 25.08.1997
7 CHF 8833,00 25.05.2007
8 TRY 4200,00 31.12.1998
At the list above only the CHF highlated in RED; in the 2 and 7th rows of the lists are matching, other amounts are not matching due to differences in currencies or dates..
I know it s pretty complicated, but it s a pain in the as. to match all of them manually.. I tried sorting and grouping acc. to currencies or dates and also changing letter amounts to negative values and summing up to 0.. nothing works.. Can anyone help???
Looking forward for aid...
I have a sheet of data that I need to find which row has matching data in 4 columns so that I can delete the matching row. I am using a helper column to find the matching row and then autosort to narrow down to the matching row and then deleting the visible rows. I thought there should be an easier way to do this.
I was playing with sumproduct, but I don't know how to get the row number.
BTW I am doing this all through VBA.
I have a sumproduct formula that compares names against one list to another, and if the date in another column is equal to another it will then return the value in another column:
However, what I want to do is some validation, i.e. if the value in column V is blank then it should display a blank... I've tried everything i could think of, IF statement, ISBLANK statments... everything still returns a 0, meaning i have to go in manually and delete those that should be blank.
Any ideas of how i can get the sumproduct to display blank or n/a if the value in V is not a number?
This is my first post to this forum, but I just couldn't figure out this Macro and I was hoping someone could help.
I have two sheets in one workbook that I need a macro to look for matching rows of information between the two and delete a row in Sheet 1 if a match is found. I have attached the two sheets here so feel free to take a look.
We need to loop through each row in Sheet 1 matching 4 criteria (Due Date, Part Number (also known as AKA), PO, and Qty) from a row in Sheet 2. If all four match, delete the row in Sheet 1 and move to the next. If all four do not match any rows in Sheet 2, move to the next row without deleting in Sheet 1. Also, if there is a row in Sheet 2 that is not in Sheet 1 - it should be turned red.
I hope that makes sense - it's a pretty simple VBA I think, but I am just not that skilled in VBA yet to get it to work out so I was hoping for some guidance from the more experienced...
I am trying to learn array formulas and can't seem to get a formula to work.
I have two worksheets one has daily payments and the second has refunds. What I want to do is look at if an account number is on the refund page and on the daily payment page, and if the service date is the same on both worksheets, I want to get the amount of the payment that was made. This is what I tried last:
=IF(REFUNDS!B6='DAILY '!B:B,IF(REFUNDS!N6='DAILY '!N:N,'DAILY '!F:F)) and I am getting an error #NUM!
Can someone help please?
I am currently faced with comparing two separate worksheets of information built by two separate individuals. I am comparing claimed invoice amounts and actual system invoice amounts.
A pivot table would seem a logical answer, since the data is condusive to such a report, but the data from the two different sheets does not match up exactly. I am comparing 9 columns of data, and need to show every colum, in a color code for easier reference.
e.g. [b]company a claim amount internal invoice amount
But, underneath Company A, I would have to show the multiple invoices for Company A, and data from that worksheet does not exactly match up with the data from the Invoice amounts, leaving me with a cut & paste nightmare.
any suggestions on an easier worksheet design? Would Combo Boxes be a soultion?
What I am trying to do is pull matching records from two spreadsheets and place them on a third spreadsheet and tell the difference between the amounts and amount remaining within Excel.
Excel Spreadsheet 1:
Supplier Name | Invoice # | Date | Amt Remaining | Current | Unique Identifier
Excel Spreadsheet 2:
Amount | Remaining Amt | Unique Identifier
Excel Spreadsheet 3: (Unique Identifier as the key that pulls all matching records from both sheets)
Supplier Name | Invoice # | Date | Amt Remaining(sht1) | Current(sht1) Amount (sht2) | Remaining Amt(sht2)| Unique Identifier | Difference Between Amounts (New Column)
PLEASE HELP ASAP!!!!!!!!!! I was giving this information, but I can't get it to work. Any suggestions?
I need to figure out the best way of extracting certain parts of information from a string and putting them into separate columns. For example I have:
A1: abby8004 - 1 refunds
A2: kristen8036 - 3 refunds
Need to have:
The extraction for column B is easy enough, but I'm having a difficult time with the number of refunds. I tried the formula on Excelisfun #489 magic trick but with no success. Perhaps it's because I have spaces in the column A cells or an operator error on my part. Help please!
I've been given the task to produce a spreadsheet to track the amount of refunds given to customers for a team of people. The general formatting i can sort out and normally i can work out formulae but i'm stuck today
each agent on the team has their own worksheet to enter the details and that is mapped across to the main sheet for easy viewing.
I have a column "Correct?" with drop down list in each cell with a yes or no option.
I need it to calculate the number of refunds that are correct, so far i have this formula . .. .
which means i've got a hidden column of 1s and 0s but i cannot for the life of me get a total of that column!!!
Any help greatly appreciated
Please note, I have read all the subjects named reconciliation and it was very good but they wouldn't help me do what I need.
I have to deal with long number of lines and I have to match them to similar lines from other companies, both lines have dates and amounts, sometimes both have Invoice number or one have invoice and the other has a description that has the invoice number.
I want to ask for help and I "tried" to make the macro idea useful in all weird cases, the charts I get are very complicated and random and of course I will make sure to arrange them as the example I am attaching, making sure that column 1,2,3,4 are the same always.
I get two types of statements;
The First Case:
what I am looking for is a way to send the matching results to a new page were they descend from 100% matching to less matching, for example the first 10 lines will be 5 lines matches to 5 lines were the invoice number from my company (DOC#) was found in the description line of the supplier sheet and the date & amount matches also.
Then another 10 lines were matching happened in invoice and DATE category only
Then another 10 lines were matching happened in invoice and AMOUNT category only.
Then another 10 lines were matching happened in DATE and AMOUNT only.
The non-matching lines can be left in the original sheet.
Then matching for Debit memos & credit memos based on DATE and AMOUNT
non-matching can be left
Then matching for Payment based on AMOUNT (first amount with first amount if possible)
non-matching can be left
The Second case:
The RECORD# in supplier sheet is sometimes DOC# number, so how do I do change the macro to search in this column instead of DESCRIPTION.
Since I have never done coding am not sure if this is the best way to think about it.
The number of columns is not fixed and different in the 2 sheets and I want to cut the whole row.
Columns 1,2,3,4 are fixed with different name for column 3 in Supplier [RECORD# (case 1) or DOC# (case 2)]
I hope you get the idea and PLEASE tell me if you find a flow in this idea ????
I hope its easier done than said....
Please check the attched I have 2007 but did this on 2003
Theres a formula that I cannot think of to figure this problem out, so any help is much appreciated.
I need a formula that will look up a cell to get a figure from, but there is three of the same name (sometimes more, depending on different products sold) i.e. "Dept Total" (shown below & attached for easier reading)
A B C D
1 Chocolate Sales Refunds Total
2 Snickers 1 0 1
3 Mars 1 0 1
4 Twix 1 0 1
5 Crunchie 1 0 1
6 Beuno 1 1 0
7 Dept Total: 5 1 4
9 Fruit Sales Refunds Total
10 Apple 1 0 1
11 Pears 1 0 1
12 Dept Total : 2 0 2
14 Drinks Sales Refunds Total
15 Coke 2 1 1
16 Pepsi 1 0 1
17 Dept Total: 3 1 2
What I want this formula to do, is find the word "Fruit" (Currently A9, but may change when info gets pulled from web), then go to the next available "Dept Total" word (currenty A12, again the position of this may change) underneath the word Fruit and then go across to find the total (this is the figure I require) and if it can't find it then to show the value as 0.
Please note that when the information gets pulled from a website, the information varies on rows as different products may be sold but the columns always stays the same (A=product, B=Sales, C=Refunds and D=Total)
Hope this makes sense....
I've tried looking up on Excel help but I haven't been able to get my head around it..... thanks again!
I have debits in Column F and Credits in Column G
Now what I want is
1) A VB Code that will automatecally match two amounts in column F & G based on the negative and positive amount. These amounts could be in F45 the match in G195 or F58 & match in G88. Please be advised that the columns are not always F & G.
2) After amounts in two columns are matched a VB Code that will cut matched rows from "sheet1" and paste to "sheet2" and delete the blank rows from "sheet1"
Hi all, I have a statement that I need to sort every month in the following way, columns A, B, C, D and E represent entries from a general ledger. Columns G and H represent entries from a bank statement. I have to sort the sheet descending, according to the amounts in columns E and H, and align matching amounts. If no match is found then an empty row must be inserted next to that amount. It is difficult for me to properly explain the process so I have attached an example file showing what it looks like before and after sorting. Is there anybody who can help me with a macro that will do this for me.
Good afternoon everyone,
I need some help with matching information from 1 sheet to another, the list i have attached is a small portion. I would gladly just sort and copy and paste but nothing will match up correctly and everything would be shifted way off. the attached explains it much easier than i can on here.
Any help is greatly welcome and appreciated.
I am having trouble with a spreadsheet I got at work. I have two data sets one made up of new data and one of old data. I am trying to merge some of the data, but can't seem to make a formula that works. Below I have an example.
0 A B C D
1 Z 9
2 Y 8
3 X 7
4 W 6
0 A B C D
2 Y 8
3 X 7
In the above example, pretend that the letters in column A - - "Z, Y, X and W" - - are all last names. The numbers in column B - - "9, 8,7 and 6" are donation amounts.
As you can see, there are amounts that were already filled out on the old data set. However, now we have new data and I want to merge it together with the old data.
What I need is a formula that will search both data sets for a matching last name (columns A). Then, once a match is found, it will copy the donation amount from the new data set to the old data set (columns B).
So the solution in the above example would identify that Last name W is on both sheets and will provide the missing donation amount ("6") to the old data.
What I have been trying is =if('New Set'!A1:A4='Old Set'!A1:A4,'New Set'!B1:B4). I know this is wrong, but I think I'm on the right track.
Hi everyone, hope your summer is going well.
I have a workbook, where it is possible that at any given time I will add additional sheets.
So right now, I have 5 sheets:
I want to put the total of A35 of all sheets on the principle amounts cell F10 without including Principle Amounts. Then when I add new sheets, it will automatically begin suming the new sheets as well.
Thanks in advance
I am relatively new to excel, and blundering my way around! I will try and explain my problem as best I can.
I am treasuer of a club. I have a workbook with individal sheets for each member. Every week when they pay their money, which is broken down into 3 different amounts, I put the amounts into columns, ie
Col A - date (week beginning, which will run to 52 rows over the year)
Col B - total paid
Col C - amount of admission fee
Col D - amount towards tea/snacks
Col E - amount towards outings fund
These amounts are put into separate bank accounts, hence the reason they have to be broken down. When it comes time to do the banking, I want to create a 'summary' sheet and would like the figures in columns B,C,D & E to transfer automatically to it. The format for the summary sheet I have set up as follows
Col A - member's name
Col B - total paid
Col C - admission fee
Col D - tea/snacks
Col E - outings
I can then get excel to total up Cols B-E to give me the totals for banking. I like the summary sheet this way as it gives a quick view of what everybody has paid, without checking through all the tabs (which could be 100 or more). I know I can copy and paste these amounts into the summary sheet, but this would take a lot of time switching from each sheet to the summary all the time. Is there an easy way I can transfer the amounts to the summary sheet? Also, the amounts will be moving down a row each week, ie
w/b 1 Jan - amounts are in row 6
w/b 7 Jan - amounts are in row 7
I am going round in circles with all different ideas but can't come up with anything. As I said, I am a novice at this!
I have columns amountA and amountB in two different sheets. I am matching them. Some amounts are same. So excel showing row positions of firstly match of a set of duplicate amts. How can I see different row positions for different set of duplicates?
Hi there this is my first post here so i am not sure if this is the correct section to post this problem in.
My problem is . . . I have an excel spread sheet conaining 2 sheets.
The first sheet contains names and amounts and the second sheet contains names and amounts.
What i would like to do is use a formula that if the names and amounts match on both sheets they are removed and the ones that dont match are left or if easier the ones with the names and amounts dont match are put in a seperate sheet on its own.
hope this makes sense and i hope someone will be able to point me in the right direction.
I asked the mod to delete my previous thread because I now have all the info and actual records (i wont be doing the actual rec im just making the process easier)
I've attached a WS
the 2nd tab is the output of checks written per the general ledger (gl) blank amounts are because the check was voided and the amount was entered as a debit.
-- I need to be able to delete rows for voided checks which is simply matching check numbers and deleting all but the unique numbers.
the 3rd tab is the output we get from the bank for cleared checks. one problem we had was that is ads an additional 3 digits to the front of the check number (for division ID) which have to be removed. I was able to get a function from VBA newbie which does this but if it were possible to simply make a CMD button which created a new column and corrected the entreries using that function that would be ideal as the person doing the rec's does not have a lot of expertise.
--- I need to combine this with the G/L output and remove cleared checks which again is simply removing all matching check numbers, leaving the records with unique numbers.
Idealy this would all be done with one cmd button but I could see it broken up into the following
1) person pastes the cleared checks from the bank, clicks a CMD which creates a column between A & B, and then fixes the check numbers
2) person then pastes that list beneath G/L output and then clicks button which eliminates all the voided and cleared checks (matching check numbers) and leaves the unique records giving her a list of all outstanding checks.
any and all help very much appreciated.
I'm trying to make an Excel spreadsheet to match movements on my bank account with what I've registered in my system, but the problem is if an amount is registered twice or more in the bank it is shown as matched, even though it is only registered once in my system.
Here is a small preview of my excel sheet:
---A ----- -- ----B---- --- ----C----
My Bank ---- My System -- My bank Vs. My system
$1.000,00 -- $1.000,00 ---- Match
$1.000,00 -- ----------- -- Match*
$1.000,00 -- ----------- -- Match*
*I've received $1000 three times in my bank, but it is seen as matched, eave though the $1000 is only registered once in my system
Does anyone know how to make a formula that will show these amounts as unmatched?
I'm using the fooling formula in column C:
Let say I have four columns.
col A has some names and in col B there respective amounts. Col C has names and col D with there respective amounts. I like to create results in col E if names matches in col A & C, name should appear one time and total of there amount in col F. If name did not match in either col A or C, still appears in col E walong with there amounts as a single line items.
For example if col A & C has
the results should be:
sothe result in col E will be:
John (only one time as in both col)
Sarah (not in col C)
Nick (not in col A)
Dana (not in col C)
Smith (only one time as in both col)
Please I need some one help desperatly.
This code sorts the result by Column A which has the values from d11 on the other sheets. It puts all the rows with no data in that column at the end. I want to delete all the rows that are blank in Column A. I assume I need a loop for that, but am not sure how to do it.
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim WS As Worksheet
Dim sht As Worksheet
Dim strtRow As Long
Dim rng As Range
strtRow = 3 'set it to the row where you want to start it from
Set sht = Sheets("Refunds Due")
Set rng = sht.Range("A1")
'clearing contents here :
For Each WS In Worksheets
If WS.Visible = xlSheetVisible And WS.Tab.ColorIndex <> 1 Then
If WS.Name <> "Resident List by Unit" And WS.Name <> "Refunds Due" Then
rng.Offset(strtRow, 0).PasteSpecial xlPasteValues
rng.Offset(strtRow, 1).PasteSpecial xlPasteValues
rng.Offset(strtRow, 2).PasteSpecial xlPasteValues
rng.Offset(strtRow, 3).PasteSpecial xlPasteValues
strtRow = strtRow + 1
ActiveCell.FormulaR1C1 = "CONTRACT ENDED"
ActiveCell.FormulaR1C1 = "RESIDENT"
ActiveCell.FormulaR1C1 = "UNIT"
ActiveCell.FormulaR1C1 = "AMOUNT DUE"
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2") _
, Order2:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:=False _
, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:= _
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Application.ScreenUpdating = True