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
ADVERTISEMENT
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
View Related
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
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
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 5.9.8.2.1
is more than
5983 for version 5.9.8.3
even though 5.9.8.3 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 5.9.8.2.1
View 2 Replies
View Related
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
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:
CHECKCARD 0256 STARBUCKS USA 00029801 GAINESVILLE VA
I have a keyword "Starbucks" in f2 and "food" in g2.
View 9 Replies
View Related
Feb 26, 2007
Got a worksheet that has list of football teams
Man utd
Man city
Tottenham hotspur etc.
in two seperate worksheet i download external data from 2 different website with some stats about teams. Problem is that theses websites user slighty different names for the teams ie. it will be man city from one source and manchester city from another. i need to somehow get the names to match up is there an easy way to do this?
View 2 Replies
View Related
Feb 7, 2014
Am trying to use index match to return value in column B based on matching 2 first chars on the left in column A.
Col A Col B
POS Majalt
POS Minalt
POS Instinctive
NB Viral
NB Sierra
NB Pierce
CLM Team1
CLM Team2
OS Tr1
OS Tr2
HR Tetra
HR Pentagon
IT penelope
Cell C1 : NBA MyFormula : {=INDEX(B:B,MATCH(LEFT(C1,2),"*"&$A:$A&"*",0))} <<== I need to find the match of NB only in range A:A to return the related values in Col B.
Expected Result:
NB Viral
NB Sierra
NB Pierce
Other than formula stated above, I've also tried with Isnumber(search), etc but all return errors.
View 14 Replies
View Related
Sep 3, 2013
I have a spreadsheet with 60k lines. One of the columns contains barcodes as per the example below, with a suffix of -XXX-XXX where the XXX are numbers. I want to highlight all the cells where the -XX-XX are the same number (as in the example below -303-303) how I can do this with a formula as I've around 30 spreadsheets each with 60,000 lines I need to sort through?
2500071189898-303-302
2500071189898-303-303
2500071189898-303-304
2500071189898-303-305
View 4 Replies
View Related
Aug 1, 2007
I have a SS with 700 rows. 2 columns (Col K and L) may or may not contain text in their cells. If the cells do have text, I want to check for the presence of keywords which could be anywhere within the text, and if I find match write the matching word in Column I. Example: I want to seach for the following strings Loaned, injured, suspended etc ... in the range the K4-K700. Lets say Cell K50 contains the text Injured - broken bones. As the word injured is a match I want to write the word injured to cell I50 In addition if Cell K50 has text and Cell L50 does not, i want to put the text "NEW" in I50, and the reverse if Cell L50 has text and Cell K50 does not i want to put the text "recovered" in I 50. Following formula (in each cell in Col I) does this job currently. Can't figure out how to redo this in VBA, and wondering if checking the 700 rows will cause Vba to be slower.
=IF(OR(TEXT(LEFT(OFFSET(I22,0,2,1,1),6),"DDDDDD")="Loaned",TEXT(LEFT(OFFSET(I22,0,2,1,1),6),"DDDDDD")="Transf"),
"Loaned",
IF(ISNUMBER(SEARCH("susp",OFFSET(I22,0,2,1,1))),
"Suspended",
IF(AND(ISBLANK(OFFSET(I22,0,2,1,1)),ISBLANK(OFFSET(I22,0,3,1,1))),
"",
IF(ISTEXT(OFFSET(I22,0,2,1,1))=ISTEXT(OFFSET(I22,0,3,1,1)),
"Ongoing",
IF(ISTEXT(OFFSET(I22,0,2,1,1))=TRUE,
IF(ISTEXT(OFFSET(I22,0,3,1,1))=FALSE,
"New"),
"Recovered")))))
View 2 Replies
View Related
Aug 27, 2009
I am trying to match some strings, to see if an entry is already included in a master list or not. I'm running into problems with some cells that have the EXACT same string, from what I can tell (a text ticker symbol) yet a MATCH or VLOOKUP won't return anything. Has anyone ever encountered anything like this? Worksheet attached with an example
View 3 Replies
View Related
Jun 15, 2009
I sent this before but the formula I was given wasn't correct (as you will see from the file). In Column B of the second sheet (Data) of the attached file, I want to bring back the relevant name form the first sheet (List), which involves searching through the long descriptions from column A on the Data sheet for the name on the List sheet.
View 2 Replies
View Related
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
Nov 12, 2009
I want to compare the colour column in book1 to the colour column in book 2. If I find a match I want to take the id from book1 and port them over to book2. How do I go about doing this?
View 10 Replies
View Related
Oct 16, 2012
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).
Example of the sheets:
Sheet 1:
Name
Food Safety
Smith,John
Walker,James
Stratton,William
Sheet 2:
Name
Food Safety
Christian, Marie
03/03/1994
Harker, Stuart
01/06/1995
Walker, James
05/06/1997
View 9 Replies
View Related
Mar 5, 2014
If i has this table name delivery
Table name is delivery and goes from a to p and 1 to 621.
View 5 Replies
View Related
Mar 19, 2014
I've been trying for a few time to make a sub, on VBA, that writes, in a specific column, " " or 1 depending on the date that is inserted. The thing I can't quite do is:
1 - I have a sheet where the different projects of a company are stored (name, starting date, ending date and person responsible) and where new projects can be added from a UserForm
2 - I also have another sheet were costs from the projects are stored (one line with cost 1 from project 1, another with cost 2 also from project 1 and cost 3 from project 2, etc) and where I can also add a new cost with another UserForm (this cost as also a date that is added, to when it happened)
3 - What I need to do is, on the costs' sheet I need to create a sub that in the last column adds me 1 if the date of the cost added is not a date between the starting and ending date of the project that it corresponds.
View 1 Replies
View Related
Jul 4, 2013
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.
View 3 Replies
View Related
Jul 4, 2013
I have column A of 700 usernames and column b with 85, I want to compare b with a and if appears in the list place a 1 in column C
View 1 Replies
View Related
Nov 7, 2013
Need vba to do the following: Compare 4 Columns for a 100% match: B, F, N, K.
IF ALL 4 columns] contain matches then, it is considered 100% match and can proceed with other code.
'Below is an example of how it should perform the COMPARISON:
'1. Compare what NSN's exists in B3 and B4:
'...If = MATCH, proceed to check critical column F for matches
'...If = NO MATCH, proceed with 100% FAIL rule.
'2. Compare what NSN's exists in F3 and F4:
'...If = MATCH, proceed to check critical column N for matches
'...If = NO MATCH, proceed with 100% FAIL rule.
'3. Compare what NSN's exists in N3 and N4:
'...If = MATCH, proceed to check critical column K for matches
'...If = NO MATCH, proceed with 100% FAIL rule.
4. Compare what NSN's exists in K3 and K4:
...If = MATCH, proceed to the set of rules used to perform updated and merging to these 2 rows.
...If = NO MATCH, proceed with 100% FAIL rule.
PROVIDE MESSAGE:
IF all four match, THEN,
MsgBox ("100% Match")
IF at any point along the way, one of those columns does NOT match, THEN
MsgBox ("NOT a 100% Match")
(for example, if it looks at Col B - finds match, it then moves to F and if F does not match, stop and provide message (no need to continue comparing the last 2 columns)...
View 5 Replies
View Related
Jun 5, 2008
I have 24 cells I want to compare to another 24 cells. I want to compare cell G5, 7, 9, 11, 12, 13, 15, 20, 22, 24, 26, 28, 30, 35, 37, 39, 41, 43, 45, 50, 52, 54, 56, 58, 60 with cell K5, 7, 9, etc. Cell G5 has to be compared to K5, G7 with K7 and so on. All the cells consist of is 1, X or 2. I want excel to count the number of matches and write it in cell G63.
But since I am a total noob when it comes to excel I have no idea how to do it.
View 9 Replies
View Related
Mar 27, 2009
I have two spreadsheets SP1 and SP2. I need to match values in column E of SP1 with values in column A of SP2. If I find a match in SP2, I need to find a value starting with 'TC_' from the previous rows and get the corresponding value in column B of SP2 and paste in column F of SP1.
For example, E1 value in SP1 matches with A3 value in SP2. So I need to find the row with value TC_AM01_axs_fhgn and get the corresponding value in column B AM01 and copy it in column F of SP1.
The two example spreadsheets are given below. The values in SP1 sheet has expected result in column F in red.
SP1
SeriesS.NoItem #CaseReuse CompScenario #A1abc001_CTC001AM01B2bcd002_ATC002AM02C3def003_BTC003AM03D4efg004_DTC004AM04E5ghi005_ETC005F6hij006_FTC006G7lmn007_GTC007H8opq008_HTC008I9rst009_ITC009J10uvw010_JTC010K11xyz011_KTC011
SP2
Case #SubjectTC_AM01_axs_fhgnAM01TC001asdhagsdjharteuTC_AM02_axs_fhgnAM02TC002asdhagsdjharteu12sdfsiu786dfgg88hdcfs676566532kjjjiTC_AM03_axs_fhgnAM03TC003asdhagsdjharteuTC_AM04_axs_fhgnAM04TC004asdhagsdjharteu
View 10 Replies
View Related
Apr 10, 2007
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.
View 3 Replies
View Related
Jun 29, 2007
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.
View 4 Replies
View Related
Dec 20, 2012
I need to do a vlookup that takes a string from one cell and then tries to find that string (embedded in a larger string) in the table array
Essentially I imagine this involves the FIND function at some point.
Attached is an incredibly simplified example of what I'm looking for.
View 9 Replies
View Related
May 6, 2014
I am trying to compare two fields using the INDEX MATCH formula. One field needs to match exactly and the second field should return the closest match to a date. I have attached an example where I am trying to find the closest production date (column F) for the matching ID that is before the complaint date.
View 8 Replies
View Related
Mar 5, 2014
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.
View 14 Replies
View Related
Dec 16, 2009
I have a sheet (name “master”) that I need to compare against data in a table. For each row, I am looking at two columns of information that I need to compare against the data table. The columns are (1) “State of Sale” and (2) “Associate ID”. I need to determine if the associate is allowed to make sales in the state that is identified, which requires looking at the data sheet.
This steps I need to take are as follows:
1 – grab the associate ID and determine if it is in the data sheet. If the ID is not in the sheet, then “No record found”. Else, go to next step
2 – if the ID was found in the data sheet, then the next step is to grab the “State of Sale” and compare against the data table. Go to the row of ID in question, and then go to the column of the State code in question. If there is an “x” in the intersecting cell, then sale is “ok”. If there is not an “x”, then “sales violation”.
View 2 Replies
View Related
Jan 3, 2013
I have two cells with values that are strings.
I would like to know of a macro that can compare each two cells(start from E2 and compare with E3)to see if the values are NOT equal.
If they are equal, do nothing and if they are NOT, highlight the value.
Example:
compare E2&E3, E4,E5,E6,E7,.... if for example E2&E3, E4,E5,E6,E7,.... each hat same string, do nothing, but if they were not the same highlight it with red.
This code checks two cells and highlights one cell when there is duplicate, I like the opposite one, but don't know what to change!
Code:
Sub CompareCells() Dim r As Range, cell As Range Range
("E2", Range("E" & Rows.Count).End(xlUp)).Name = ("MyRange") Range("MyRange").Select
' clear all colors from selection Selection.Interior.ColorIndex = xlNone
' loop through cells and compare Set r = Selection Set r = Selection.Resize(r.Rows.Count + 1)
For Each cell In r If cell.Offset(1, 0).Value cell.Value Then
cell.Offset(1, 0).Interior.ColorIndex = 3 End If Next End Sub
View 3 Replies
View Related