I have a vb script used to compare a 3x3 matrix cell. My idea is to compare each cell by each cell. My 1st input is xx1.txt shown below
123
456
789
My 2nd input is xx2.txt shown below
1893
456
7810
I am expecting to get the below output with 89 and 10 highlighted in red because these are the difference between the 2 inputs and the rest of the numbers highlighted in green
1893
456
7810..................
I am modifiying a macro I once used to compare cell data from a worksheet to a worksheet in the same workbook. But I am having trouble getting it to work bewtween two workbooks.
View 2 Replies View RelatedI have the follwing data in A1:T17
3,4,7,9,10,11,21,32,33,35,37,41,47,57,60,64,69,72,74,75
4,6,9,10,12,13,15,21,22,31,35,47,49,52,56,63,64,72,74,75
4,6,9,10,15,21,31,33,34,41,42,45,46,47,57,60,68,72,74,78
3,4,6,10,11,13,18,21,30,32,33,35,46,53,60,67,69,74,77,78
6,9,13,16,21,22,31,46,48,49,52,61,63,64,69,70,71,75,78,79
3,4,7,10,14,17,18,21,28,31,33,36,37,43,47,57,65,69,75,80
4,7,13,15,17,25,29,32,37,42,45,47,50,57,60,64,68,71,72,74
3,7,10,11,16,18,28,34,35,43,47,51,52,55,56,57,60,64,71,72
8,9,10,12,16,21,22,28,38,47,49,51,52,53,54,55,64,66,71,72
4,5,6,9,12,15,19,20,30,34,35,38,45,47,54,56,63,65,72,78
5,6,9,12,15,21,26,31,32,43,44,47,64,66,67,68,69,74,75,80
..................
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
this.
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.
.................
Sub Compare()
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
End If
Next cc
...............................
Next r
End Sub
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?
Assume you have unique colors across the top & unique fruit down column A. in Sheet1
I than have a two column tables in sheet two with uniqe fruit in column A & corresponding colors in Column B-H.
Is there a way for me to place an "X" on Sheet1 based on a lookup on Sheet 2, where the fruit intersects with the colors? I sure hope that's clear - I can't think of a better way to explain it. Or if there's a better way to solve this many to many matrix - do tell.
For a table like the one below produced for the sake of example (actual is much much bigger) I want to make it list rows that are true for a certain column for a certain variable in the matrix. So for say water terrain, which types of activity can I do i.e. swimming. Or for Offroad the activites which I can't do i.e. Run and Swim.
ActivityWaterRoadOffroad
Jog nym
Run nyn
Walk nyy
Swim ynn
y=yes
n=no
m=maybe
I want to compare two Excel workbooks which have multiple columns and identify the rows only if all the valves match in both the workbooks, i am attaching a file to understand better.
View 3 Replies View RelatedI want to compare a string in a different workbook. and find the relevant cell value of that.
e.x. in one worksheet the value displayed like "DEBIT/ATM CARD TRANSACTION FEE" But in another worksheet the value displayed as "018 - Debit/Atm Card Transaction Fee".
Now I want to compare this string and the value displayed in the column E for this row of workbook 2 should be displayed in workbook 1 in the column D.
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
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.
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.
I would like to have a macro or a function, which could compare two "A" columns in different workbooks. macro should run from the first one wb, which gonna have less data in a column than wb2. so, if, let's say, A3 in wb1 differs from A3 in wb2, an empty row should be added in wb1, and now we would jump and compare A4 in wb1 with A4 in wb2 and so on..
View 2 Replies View RelatedComparing two workbooks. At first the workbooks were quite stable and didn't have no new entries or deletion of old entries so I initially used vlookup to give me what I needed and later found some basic VBA on the internet which did what I required quicker.
The workbooks have now changed slightly where each month new deliverables are added and old ones are deleted. This results in the entries of the two workbooks not aligning in the same rows/cells therefore when I run the vba it highlights 90% of the workbook as its just comparing cells against each other.
Each entry within the workbook has a unique identification number so I can still use VLookup for these however this doesn't allow me to see the new deliverables which have been added of the old deliverables deleted.
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!
Master Spreadsheet
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
Source Spreadsheet
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
I have workbook 1 and workbook 2.
I need to lookup the value in workbook 2 in order to populate workbook 1.
If workbook 2 value = NA, then "" in Workbook 1
If Workbook 2 value > workbook 1 value, then Workbook 1 value
If workbook 2 < workbook 1, then Workbook 2 value.
Should be simple. Keep getting errors in formulas @if or @lookup.
It would be nice to know by worksheet within same workbook as well.
1. To compare two workbooks with exactly the same columns- Target and Source
2. The target workbook basically has more row items than the source since this is considered the masterlist.
3. The source worksheet's names and contact details are filled out by different sources but will still maintain the same columns.
4. The records of the source workbook must be transferred to the Target workbook reference to its appropriate IDs.
5. The record of source book may be updated and will be fed to the Target book but must not copy the record that is already present in the masterlist.
Below is the code I was working at
Public Sub CopyRecord()
Dim sRng As Range
Dim tRng As Range
Dim sCell As Range
Dim tCell As Range
Dim tLR As Long
Dim sLR As Long
Dim sCount As Long
'Find last row of target sheet
Workbooks(Target).Sheets("Sheet1").Activate
tLR = ActiveSheet.UsedRange.Rows.Count
'Find last row of source sheet
Workbooks(Source).Sheets("Sheet1").Activate
sLR = ActiveSheet.UsedRange.Rows.Count..........
I have two open workbooks, the first is called "Original Data.xls", the second "SIP.xls".The "Original Data" workbook contains a sheet called "Data" which has a list of information in the column range 'B:H', starting at row '4'.The "SIP" workbook contains a sheet called "Staff" which has a list of unique ID's in column B, starting at row 4.What I'd like to be able to do is compare the unique ID's in column 'B' of the "Data" sheet to that of the unique ID's in column B of the "Staff" sheet. NB.
There could be multiple records for the same unique ID in the "Data" sheet, but they will only occur once in the "Staff" sheet.If a match is found, I'd like to copy the row from the "Data" sheet and paste this to a sheet called "Matched", starting in B4, also contained in the "Original Data.xls" file.As each match is found I'd like to paste this into the next available line on the "Matched" sheet, and I would like, if at all possible, for the macro to continue checking for multiple entries until column 'B' in the "Data" sheet is blank.
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"
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 (we’ll call it run_list.xls) against the values in two different workbooks (we’ll call these production.xls, development.xls). However after several days I am nowhere closer to solving this problem then I was when I started. I’ve 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.
Here’s 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.
I've attached two separate workbooks that have indentical sheets in each. One workbook is a 2 day later update of the first. I'm trying to develop VBA code that will open both workbooks uses column B (work order #) as the unique identifier for each like sheet (which represent a month) and compares all other columns for that unique indentifier. Then I need to generate 3rd workbook that is identical in format (has a sheet for each month) and content to the two source ones but shows the unique identifier and any changes:
Column A (due date)
Column d (order quantity)
Also,if in the newer workbook(5_12_06) there exist a unique indentifier (work order #) that was not in the older workbook(5_10_06) and the same if there exits a unique indentifier in the older workbook that was removed in the newer workbook.
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.
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)
View 2 Replies View RelatedI have two workbooks, each with columns titled State Code & County Code. I would like to pull data from one workbook into the other workbook using conditionals. (Copy/Paste will not work as the file I am pulling into has MSA Codes in the same columns)
The data set is numeric, i.e State 1, County 13
Here is the scenario. 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 programmatically 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 programmatically so when I click a button this comparison/highlighting takes place.
I want a macro to compare each value on workbook 1, Sheet "Destroyed", column "C" against each value on workbook 2, Sheet "ERT Master", column "B". For each instance where the value on workbook 1, matches a value on workbook 2, the row where the match is found on workbook 2 needs to be highlighted in yellow from columns A to H. This needs to be repeated for each value in column "C" of workbook 1.
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.
View 2 Replies View RelatedI 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.
I am trying to find a way to compare the cells (example: D4:D12 to the same cell range in another workbook. If the value in D4 is less than D4 in workbook 2 highlight it red and if it is greater highlight it green. I also want a loop to go through the specified range. Of course, the scale of the worksheets is much greater.
Another issue I foresee, is that the workbook name that I am comparing to changes every week, so is there a way to handle this change easily?
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.
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.
View 4 Replies View RelatedI have two separate workbooks, each with a great deal of information, such as name, ssn, dates, statuses, etc. I would like to compare the SSN column for both workbooks, and when it finds a match (which there would be many) to copy the MOS cell from the same row to the other workbook. Here is my breakdown with names:
Workbook 1: Macros.xls
Workbook 2: MASS 162.xls
The SSN column is column C in both workbooks
When there is a match found between the SSN column in Macros.xls and the SSN column in MASS 162.xls, I need to copy the MOS cell (column E) from the MACROS.xls workbook to the MASS 162.xls workbook into the same row, but in column M, which is currently empty.