Compare Data In 2 Workbooks And List Missing Data
I have data in 2 workbooks. Book1 - Sheet1 - Col A and Book2 - Sheet2 - Col A. I want to compare Book2 with Book1 and list all values in Book2 that do not find a match in Book1. I want to run a macro for this
View Complete Thread with Replies
Related Forum Messages:
Compare Workbooks To Include Missing Data
I have 2 worksheets that need to be merged. More specifically, I have one 'master' XLS file with some columns included in it that are missing from a second XLS file. I need to merge the new columns (with column titles in row 5) into the second 'auxilary' sheet without touching data above row 5.
Compare Two Columns, If Missing Insert Missing Data
I have two columns which i want to compare, they contain text data such as A123.
what I'd like is if its in column A and not in Column B then add to bottom of column A.
Once its in column A i can do the vlookup's to draw the other data, costs etc, over but don't know how to identify, and add, the missing codes to the list.
Compare Data With Different Workbooks
i had received a new project. i want to compare the data among 2 different workbooks. there are book1 and book2 which i attach at here. if there are 1 cells is different with both workbooks, then the whole row will copy and paste to a new workbook "output.xls".
i also attach the "output.xls" at here.
the background color i highlighted which data is different, just for easy to view. so can ignore the color.
Compare Workbooks And Extract Data
I have 2 workbooks containg about 600 names and the serial number of the computer they are using. One I keep so I know who has what machine. The other is created automatically by a service we subscribe to.
This workbook is loaded with errors. Machine costs being charged to the wrong people.
They both contain last name, first name and serial number. Although not in the same columns. With formulas, I have been able to take the serial number from the service.xls and find that serial number in the inventory.xls.
The ultimate goal is this:
Take serial number from service.xls and compare it to inventoryxls.
If it exist, put the first and last name of the user from inventory.xls onto the service.xls sheet.
If it does not exist, then put "does not exist"
Compare And Merge Data From Two Different Workbooks
I'm sure that there is an excel function that can do this but darned if I can figure out which one it is. This is what I would like to do: I have two different workbooks. Each workbook has one worksheet that has two columns of data. The first column is a part number and the second column is a price. The first workbook holds my master data. Starting at the first column of the first row on the second workbook I wish to compare the part number held in that cell with the part numbers held in the first column of the first workbook. If there is a match then I want to take the associated price from the second workbook and place it in a cell to the right of the corresponding part number in the first book - actually in the first open cell in the third column. I wish to do this automatically via macro, or otherwise, for all part numbers in the second workbook. The macro will also need to recognize that some part numbers in the second workbook may not appear in the first workbook in which case that part number is skipped.
The end product is my first workbook that has been transformed from two columns of data to three columns of data - one part number and two price points although not all part numbers will have the two price points. If it helps I can copy the data in the second workbook and paste it in to a second worksheet in the first workbook and do all the work in the one workbook.
Compare Data From 3 Workbooks Against 2 Files
For the past several days I have been attempting to write a macro, which in theory would check the values within a one- sheet workbook (well call it run_list.xls) against the values in two different workbooks (well call these production.xls, development.xls). However after several days I am nowhere closer to solving this problem then I was when I started. Ive tried different scripts and variations of vlookup, but I have been unsuccessful in tailoring what I have found to meet my needs. So I feel it is time to lie down and scream for a medic.
Heres some background on the workbooks. The sheets in all three workbooks are set up in the same manner. Cell A1 contains time/ date, cell A2 contains a lot#, cells A3:A99 contain positioning data, and cells B3:B99 contain serial numbers associated with tubes in the specific positions. The only differences between the workbooks are the sheets in production.xls and development.xls are labeled according to their specific lot number.
What I have been trying to do is to take a value from cell "B3"in run_list.xls and find where that value occurs in column B either of workbooks. When that value was found I wanted to copy cell "A2" from its sheet and paste that value in cell "D3" of run_list.xls. I had also wanted to repeat that those steps for every cell in column B containing a serial #. If a serial number was not found I wanted it to report Not Found. There are also times when instead of a serial number a phrase No Trakmate is listed. In these cases I had intended to skip these lines.
Also, since production.xls, development.xls are updated frequently their names are also updated with new version numbers ie. Production_v10.xls I had intended to allow the user to choose what files to search in but was unable to figure out how to add a second location.
Compare Two Workbooks And Copy Unmatched Data
I have two workbooks: one is a daily workbook that will be used to keep track of work accomplished, the other is a weekly report that is generated by head office.
What I need to do each week when the head office report is generated is to match up the Project ID's (they are the constant in each workbook) from the daily workbook with the weekly one. If there are Project ID's that are new, the corresponding information would then be copied over to the daily workbook.
I am working on the copy command but I'm just not sure how to go about setting up the search to match Project ID's.
Compare Data In Two Workbooks And Highlight Matches
I am running a business and each day I download my orders for the day. The information includes customer names and addresses. The customer name is for example in column E. We will call this "Book1". I have a second workbook that I use to store bad customer information. i.e. customers that I do not wish to sell to. The information stored here is customer name and address e.g. column A - customer name, column B - 1st line of address etc. This will be "Book2".
I wish to programatically be able to compare customer names in column E Book1 to the customer names stored in column A Book2. Any matches found should highlight the rows (i.e. orders) in Book1. I have tried to make this clear as possible. I want to be able to do this as a macro hence programtically so when I click a button this comparision/highlighting takes place.
Compare Two Ranges In Different Workbooks And Copy Data To A 3rd Workbook
I have two spreadsheets in different workbooks ( workbook 1: sheet 1 and workbook2: sheet1), here i need to compare column 5 in Book1 and Column 5 for all cells, say X is the value we are looking for..
X occurs once in book1 and might occur more than once in book2..so if a match occurs ( that is once the code checks that there is X occuring in both books in columns 5) it should copy all rows in book 2 where X occurs to a new workbook 3 in sheet 1 and also it shoud copy entire row data where X occurs in book 1 sheet 1 . But this data from book 1 has to be copied at the end of row after the data from book 2 has been copied.
if X occurs 4 times in book 2 , then 4 rows have to be copied in book 3 and then data from Book 1 where X occurs only once is copied 4 times at the end of the data from book 2.
this process has to repeated for all cells in columns 5 in book1 and column 5 in book2 .
Dim M, N As Range, x As Variant, y As Variant
Dim NewRange As Range
To get the book1 location
MsgBox " Selec the Location of N File"
Compare Data-set With List & Summarize Matches
1) Background Info
We are trying to summarise some data that has been exported from an ancient database into a poorly delimited csv file. My colleague has imported the csv file into Excel ( attached), and we are trying to work out what to do next.
The dataset contains approx 300 records - each record being called a "sample" and having a unique sample number. NB: I have had to attach a cut-down version with only 3 samples, due to file-size - but it will hopefully give an idea.
Each sample contains 2 types of information that we are interested in (and a lot of irrelevant data besides). The relevant bits are:
- predicted occurrence of various species (given as a percentage for each species)
- observed occurrence of various species (given as positive/negative for each species, where positive is indicated by an asterisk)
2) What We're Trying To Do
We are trying to compare this dataset with a master-list of 80 species names. For each species on the master-list, we want to:
a) check whether it has a "predicted occurrence" value in each of the 300 samples (and if so, record the value in a summary sheet)
b) check whether it was observed in each of the 300 samples (and if so, record this in the summary sheet)
3) The Problem
Unfortunately, the dataset has imported into Excel as a gigantic list: 48000 rows (including loads of blanks) and only 3 columns across. To find the relevant data, it's necessary to:
a) open the attached workbook, and go to the worksheet named "Data"
b) Scroll through the rows, looking for the string "RIVPA" in column A. This tells you where each new sample begins. (The sampleID is stored in the same row as this, in column C. It is mixed up with a load of text, which we will need to separate out at some point, but that's a secondary consideration at the moment.)
c) Scroll down further until you find the text string "Predi" in column A. This indicates the beginning of the data we're interested in, for each sample (i.e. for sample 1, I'm talking about row 58). Count down a further 2 blank rows, and then you find the data itself:
- Column A contains the observed occurrence (a positive result is indicated by an asterisk)
- Column B contains the predicted occurrence
- Column C contains the species name
My colleague was trying to build a summary table, in the worksheet named "Summary". He was using the LOOKUP function to extract the data, but because there are so many samples, it's beyond unwieldy. He asked me if I could write a macro do do the job, and this is where I'm stuck.
How could I set up a macro that can identify where one sample starts and finishes, and where the data is within each sample? What is the most sensible shape for such a macro? If I could get the overarching logic worked out, I could make a start on writing the individual bits of code, but at present I just can't figure out how to begin.
To make things more difficult, the samples are not all the same length, and do not all contain the same list of species as each other. (However, at least there should be no species in the samples that are not in the master-list). The first sample begins on row 5, the second sample begins on row 173, the third on row 340, and so on.
Compare Multiple Column Of Data And List Out Common And Unique Component In Adj Columns
I am trying to compare multiple column in a worksheet to find
common component in all the columns and what is unique to a particular
column only. And list the results/finding in adj column. What i am
trying to accomplish is something as below.
Sheet1 Sheet2 Sheet3
Column2 Column2 Column2
02-1234-12 07-1234-12 02-1234-12
04-1234-12 03-1234-12 02-1234-12
05-1234-12 02-1234-12 06-1234-34
Common to all Unique to sheet1 Unique to Sheet2
02-1234-12 05-1234-12 07-1234-12
Vlookup To Compare Two Sets Of Data And Change The First Set Of Data If It Is Than The First
I have tried nested ifs and vlookup to compare two sets of data and change the first set of data if it is than the first. But leave it alone if it either is the same or does not exist in the new set of data. It sometimes seems to work but i find it is not consistant. It looks simple but i think i am missing something.
if column A has identifiers and column B has results then it should work if the identifiers in column C are found in column A and it looks to see if column B and D are the same, then change B if different but leave it if either it is the same or not there.
Compare Data If Data Is Changed Put In Column In New Sheet
I want to import data with a web-querie. The data consists of numbers in a column (14 cells).
I want to poll the info in the table in the webquerie every 15 minutes to see if the info in the table is changed. Therefore I refresh the info every 15 minutes, and when the info is changed, than the data must be put in a new sheet in a column and the columntitle must be the date and time that the info is put into the column. Can this be done in vba? I suppose that to compare the info of the new poll with the info of the previous polling the previous info must be put in a "compare" table.
Message Box For Missing Data
I have ben given a excel file from another department at work as they had been told i know a bit about excel. Excel... Yes, Visual Basic... Nil, zilch, nada! It really might as well be written in chinese!!
What has happened is the guy who set this up has since been made redundant from the company and they now want the file to do one more thing.
Basically what they want the file to do is, when the user clicks the 'generate file' button, if E2 has no date entered then a message box pops up stopping the user from going any further until a date is entered.
Flag Missing Data
Is there a way to flag data that hasn't been used in another worksheet. For example, a second worksheet consolidates the data into two different groups, but someone spelled the name of one of the groups wrong in the first worksheet and the data was not added via a sumif function.
Compare And Copy New Data And Updated Data
I would need Excel to compare rows between two worksheets (A & B), and if there are any:
(1) new rows in A, to copy across the new rows onto B.
(2) updated rows in A, to copy the updated data onto B replacing existing B values.
This in itself would be simple. Only thing is, worksheet B would be subtotaled and sorted by the subtotal and this is something I wonder if Excel can cope with. BTW I wouldnt mind it if subtotals in B has to be undone before the data gets updated, so long if Macro automates it for me. Is this possible or am I asking for too much? A representative excel file is as attached.
Add Missing Data By Comparing With Another Workbook
I have been searching the forum for weeks now for an solution to my need, but was unsuccessful. I have two Workbooks. One is for tracking new enrollments in the school for whom I work named WVT.xls and the other is for tracking the school materials assigned to each student named 2007.xls (for inventory accounting). I need to streamline adding new enrollments names to both Workbooks by adding to only one manually and then using VBA code to update the names in the other workbook. The sequence would be to type the names into WVT.XLS and run a macro in 2007. XLS to automatically update the names. Below is the locations of the same named columns in each workbook.
First Name Last Name
WVT.XLS Column B Column A
2007.XLS Column B Column C
In 2007.XLS, there is a sheet for each school location that is represented by a unique school code. In WVT.XLS, there is one sheet for all enrollments from the start of distributing materials. Each row is a student. There is a column that contains the unique school code to identify in which school the student has enrolled.
Check A Userform For Missing Data
I have created a user form for some data submission. I am looking for a macro to check the text boxes, that I have named appropriately i.e. Name, Address, City, State etc. , to make sure there is something entered.
If nothing is entered it needs to return a msgbox that says what field is blank.
Dynamic Name Range, Missing Some Of The Data
I have a pivot table based on a sales sheet called "Datasheet". I have created the pivot table using a dynamic name as below =OFFSET(DataSheet!$A$1,0,0,COUNTA(DataSheet!$A:$A),12). My problem is that I have 11100 lines of sales data but the when i refresh my pivot table it only seems to be going down to row 10979. Therefore missing out some of the data. Is there a max number of lines for a dynamic named range or is my formula wrong in any way
Auto Fill Down Missing Data In Column
I'm trying to setup a macro to run on a database extract that is sent to me. Some of the data is not filled in for each row and I want to automate the auto fill down for each entry based on the Reference # above. I searched the forum for terms that I thought might be related,
Here is the starting data: ...
Combine 2 Worksheets & Fill In Missing Data
I have 2 sheets in the same workbook (sheet 1 & 2) with one matching column (A) of info and need them to combine and fill the missing data. Each sheet has identical column heading and the amount of data is not the same; sheet 1 has ~2000 rows and the other ~5000. Sheet 1 is consolidated so I would like to fill in the missing cells from sheet 2.
Ex. Sheet 1.......
Ex. Sheet 2.....
As you can see, the missing cells in sheet 1 could be filled from the data in sheet 2; also note that the above info is on different rows (and will be random for each case). I only want the columns to fill in that have a matching email, the other 3000 rows in sheet 2 do not match with sheet 1 and I don't want the rest of it to transfer over.
Index Function - Data Identified Based On A List Of Account Numbers And Return This Data To A New Sheet
I have a multiple column spreadsheet (Call it- "Money") whereby I need the data identified based on a list of account numbers and return this data to a new sheet.
In "Money" I have:
IE; columns B, C respectively have cust #A100 & 20.00
columns E, F respectively have cust #B100 & 40.00
columns G,H respectively have cust #C100 & 60.00
(above for illustration-there are 100 lines of data in these columns with varied account numbers and respective dollars)
So what I have now is a new sheet I have named "Control". I have listed all my account numbers like A100,B100 etc. in column A. These are the account numbers for ident purposes.
I need the data entered in "Money" identified by those columns B,E,G with respective amounts from C,F,H and based on the list I have in "Control" whereby in "Control" if A100 is listed in column A then the figure to be returned in column B is all the data bits found in all columns C,F,H from the entire sheet "Money". Tough to explain but ie below....
Data Validation List: Ignore Blanks Between Data
In column A I have a list of text. There are blank lines in between the cell that actually contain text. What I am trying to accomplish is create a validation list that will give me only the cells with text in them and ignore the blanks. For example in column A1:A7 I have the following text:
My validation list will return those names but will also give me the blanks in between the names.
Is there any way to ignore the blanks?
Allow User To Automatically Add Data To Data Validation List
I am trying to learn the concepts, then apply them to a set of actual worksheets. I am trying to use named ranges from one worksheet in another worksheet. The named ranges need to expand. Although I can get the add data to a dummy worksheet to work within the same worksheet and I can even get a named range to work in the foreign sheet.
My problem is, when I can end up adding the additional data to the named range, it will only add one name. I have a number of named ranges that are referenced on various worksheets. The idea is, certain named ranges should be able to expand so I donft have to type them all in. (There could be hundreds of manufacturers or models.) I would also like them to be able to sort themselves after additional names are added to the named ranges. I would also like them to auto populate as well. I am using Excel 2003 SP3 (11.8307.8221). I have enclosed the file. Drop down lists (named ranges) are on the worksheet called DROP DOWN LISTS (LOCKED) **ITfS NOT CURRENTLY LOCKED**.
The data validation issues are on the worksheet called Rev7.0 Wood (Basic Sheet.) There are other instances to use these, but I hope to learn to do them myself on the other worksheets. Of particular interest is Column f.................
Data Validation List With Changing Data
I would like to utilize data validation to create a drop down list from a list of 100 rows that changes monthly. Currently the list is set to draw data from A1:A100, however depending on the month, different cells will have data in them. What I don't like is currently the blank cells create gaps in the data validation drop down list and make it longer than it needs to be. Is there any way for excel to automatically just display cells with data in them in the drop down?
Data Validation :: Limit The Data Available On The List
have created a list obx using data validation. My question is, is there a way to limit the data available on the list.
Example: sheet 1
A1 A2 A3
address s Date
has data validation list that consists of all of A1 a named range called "address". Everything works great.
I now want to be able to limit the available choices in the list depending on what cell the user is in
example: if user is in sheet2 a1, i only want them to be able to pick address that have an "A" or "p" in cell A2 on sheet 1
I tried to acomplish this using conditional formatting, I format my named range with different colors, but the colors do not carry over to the data validation list on sheet2.
Compare Data For Month
Each day I have workbooks saved that contain data of alarm files saved format PAR Process Alarm Report_MMDDYYYY.
Within each workbook there is a sheet named E&H for each day. The goal is to figure out which Tag is causing the most problems. 1 Tag may hit 100 times in a day and then again 40 times a week later. Another tag may hit 2 times a day every day - This is the tag we would want to tackle. Ive attached 1 of the E&H sheets so you can see what they look like. Comments are in row 1 explaining what they are.
I have code provided here that will go into each workbook. Ive commented out the part that I think needs to be edited to make this all work but I could be wrong. This will be extremely helpful if I can get this all to work.
Compare Data Between 4 Columns
i want to compare data between 4 columns, 2 columns of data is in Sheet 1 and 2 columns of data is in sheet2. Data starts at A5 in both worksheets. Data consists of stock codes and there holdings. Sheet 1 data is real data the primary source, sheet 2 data is a bit of real data and incorrect data.
My data in sheet 1 is layed out as below, this is just a sample, real data has around 1500 stock codes and holdings.
Starting at A5
Col A (Stock) Col B (Holdings)
5 aap 10500
6 aax 10987
7 aas 76544
Sheet 2 data is layed out as above but holdings may differ, so
Col A(Stock) Col B (Holdings)
5 aap 10500
6 aax 10300
7 aas 76534
I need a formula which will tell me which stocks holdings have changed in sheet 2 compared to sheet1.
Compare The Data Between The Two Columns
I'm working with Excel 2007, I have two tabs on my spreadsheet, each tab contains a column of numbers formatted like 5552220. Is there a way to run a macro or write a formula that would compare the data between the two columns and either automatically delete the numbers that don't have a match or highlight them? For example:
Tab A Tab B
6666445 needs to be deleted in tab B because it's not present in tab A. There could be up to 100 numbers that require deletion from tab B.
Compare And Combine Data
The data in each worksheet is from different programs that I need to combine. The first worksheet contains student mailing address information and the second details of payments for a specific purpose. Both worksheets has common data that is the student number. We have around 960 students and I can have anywhere between 400 and 20 students paying within any given month.
Below is an example of what I need, Sheet3 being the end result. I don't care if it is easier to combine both sheet1 and sheet2 completely and need to delete columns or move anything around (that takes no time).
I have tried to research how to do this but as I have no programming experience I have been unable to get my head around it.
Compare Columns Of Data
I have column A with 228 rows and column D with 314 rows. Both columns have the same data except that D has different data. I would like to line up everything that is the same in A and D and everything else in column D that is not the same move to G. Is there a quick way to do this?
Compare Data Using Wildcards
I need to match up two columns of data. I need to match the first column with text in the second column that is "like" the first. Example..
Basically I need to search on only part of the text in column B. So I would need to wild card both sides of the text like "*111*".
Compare Lists Of Data
I need to search a column of numbers in Excel (A) and compare it to another column (B). I need to find all of the numbers that apprear in column A but not in column B. Can this be done?
Compare Data In Columns
I have 2 columns say A and B (infinite number of rows)
A is an Indexed number starting at 1
B is numerical data.
I now have a second set of the same columns with the same data, however some rows are missing. I need a way of comparing the data and then display the rows I am missing in the second set of columns.
For example the first set will be the master list and look like:
Second list has row 2 and row 5 missing.
The desired result is to have the missing rows displayed;
Compare Data In Different Tables
I need to check data from two tables(A and B). They are supposed to match each other and my job is to check if there's error in it. Each row contains several criteria, and all criteria need to be matched. I need to give a report on the info that
1) contains in table A but not in B
2) contains in table B but not in A
3) contains in A and B but some of these criteria do not match..
I have basic knowledge of VBA. Forget to say, they are in different order. And even after sorted, the name may still unmatch with each other, so can not use formular like "=Sheet1!A1=Sheet2!A1"
Compare Data In Two Worksheets
I am new-hire training programmer in multinational semiconductor company in Penang, Malaysia. I had been given a project to compare one data sheet (sheet 1) with another one data sheet (sheet 2) and the result is displayed in another sheet (sheet 3) in the same workbook.
1. To start compare, I need to click a button (command button) in Sheet 1 and then VB will run and displayed the result in sheet 3.
2. The comparison is based on the wafer map which is :
i)Value=1 in sort4(sheet1) is equal to the value=1 in Pattern Verification(sheet2), the result will display PP(Pass/Pass) and count the quantities of PP.
ii)Value=1 in sheet1 is equal to value other than 1 in sheet2, the result will display PF (pass/fail) and count the quantities of PF.
iii)Value is other than 1 in sheet1 is equal to value=1 in sheet2, the result will display FP (fail/pass) and count the quantities of FP.
iv)Value is other than 1 in sheet1 is equal to value other than 1 in sheet2, the result will display FF (fail/fail) and count the quantities of FF.
v)All the result will display a new wafer map with all result above. Result will display in Sheet 3........
Compare Data Across 2 Worksheets
I would like to create a macro button that when pressed will make a comparison of the data that was inputed by the user and the raw data on another spreadsheet. If the number is higher than the raw data, it would say "Above"; if lower, "Below"; if equal, "Same"; if raw data is not available, "NA". These 4 destinations would show up in the F,G,H columns under irrigation, livestock, aquaculture for each parameter listed. The raw data can be found on another spreadsheet (sheet 2).
Compare Worksheets From Two Workbooks
I have a master tracking document that I use to record project information. My client sends me an updated schedule each week which may (or may not) have additional stores on it and some of the details of the stores may have changed.
I need a macro to capture these changes from the source spreadsheet (the one the client sends) and update the master tracker. The master tracker has a lot of additional columns of data that I add in myself about each project so I don't want to lose this information. The macro needs to see if the store on the source sheet is already on the master tracker and if it is then it needs to check to see if any of the columns below have changed.
If the store isn't on the master tracker then it needs to be added. There are around 750 stores on the master tracker at the moment so to do it manually takes forever!
Column A - Retail Region
Column B - Project Name
Column C - Postcode
Column D - Net Selling Area
Column E - Project Manager
Column F - Contractor
Column I - Start On Site
Column J - Launch Date
Column C - Retail Region
Column D - Project Name
Column I - Postcode
Column J - Net Selling Area
Column M - Project Manager
Column N - Contractor
Column P - Start On Site
Column Q - Launch Date
Compare Strings In Two Workbooks
I have a workbook called Keyword that has two columns c,and d, of words.
The length of the columns can vary,because I add words to them.
I also have a workbook called testfile, and cell B1 has a word in it.
Testfile also has an .xla module that adds in when testfile is open.
I want to type a function called asset in cell C1 of Testfile,and have
it compare each word in Keyword workbook to the word that's in cell B1
of testfile. If a match is found in column C of Keyword, the function
returns the letter C. If match is found in col D of Keyword, it returns
the letter D. If no match, function returns "no match".
I have both workbooks open and the vba code I stuck at the bottom of
the .xla module as another function.
Here's my code so far.
Function asset(Description As Variant) As String 'arg. is the word in cell B1 in testfile
Dim LastRowOfC As Integer, LastRowOfD As Integer
Dim i As Integer
Compare Information In Two Workbooks
I need to compare the info from two worksheets that are in different workbooks.
Comparing Column 3, I need to find wich entries from WB1( the old file) are not presented in WB2( the updated file) and whitch entries from WB2 are new ones, not presented in WB1.
Compare And Move Rows Of Data
I need to run the same database dump every day and find the differences. Both spreadsheets will have the same column headings, with Column A being the lookup value. Each spreadsheet will have many columns of data. I need to find the differences from Row 1 on spreadsheet A with Row 1 on spreadsheet B.
Spreadsheet 1, Column A value is 900026. Need to find 900026 in Column A on Spreadsheet 2. Compare all the data in each column to see what has changed. If anything has changed on Spreadsheet, copy the row from Spreadsheet 2 onto a new sheet and 'tag' which data has changed.
Also, again using Column A as the identifier, if there are rows on data on Spreadsheet 1 that do not appear on Spreadsheet 2, copy the entire row onto a new sheet and tag it as "Not on new datadump". And vice versa, is there are rows of data on Spreadsheet 2 that do not appear on Spreadsheet 1, copy the rows of data onta a new sheet and tag it as "Not on old datadump".
Compare Blocks Matrix Of Data
I have the follwing data in A1:T17
I want to compare the first set of 20 numbers (A1:T1) with the second set (A2:T2) and check how many numbers match. ** If the matched numbers
are >=10 ** then list them to the right of cell W1. In this example, 10 numbers matched are 4,9,10,21,35,47,64,72,74,75. List them in W1:AF1.
Now compare (A1:T1) with (A3:T3). In this example, 11 numbers matched 4,9,10,21,33,41,47,57,6,72,74 list them in W2:AG2.
Now compare (A1:T1) with (A4:T4). 10 numbers matched 3,4,10,11,21,32,33,35,60,69 list them in W3:AF3.
Compare (A1:T1) with (A5:T5). Here only 5 numbers matched 9,21,64,69,75 (Which is ** less than 10 ** - does not match the criteria) DO NOT LIST
Compare (A1:T1) with (A6:T6). 11 numbers matched, 3,4,7,10,21,33,37,47,57,69,75 list them in cell W4:AG4.
Dim r As Integer, rr As Integer, c As Integer, cc As Integer, opc As
Integer, opr As Long
opr = 1
For r = 1 To 16
For rr = r + 1 To 17
opc = 23
For c = 1 To 20
For cc = 1 To 20
If Cells(r, c) = Cells(rr, cc) Then
Cells(opr, opc) = Cells(r, c)
opc = opc + 1
The above code runs fine but If I increase my rows from 17 to 1200 it takes 3 1/2 hours to complete. Can it be reduced?
Compare And Copy Data From One Sheet To Another.
SalesImport contains sales data, and one column of that sheet is a unique reference number to identify the agent responsible for those sales.
Main contains much more data, and I need to transfer sales data from SalesImport to Main in the most efficient, automated way possible - on a monthly basis most likely... but possibly weekly or even ad-hoc at a later date... so it really does need to be quite quick.
At the moment I have come up with an idea to loop through each row on the SalesImport page, and for each row, compare the unique reference number (Column I) with the unique reference numbers on the Main sheet, (Column C). If they match, set the value of the sales column on Main to the same value as the sales column on the SalesImport sheet.
However, this is not working as i would hope, and doesn't copy anywhere near all the rows I know are found in both sheets.
Dim c As Long, d As Long, Limit1 As Long, Limit2 As Long, Limit3 As Long, sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet
Set sh1 = Sheets("Main")
Set sh2 = Sheets("SalesImport")
Limit1 = sh1.Cells(Rows.Count, 3).End(xlUp).Row
Limit2 = sh2.Cells(Rows.Count, 9).End(xlUp).Row
For c = 2 To Limit2
For d = 3 To Limit3
If sh1.Cells(d, 3).Value = sh2.Cells(c, 9).Value Then
sh2.Cells(c, 27).Value = "In Main List"
Else: sh2.Cells(c, 27).Value = "Not in Main List"