VBA To Compare Worksheets; Update Certain Columns If A Match; Add New Row If Not
Jun 17, 2008
I would like a macro that can match column A & column B data of Worksheet(WeeklyJob) to column A & column B of Worksheet(Master) then if match is found copy column C through column F into Worksheet(Master) column C through F (and overwrite any [outdated] existing data there may be in those columns [thus updating the job's weekly charges, etc.]).
If match is not found I would like it to copy entire row from Worksheet(WeeklyJobs) into first blank row at end of Worksheet(Master) (thus giving me a new record of a new job from the weekly report).
All the columns in both worksheets are labeled the same (& row 1 is headings).
We have a tariff table with the following data per row: Area code - Destination name - Price - and some other stuff that is not important. Once or twice a month we receive updates on Price for some destinations and maybe even some new rows with area codes and destination names that we do not have in our table. Can someone help me with a macro that will look into two sheets and do 2 operations:
1) taking as reference the Sheet2 - Column C (Price Tag) will update column C in Sheet1, using the Area Code (Column A) of both sheets as identifier to find to what row will apply the change;
2) if there is a new Area Code - Destination - etc. row that is not present in sheet1, will add it to sheet1
It does not matter to me if will update directly sheet1 or it will write the entire sheet1 content with the updates to Sheet3.
I receive hundreds of excels which I combine into one sheet for input into a program (which I don't know much about). The combined sheet contains 2 columns repeated with different information. For instance Column A is Category, Column B is Dollar Amount, Column C is Category, Column D is Dollar Amount and so on.
I then start with Column A and compare it to Column C to make sure the categories match. If C is a new category (not listed in Column A) I shift columns A and B down one and add the new category to Column A. This updates my master list in column A but also maintain the format and correct dollar amount that was listed in Column B. Then I move to Column E and compare it to A using the same logic.
I repeat this process throughout hundreds of columns. I then delete all the category columns and am left with Column A as a master category list and all the dollar amounts in the correct location for all excels that have been sent in. I'm hoping someone here knows a faster way for me to do this using VB or VBA for Excel or a Macro. I've tried a few things I've found on these forums to no avail, and I'm not an expert when it comes to this. This currently takes me days to complete, and I know it should not take this long. Please save my sanity!
I have two workbooks, one is reference (W1), other is the one that needs update(W2). I need macro that compares reference column in W1 with targeting column in W2, then for match cells update two or more cells in same with new data from reference cells in W1, and for end to report what data in W1 in reference column wasnt find in W2. I tend to use macro in reference book W1, and to update book W2 without opening, so I need this macro to work just with file manager from excel . No need for user modul for targeting columns, they are static in both woorkbooks. In practise in W1 is invoice prices with part numbers, at other one is the same, but with prices for distribution (with formulas), I want ti automaticly refresh part numbers with new prices, an to have report what part numbers are not in price list (W2)
I am not a programer by trade so I can't figure out what I need to do to solve my problem. I need to compare two worksheets to each other and only keep the rows of data that match. I am comparing address's in each worketsheet with each of them located in column C in each worksheet.
Basically, i have a workbook that contains 2 worksheets with stock data for two stock holding sites, we're consolidating our stores and need to confirm what stock is held at both sites. I need to compare column C on both sheets to find if an item exists on both sheets, and if so copy the entire row from Sheet 2 onto the next free row on a new sheet. Col C on each sheet contains the stock number, however, on Sheet 1 the stock number is abbreviated ot the final 7 letters, with Sheet 2 showing the FULL stock code.
I need whatever is in "Sheet 1:Col C" (for example '1234567') to be compared to whatever is in "Sheet 2:Col C", and if a match is made (for example 9999-00-1234567) then the row containing the match be copied to the next free row on Sheet 3.
I have two worksheets, PM1DATA and AMDATA, i'm looking for a way to compare the two sheets and have any data that appears in collumn B within the AMDATA sheet, but NOT within collumn B of the PM1DATA sheet to be copied and moved to a sheet called NEWKITS. I would like the entire row to be copied when new data is found, not just collumn B.
I'd like to compare 2 columns from different worksheets in the same workbook with a twist.
If any and all data from worksheet 2-column c matches any and all data from worksheet 1-column c, then go back to worksheet 2-column B on the same data matching row, copy the data from that cell and paste it into the same row of the data match in worksheet 1-column b and paste it.
I know there is a forumla to compare a small database to a list, but can't find it (I used to use French Excel).
Here is an example :
In a sheet, my small database :
(in two different cells)
Value1 and ValueA Value2 and ValueB Value3 and ValueC
etc...
In another sheet, my list :
I have a list where there's a lot of Value1, Value2... in one column, and in the next column, a lot of ValueA, ValueB....
I want to check in my list : if I have Value1, then, on the same row (next column) I have ValueA, if I have Value2, then, I should have in the same row (next column) ValueB....
And if I have Value1 and in the same row (next colum) ValueB, I want some kind of "Error" text.
Can't find this function nor (of course) the criteria to use it correctly.
I need to compare names in column A(sheet 1) to names in column A(sheet2) and if there is a match then I need the date from column B(sheet 2) to be copied to column B(sheet1).
I have 2 columns of usernames A and B, A has approx 700 usernames and B has about 80. In column C I have a list of names 1 for each username in column A. I want to compare the B with A and if there is a match return the value in C and place it in D.
I have a list of names on 2 separate sheets that I need to modify. If I have a name on sheet1 matches a name on sheet2 I would Like to add a "space" then "(SV1) at the end of the text string on sheet2. I have a list of 1500 and will only have to modify 75-100. Example shows on sheet2 how I would like the desired outcome to be.
I must match column A and column B to return the column C In colA there are all my countries In column B there are all my countries code and countries I want get the colC where all my colB match than colA.
I have 2 workbooks each with last name columns. One book has a column of a few hundred names and the other has a column of over a thousand. I need to compare the last names and pull out the employeeID (which is in a seperate column) to a specific column for the ones that match.
Trying to compare data from two spread sheets, if there is similar data in column a, output all data (sheet1 & 2) to the 3rd sheet.
Sheet1 a b c d smith john 888 cicero king larry 123 syracuse
Sheet2 a b c d smith marge 777 liverpool king mike 458 dewitt
Sheet3 a b c d a b c d smith john 888 cicero smith marge 777 liverpool king larry 123 syracuse king mike 458 dewitt
The closest thing I can find to what I'm trying to accomplish is this link here: Compare Worksheets
I'm also would like the ability to change the columns I am comparing. The actual sheets may have more that 4 columns.I can somewhat read the formulas but have a hard time under standing them completely.
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.
I have 2 worksheets in which I have to copy one column of cells from one to the other. The problem is that the "main" worksheet lists nearly 3,500 clients and I have to copy numbers into the "main" worksheet on weekly basis from another worksheet that lists only about 1,100 of those 3,500 clients.
Both client lists are in alphabetical order, but I obviously cannot just copy one column into the other, because then the numbers won't be matching the correct clients.
I'm sure this query has been answered somewhere else but I can't seem to find it. I basically have information in two worksheets in the same workbook which need to be compared and the missing values from one worksheet need to be listed in a message. There are duplicate values in both worksheets so only need a list of the unique missing values. For example:
Sheet1 Column A 1 2 1 5 5 2 3 5 4
Sheet2 Column A 2 3 3 4 3 4
The message box should state that we are missing 1 and 5 from the dataset as it is not in Sheet2. If the list could be sorted in ascending order that would be great too.
I'm working on budgets (salaries). I have one main spreadsheet with all employee names listed - other spreadsheets are for each department. I need to bring the total salary from the department spreadsheets and would like to do this by looking up (or matching?) the names in the column from the main sheet to the department sheets and then putting the salary from the department spreadsheet to the main spreadsheet. In addition, I have several that have their salary split between two or more departments. On my department spreadsheet I have a column labeled % of salary for this department. On the main spreadsheet, I want to figure out a formula that will sum the percentages for a certain person (so I make sure I don't go over 100%).
For example - Joe Smith's name is on the main employee spreadsheet. He is also listed on 3 other department spreadsheets, with % of salary listed as 30% for department 1, 30% for department 2, and 40% for department 3. On the main spreadsheet I need to add up Joe's percentages from the department spreadsheets.
Been a while since I've worked with macros within excel and I can't seem to get what I remember being a basic macro to work whatsoever. I have 2 worksheets containing a massive amount of data and need to pull some cells from one into the other when values in 2 columns match.
To better explain, sheet1 has ID numbers in column G spanning for roughly 1700 rows. Sheet 2 has corresponding ID numbers in column EO. The data I need to copy over is in columns EP and EQ on sheet 2.
So I'm trying to build a macro to compare the values in Sheet1_Column_G to those on Sheet2_Column_EO and when a match is found, copy the value in EO and the adjacent entries in columns EP & EQ over into columns X, Y, and Z on sheet 1.
Couldn't get a VB function together to save my life so I tried working with MATCH & INDEX and didn't get very far either. I've included my current function below.
I have a spreadsheet with ~35k rows of data. This isn't too easy to explain I have the following data (example).
User Name Employee ID Data Level Jan 08 Feb 08 March 08 etc. Jo Bloggs 12345 A1 1 1 0 Jo Bloggs 12345 A2 1 1 0 Mark Jones 89101 A1 1 1 Mark Jones 89101 A2 0 1
For each user with data level A1 I wish to add an X if the same user has a corresponding data level of A2 so the result should be,
User Name Employee ID Data Level Jan 08 Feb 08 March 08 etc. Jo Bloggs 12345 A1 X X 0 Jo Bloggs 12345 A2 1 1 0 Mark Jones 89101 A1 1 X Mark Jones 89101 A2 0 1
I would really love a formula or macro to be in existance that would save me from trawling through all this data and going blind.
Each monday i get a new price list from our supplier. My job is to compare this list with ours and update if neccesary. The reason, why I'm writing here is simple -I need to automate the process. Since their and our pricelist is somewhat different, it's only possible to use selection comparison. So, I need something that can do this:
1. First I open those two files and make a selection on both of them (like all the apples on the supplier list and all the apples on our list) 2. Push a button that executes a code 3. The code compares a value in the first column 4. If it finds a match, compares the data in second column 5. If data is same, color the cell (or the text) lets say yellow 6. If data is different, update field in our pricelist and color the cell (lets say red), so I can find and recheck it later 7. If the supplier has a _new_ product, the code will insert it somewhere in our list. Doesn't matter where, it may as well be a new sheet 8. Compare the next cells in selected area
I have a price list from my supplier with the new prices.
I then have my Accounting software where I need to update the cost and retail prices. The problem I have is the Accounting software has allocated it`s own Unique ID for each item. So in order for me to bulk import this I need to keep this unique ID with the Actual Product ID together otherwise it will duplicate the product.
Here is a example
Sheet to be updated (Cost and Retail only) from PRICE LIST SHEET Uneque ID SKU
I'm trying to compare values in 2 separate columns to see how many times the same value appears in both columns. Ideally I would be able to insert a range function to compare the values in the column "ID 1" against the values in column "ID 2" and return the count of times that a value appears in both columns. For example 2122, 1112 and 1718 appear in both columns and I would like the formula to return a count of 3.
In my actual project I'm comparing 2 columns in the same worksheet. The column are column B with data in cells B2:B10266 against column C with data in cells C2:C18560.
I am trying to write a Macro so that my Mastersheet has a command button, that when clicked, will update itself with information from 5 other worksheets. I only need it to update new data and not data that is already there and i need it to copy it into the mastersheet in the next available blank row.
I am working with a mastersheet created by someone else and I have created the 5 worksheets. All the columns are exactly the same in both the worksheets and mastersheet. The mastersheet has 2 sheets, the front sheet ("Front Page") has the command button and the second sheet ("Activity Log") is where i want the new data to appear. I have very little knowledge of VBA, I have recorded a few macros and have used that to create the code i need to open a closed worksheet but I dont know where to go from there.
1.1st sheet is my new data. 2.2nd sheet is my old data. 3.3rd sheet is where I want to show the differences.
Im looking to do the following: 1.Compare Column A from the old data (sheet 2) to the new data (sheet 1). If a new value for Column A appears in sheet 1 and is not on sheet 2, then I want the row copied from sheet 1 to sheet 3 and be appended by ADD in Column M. 2.Compare Column A from the new data (sheet 1) to the old data (sheet 2). If an old value for Column A appears in sheet 2 and is not on sheet 1, then I want the row copied from sheet 2 to sheet 3 and be appended by DELETE in Column M. 3.Compare Column A from the old data (sheet 2) to the new data (sheet 1). If the value for Column A is the same but the value for either Column B or Column L is different then I want the row copied from sheet 2 to sheet 3 and be appended by FROM in Column M and I want the row copied from sheet 1 to sheet 3 and be appended by TO in Column M.
