Macro: Compare The Amount & Paste OK
Feb 24, 2010
Hi all,
I have a workbook with 2 sheets: "dan" and "resources"
On the "Dan" sheet, in column B there is a list of names and in column column C, a corresponding dollar amount.
On the "resource sheet" in column B is a subset of the names found on in column B of the Dan sheet. On the Resourse sheet, there is a corresponding dollar amount in column c also.
I would like a macro to compare the dollar amounts to ensure they match. If they match, I would like to place an "OK" in column Q on the appropriate row of the Dan sheet. If the amounts do not match, place an "X" in column Q of the appropriate row on the dan sheet. If the name on the Dan sheet is not one that appears on the Resource sheet, then place an "N/A" in column Q of the appropriate row on the Dan sheet.
View 9 Replies
ADVERTISEMENT
Mar 3, 2010
I want to create a macro that will copy and paste a couple seperate collumns but the problem is that each month, and each invoice, contain a different amount of rows so I can't "record" a macro. Lets say I start on A4 which is the heading of Column D..below it are a bunch of records at the end of the records is a space. I need it to stop there. Then do the same for Column G, I, etc. I would like it to copy each column and paste in a new workbook.
View 9 Replies
View Related
Nov 20, 2012
For the below macro.
ABCDG
CodeTotalBaseNetA1 = 20%
A110083.3316.67A2 = 10%
A2350318.1831.82A3 = 7%
A3220205.6114.39A0 = 20%
A015015030
A1 is the code, take the total(B) and calculate the base(C) on the percentage given in the G, net = B-C. for A2 and A3 it is the same process.
For A0 the total (B) is equal to Base(C) and calculate the net on the given percentage in G.
View 1 Replies
View Related
Jan 28, 2009
I need a formula to calculate the amount due based on cumulative sales once a breakpoint amount is reached.
Example:
Breakpoint:
cum sales are > 500 pay at 3%
cum sales are >1,000 pay at 2%
month/ sales/ cumul sales/ amount due
jan/ 100.00/ 100.00/ 0
feb/ 600.00/ 700.00/ 6.00
mar/ 600.00/ 1,300.00/ 18.00
and so on...until the end of year.
I tried using an if formula by could not get it to work.
View 9 Replies
View Related
Jun 5, 2008
Im trying to set up an active running inventory sheet where: (A)the progressive daily sheet cells reference back to the corresponding master sheet cells fluctuating the master values, (B) the same progressive daily sheet cells reference back to a cummulative totals-cell based on whether I added or subtracted inventory. I want to make a copy of the blank "sheet 2" with all of the formulas and move it to the end of the workbook each day and enter new values which will reference back to the master sheet so that I can click on a date sheet and see an individual day's values or click on the master sheet to see the fluctuating inventory on-hand and the cummulative +/- totals of all days combined. I've got a couple hundred individual cells to reference. I've tried and tried but I can't make it work. Heres what I need to do:
I need to reference individual cells from "sheet 2,3,etc" back to a corresponding cell in a master sheet. But I need the values in each cell in "sheet 2,3,ETC" to increase or decrease the corresponding cell values in the master sheet. For example: If the value in the master sheet B5 is 200. Then in sheet 2, I enter +50 in B5, I need the master sheet cell B5 to increase by 50 to 250. I also need a way to decrease the cell value in the master sheet B5 if I enter a negative value -50 in sheet 2 B5. I also want to know if I can reference the same cell values entered in "sheet 2,3,etc cell B5" back to totals columns C5 for adding inventory or D5 for subtracting inventory in the master sheet where the master totals columns would reflect cummulative totals added or subtracted. For example: if the value in sheet 2 B5 is +50, then the value in Master sheet C5 would add 50 to a progressive total. But if the value in sheet 2 B5 is -50 then the value in master sheet D5 would add -50 to a progressive total.
View 4 Replies
View Related
Jun 3, 2014
I would like to make a macro that compares two worksheets, highlights the differences, and paste the whole row in a third worksheet if a difference is found.
I managed to highlight the difference between two sheets using the following code. But how to modify it to paste the differences in a third sheet...
Code:
Sub Compare()
CompareWorksheets Worksheets("Sheet1"), Worksheets("Sheet2")
End Sub
Code:
Sub CompareWorksheets(ws1 As Worksheet, ws2 As Worksheet)
Dim diffB As Boolean
Dim r As Long, c As Integer
Dim lr1 As Long, lr2 As Long, lc1 As Integer, lc2 As Integer
Dim maxR As Long, maxC As Integer, cf1 As String, cf2 As String
[Code] ......
View 4 Replies
View Related
May 28, 2014
I have a workbook with 2 sheets. Both the sheet contains Column "Name,Avg,Max". Compare both the sheets and paste the Avg values in Sheet3(Avg) and Sheet4(Max). I have attached the sample file below.
comparison.xlsx‎
View 8 Replies
View Related
Jul 26, 2009
I have one column of names in excel. The column may contain more than one row with the same name but these rows with the same name will all be grouped together. This is an exampe (each name represents a row in column A):
ColumnAColumnB
andrewData
julieData
julieData
julieData
jonathanData
jonathanData
What I want to do is copy the rows with the same information, e,g, the rows with 'julie' above, paste them into a new spreadsheet and email this spreadsheet to specific email addresses and then do the same for 'jonathan'.
I can work out how to send an email using VBA but I am really stuck as to how to go through the rows and send the email in discrete 'chunks'. I have tried using a for next loop, looping through the rows and copying/pasting rows that are the same as the previous one into a new spreadsheet but this does it one row at a time.
If I include the instruction to email the spreadsheet within the loop this would also email the new spreadsheet one row of information at a time, i.e. three emails for 'julie' each containing a spreadsheet with one row of information on it, rather than one email containing one spreadsheet with all three rows on it.
View 4 Replies
View Related
Jun 2, 2009
I'm alittle new to excel and MrExcel website. I have a spreadsheet that im trying to get a formula to work as below. If anyone knows how to get this to work please help! Thank you.
My spreasheet has the following:
Sheet1:
Column B Column G
1 test 1
2 center 2
3 school 4
Sheet2:
Column B Column G
1 work blank
2 school blank
3 home blank
I'm trying to find the value from sheet2 cell B2 (school) in sheet1 column B and then when it finds that, I need it to copy the data from the Corresponding sheet1 Column G cell to sheet2 Column G cell.
View 9 Replies
View Related
Sep 11, 2013
Here's how my daily report is currently set up:
ExternalInternalDifferences
123000123000
234564234564
345456345456
456567378987
565456456567
654547524564
788879565456
865478654547
745654
788879
865478
The External column contains data provided by an external vendor. The Internal column contains data compiled internally. The data in the Internal column will always contain data duplicate to the External column, but will also contain unique data not present in the External column. I would normally cut out the data contained in Internal column that is not duplicated in the External column, and paste it into the Differences column, and move the remaining cells in the Internal column up. The result would be the External column data and Internal column data would align by row, and the data unique to the Internal column is segregated to the Differences column, like so:
ExternalInternalDifferences
123000123000378987
234564234564524564
345456345456745654
456567456567
565456565456
654547654547
788879788879
865478865478
While not a difficult task for 8 rows of data, the actual daily report contains over 1000 rows, on average. I would like to set up a template workbook, where I can simply open the template workbook, paste the data into both the External and Internal columns, then have the values that are unique to the Internal column extracted from the Internal column and inserted into the Differences column.
The end product would be used daily, with differing amounts of data (from 100 rows to 10,000 rows).
View 8 Replies
View Related
May 4, 2006
What I am trying to do here is to compare 2 lists in 2 different files, and when there is a match, then copy and paste the related cells of the matching name. Sorry if this sounds messy, perhaps the sample file I have attached can explain better.
Every month I get a new file in the format of “Data Source” sheet where the list of banks in column A and the figures in column M, AA and AB might change from month to month. For the sake of convenience, I put the source data as a different sheet instead of different file here.
I have an existing report template in the format of “Final report” sheet where basically I copy and paste the relevant cells according to the name of the banks.
I don’t think I can use Vlookup because the cells that I want to extract are not right beside the search criteria. If I’m wrong please correct me.
Anyway, assuming a macro is needed for this, I am wondering if I can create a macro, where it can search the list of banks in column A in “Data Source” sheet based on the list in column A in “Final Report” sheet, then copy the correct cells from column M, AA and AB and then paste them into the correct cells in columns B, E and H in “Final Report” worksheet?
Note that not all the banks in the “Final Report” sheet are in the “Data Source”, so for this example, row 4 for ABN Bank should remain blank after the search because it is not listed in the “Data Source”. The Data Source List might also change over time.
There is also this problem of the bank names from the “Data Source” sheet not being exactly the same as the existing list in “Final Report”. For example in this file, ANZ Bank in the other sheet have all the extra stuff behind, but we know it is the same bank.
View 9 Replies
View Related
Aug 4, 2008
I have 1 workbook, with 3 sheets. Sheet1 (EVER) has 3000+ rows and 12 columns of customer information. This sheet is for all customers who have ever placed an order. Sheet2 (06-07) has 1500+ rows and 12 columns of customer information. This sheet has all customers who have placed an order in the last 2 years. Sheet3 has 1 row, which consists of the column titles (12 columns) that are on Sheet1 and Sheet2.
I need to put all customers that are on Sheet1, but not on Sheet2 in Sheet3. I have tried VLookup; advanced Filter and a number of codes in the last 3 days and have not been able to figure this out.
View 6 Replies
View Related
Jul 23, 2014
I have a table that looks like:
7300
3
7301
2
7302
5
And I am trying to make a macro that will go concatenate the first 3 columns of all rows in worksheet 7300, the first two columns of all rows in worksheets 7301, etc, and insert them into column A of each corresponding worksheet.
View 7 Replies
View Related
Jul 17, 2008
I have a large amount of data in a single spread sheet. Each row has a branch number on it, there are multiple branch numbers. Each branch number is located in Column A. I want to separate this sheet and put all of the branch numbers into their own sheet. how to do this without manually copying and pasting?
View 9 Replies
View Related
Nov 25, 2008
I have an excel sheet and I want a Msg pop up to show a certain amount from a specific cell. So if the cell shows $100. I want to a do a macro that if I hold control + shift + a I will get a pop up saying "Your amount is (value of the cell).
I can only get it to work if I want the vale from an active cell.
MsgBox "The value in the active cell is " & ActiveCell.Value & "."
View 9 Replies
View Related
Mar 8, 2005
I am using the close.workbook command in VBA to close a workbook I opened to copy data from. I am getting a message that says "You have a large amount of data on the clipboard. Do you want it available?" What command do I use in VBA say no to this message?
View 5 Replies
View Related
May 5, 2014
Phasing Design.xlsx
We have a requirement to spread/phase amounts over multiple columns (representing months) using a weighting (represents working days per month).
We will calculate the weighting result in excel first (represented by a % per month), so the macro will be more of an allocation of row amount against the equivalent column %.
So far i have the following code:
VB:
Sub phasing()
Dim SourceA As Range
Dim Weight_ResA As Range
Dim TargA As Range
Dim i As Long
[Code] .....
View 2 Replies
View Related
Jan 29, 2014
I would like to implement specific cell ranges from two specific worksheets each within 33 workbooks (which all have several tabs) into a summary page in a separate workbook.
The cell ranges are going across my spreadsheet in rows and I would like for them to transpose into a columns depending on the data which I have separated by catergory on the summary page. They are all on the same location in each workbook which is separated by country. The cell ranges are E26:P37 and I would like to transpose them and have them put below eachother without overwriting for my format on the summary page, how I can put this together in a macro?
View 1 Replies
View Related
Oct 1, 2012
I have one workbook that needs two macros.
On the "Complete Backlog" tab of my workbook, I want users to enter in the requested information based on the column header. Then I would like a Macro attached to a button that says "Refresh" that the user would click after they have entered in all of the information. This macro should look in Column M (WIP Status) and if any of the cells say "Close", it should Cut the entire row from the spreadsheet(Ex. A2:M2) and Paste it into the speadsheet titled "Closed Jobs".
This is so that as jobs are closed/finished, they are removed and stored on a separate sheet. The items would have to be pasted so that it pastes into the next available row - not just on top of each other.
I also need another macro that i can put into a button that doesn't "delete" a row from the sheet, but just copies over to another sheet - so that there are two instances in the workbook.
If would look something like: If a cell in "Column G / Director" of the "Complete Backlog" speadsheet is equal to "Snodgress" then copy columns A-L of the same row to the spreadsheet titled "Snodgress" - of course skipping down the rows to the next blank row.
.....is equal to "Herr" copy row to "Herr" spreadsheet.
....is equal to "McCormick" copy row to "McCormick" spreadsheet.
and so on.
View 2 Replies
View Related
Oct 26, 2007
I have a macro that does lots of cutting and pasting, and to make sure it can run without interference from my normal work, I did add the line Application.Interactive = False. But seems while I am cutting and pasting in other windows while the macro runs, sometimes it crashes on the paste part. Is the macro and the Windows Operating system sharing the clipboard?
FYI, Windows XP professional, Excel2000.
View 9 Replies
View Related
Apr 28, 2012
I am trying to subtract an amount from a cell until it reaches 0, then move and subtract from the next amount, and so on.
AmountFixed BudgetedResult53.50Subtracted 5 tell it hit zero1.30Subtracted remaining amount until zero43.8Subtracted remaining amount until zero3.8 was left over without hitting zero is good. Is there formula for this?
View 6 Replies
View Related
Mar 15, 2007
In a financial environment we have a calculator which uses iteration to allow for a cost being added to loan amount where the cost is based on the total loan amount. Iteration is set to 100 iterations with max change .001
On one PC the first time the calculator is opened it gives a particular (incorrect) result. If the input cells are cleared and the data re-entered, it gives the correct result. This only happens on one particular PC. Is there some other setting , other than the iteration setting, that would cause this?
View 5 Replies
View Related
Aug 29, 2012
I have two worksheets.
Column A on both sheets have an ID number which is unique to clients. Sheet one will only have a client ID once but sheet two may have the same ID multiple times
I need to compare both sheets Column A and if it finds a match, then paste the cell value on column E sheet two, into column K on sheet 1.
sheet 1:
A ..........................................K
Client ID............................. name
Sheet 2:
A ...........................................E
Client ID............................. name
View 3 Replies
View Related
Jul 10, 2006
I want this macro to find in this case "406" in column A which is at the very end of the last block of 160 rows of information.
I then want it to move up 159 rows and copy 160 rows of information underneath the last block of information.
i.e.go to A5280 , then go to a5121, copy rows 5121:5280 to 5281.
It falls over on the very last line of code I can see A5281 selected but it won't paste....
View 9 Replies
View Related
Feb 15, 2014
I have been using this macro to separate lots of rows with a certain amount of blank rows.
Example:
Code:
Sub test()
Dim j As Long, r As Range
j = InputBox("type the number of rows to be insered")
Set r = Range("A2")
[Code] .......
I trying to change it so that, instead of inserting how ever many blank rows, it just copys the row above.
Example (Row A to C)
I have been messing around with macro recorder and i could select each row and paste it into the blank rows, but from researching on the internet selecting and pasting data seems to be a waste of resources (ram?) and i will be running this on a couple hundred rows (lots of data). Also, i would like to keep the functionality so that i can still choose how many times it copys/inserts each individual row from a input box.
I eventually want the data to end up like it is shown in the table but i am takin it one step at time because i want to understand what the code is doing.
View 1 Replies
View Related
Aug 9, 2007
I have a spreadsheet with three sheets („Data”, „20” and „30”). On the Data sheet I have only two rows, and I do not work on this sheet. I work with the sheets 20 and 30. On the sheet 20 I need to sum up the amount of column H. But my problem is that the count of this column (the rows or cells) allways change. Sometimes there are 554 the next day 663 and so on. So I need the macro to sum up the amount of column H two cells under the last cell. I need exactly the same in the sheet 30, but the only difference is that in the sheet 30 the amounts are in the G column (not in H). Finally I need to compare the two summed up amounts (sheet 20 and 30) and they must be equal. So what I would need as macro is that when I run it, it sums up the amounts in both sheets and then in the Data sheet (in the 5th row, cell A) displays the summed up amount and (in cell B) displays OK if the two summed up amounts are equal, or just dislpays ERROR if the summed up amounts are not equal.
View 13 Replies
View Related
Oct 22, 2008
Attached is a sample data set. If all the positive and matching negative entries are located and removed, there are 4 remaining entries. The only pertinent columns are A through J, the columns after that are where my fiddling is occurring.
First you have to highlight all the data (including the control columns to the right). Then, my macro sorts the data by the absolute value (found in column O), allowing the formulas in N to then properly mark all negative entries with an "X and all the ones that now have a matching positive entry directly below it with an "X".
Then my macro hides all the fields with an "X" in column N. There is a macro called "CLEANSE" that does this, but only hides the rows. I keep messing up when I try to delete them entirely.
Can someone correct my macro so it will properly delete the rows with an "X" in the N column, or suggest a better macro that will scan the E column and flag matching positive/negative numbers and delete them without my control columns N and O?
View 3 Replies
View Related
Dec 17, 2008
i need a macro to do a comparison b/w the two given files ...(book1 n book2) ...
n want the result to be like result file .. (result.xls)
View 6 Replies
View Related
Mar 10, 2014
My Process goes like this: We receive work in the excel sheet and in the column K(Period will be in YYYYMMDD)
First I want macros by which I can change YYYYMMDD TO MMDDYY.
then
It should compare column J and column K.
see the below example.
Period date (column K) Product end date (column L)
Column J column K
20131031 10/31/2013 = If column J & K dates are equal then "No action required"
20131031 7/31/2013 = If column J is higher then column K dates then "Action required"
20130930 11/2/2013 = If column J is lesser then column K dates then "No action required."
I need comments in column Q.
View 1 Replies
View Related
Apr 17, 2014
I am relatively new to VBA macros. I am having two sheets, in one sheet I have a non-contiguous 20 rows range and in the other sheet I have a 20 row contiguous range. I need a macro which will compare data between the two ranges(one to one compare) meaning 1st row of the first range should compare with 1st row of the second range and if it matches then it should populate the adjacent column in the second sheet with true or false accordingly.
View 2 Replies
View Related
