Compare Strings In Two Workbooks

Jan 11, 2007

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

View 11 Replies


Compare Two Strings And Find Difference?

Aug 6, 2010

What I'm trying to do

A1 = Hello John, my name is Steve, how was your day?

B1 = Hello John, my name is Steve, was day?

I need C1 to return:

how your

Is this possible?

View 9 Replies View Related

VBA Compare Two Strings, Highlight Differences

May 18, 2009

how can I get the red highlighting to work like so?

Test Test_002 6698F ES6698F DVP3142 DVP3144 GTM8800 GTI8000 SDV394 SV384STC

View 9 Replies View Related

Compare Version Number Strings

Dec 13, 2006

I have two numbers, they are version numbers, I am having problems with my If then else and elseif statements.

59821 for version
is more than
5983 for version

even though is a greater version number. Do you see where my problem is. How do I compare # by # reading it, checking to see what one is bigger and going from there. so when it gets to the 3 of the 5983 its bigger than 2 of 5982 of the

View 2 Replies View Related

Macro: Match Compare Strings

Dec 16, 2006

I'm writing quite a large electronic logging program using excel and vba and have hit a snag in one of my subs which reads from a text init file. Let's take a look at the code.

Private Sub ReadInit()
'Opens txt init file, looks for target list, splits line by commas and enters into array

Dim FileNumber As Integer
Dim TargetsRead As Boolean, LastFileRead As Boolean

Set ObjFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = ObjFSO.OpenTextFile(SaveDir & Init)

TargetsRead = False
LastFileRead = False
Do Until objFile.AtEndOfStream 'do until end of file

View 4 Replies View Related

Compare & Identify Strings In Range

Jan 12, 2008

I am having no success using either: 1) COUNTIF with wildcard (*) or 2) Case Statements to find text strings in a cell. I've even tried using Copy/Paste Special values to change the way Excel 2007 interprets the field.

The Do While loop looks at target cells in Col. 18 which contains one or more specific last names. I want to put a text string (also a name) in Col. 19 IF the target cell contains the last name in the COUNTIF or Case Statements. The COUNTIF normally puts a TRUE or FALSE based on the condition, but I'd like to put the the last name based on which condition is met and then continue looping to Row 3. It's a small number of rows, less than 100.

Here's the Case statement ...

View 9 Replies View Related

Storing Long Strings In Workbooks

Mar 6, 2009

I need to associate a fairly long (800 characters) string with a workbook as a template SQL query for an ODBC connection. I've thought about putting it into a cell, into a shape, into a standalone VBA module (with some 'magic' to stop it compiling). All I need is to be able to access it fairly easily via VBA (which I'm comfortable with).

View 3 Replies View Related

How Can I Compare Strings To A Keyword List And Return A 3rd Value

Jan 24, 2008

I am trying to create my own budgeting tool. I have most of the tracker set up but I hit a stumbling block. I downloaded transations from Bank of America as a CSV file into excel. I want to categorize these transactions.

I created a keyword list that has words like "Starbucks" "Exxon" etc. Each of these keywords is then listed next to a category like "food" and "gas". So I want a formula that will compare the transactions to my keyword list and return the category type. Here is an example transaction:


I have a keyword "Starbucks" in f2 and "food" in g2.

View 9 Replies View Related

Excel 2007 :: Update Connection Strings In Workbooks?

Apr 7, 2012

I have a client with approximately 4-5000 Excel workbook files that all contain the old Jet connection string and these files do not run under Office 2010 without changing to the newer ACE connection string. It is just too much to open each file and change the connection string.

Is there a way to do a mass change - like global search and replace - the connection strings in the files? I have a text editor that is able to do that but I fear it may mess up the file.

View 3 Replies View Related

Compare Columns In Two Different Workbooks

Aug 22, 2013

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 Related

How To Compare Two String In Two Different Workbooks

Aug 4, 2014

I 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.

View 6 Replies View Related

Compare Data With Different Workbooks

Oct 11, 2008

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.

View 14 Replies View Related

Compare Information In Two Workbooks

Sep 28, 2009

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.

View 2 Replies View Related

Compare Two Columns In Different Workbooks

Feb 15, 2012

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 Related

Compare And Show Changes Between Two Workbooks

Jun 16, 2012

Comparing 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.

View 3 Replies View Related

Compare Worksheets From Two Workbooks

Dec 21, 2009

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

View 9 Replies View Related

Compare Two Different Workbooks To Populate Values?

Dec 26, 2013

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.

View 4 Replies View Related

Compare Cell Data Between Two Workbooks

Jul 7, 2009

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 Related

Compare And Copy Details Between 2 Workbooks

Dec 17, 2009

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
tLR = ActiveSheet.UsedRange.Rows.Count

'Find last row of source sheet
sLR = ActiveSheet.UsedRange.Rows.Count..........

View 14 Replies View Related

Compare Two Workbooks And Copy Matching Row

Jul 23, 2013

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.

View 4 Replies View Related

Compare Workbooks And Extract Data

Jul 3, 2007

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"

View 9 Replies View Related

Compare Data From 3 Workbooks Against 2 Files

Feb 15, 2010

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.

View 9 Replies View Related

Compare Sheets In 2 Different Workbooks And Note Changes

May 21, 2006

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.

View 2 Replies View Related

Compare Each Cell In Matrix. Different Workbooks

Nov 10, 2006

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


My 2nd input is xx2.txt shown below


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


View 6 Replies View Related

Compare And Merge Data From Two Different Workbooks

Apr 4, 2007

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.

View 4 Replies View Related

Compare Columns In Two Workbooks & Update One

Dec 15, 2007

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 Related

Compare & Copy Values Between 2 Workbooks

Feb 13, 2008

I 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

View 3 Replies View Related

Compare Text Strings In Two Columns And Return Text From Adjacent Cell

Feb 28, 2013

how to Chk the text string in particular cell, compare it with a super set column and get the full from of the text string from another corresponsing column and the output will be corresponsing full form of the chked text string?

View 6 Replies View Related

Compare Data In Two Workbooks And Highlight Matches

Jan 28, 2008

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.

View 4 Replies View Related

Macro To Compare Workbooks And Look For Matching Values

Dec 4, 2006

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.

View 9 Replies View Related

Copyrights 2005-15, All rights reserved