Partial String Vlookup
Nov 25, 2008
I have two columns Cust. Phone and State. The Cust. Phone column has the full 10 digit number. Some with the preceeding 1 and some without. The vlookup will sit in the State column. I am trying to perform a lookup using the area code.
I have tried the following but always recieve #N/A.
=IF(LEFT(CELL("contents",A2))="1",VLOOKUP(MID(A2,2,3),D2:E8,2,FALSE),VLOOKUP(LEFT(CELL("contents",A2),3),D2:E8,2,FALSE))
It makes sense if I walk through it but fails to work.
I am trying to use the HTML maker to upload an example but I'm running into problems. I'll update ASAP.
View 9 Replies
ADVERTISEMENT
Apr 9, 2014
I'm trying to lookup a 10 digit number against a string of numbers seperated by commas. And then return the Carrier Name and On-Time
LOOKUP TABLE
Order #(s) Carrier Name On-Time
5082940535,5082940507 Freight Lines Yes
5083055781,5083056150,5083056098 Ocean X No
Order # Carrier Name On-Time
5082940535 ? ?
5083056150 ? ?
5082940507 ? ?
5083056098 ? ?
View 5 Replies
View Related
Aug 18, 2014
I have a list of items in Column A and size info on Column B i want to being in the info to another sheet with a vlookup but the problem is that I have a lot of items with different colors so after the item no. it has a dash and a letter or two for the color so i want to bring into my new sheet all info.
I have in my old sheet for all items regardless of color so for instance in my old sheet i will BR1000-EM and then in my new sheet i will have BR1000-R and BR1000-SA and BR1000-YC how do i make a vlookup it should only lookup the values in both sheets only till the dash (i cant use a certain no. like left,6 because the item no can have more then 6 but it always has a dash when it has a color code) also not all items have dashes so the dash is not always there but when its there i would like that the lookup should stop by the dash.
View 6 Replies
View Related
Mar 31, 2014
I am trying to do a sort of index match thing using VBA. What I am attempting to do is to use the prefix of a long number and try to find that exact prefix in a string array, and output that string array value. So this is what I have so far as a test:
[Code].....
So I can match the text exactly so if I put PREFIXB in cell A1 in this example, i will get the msg box saying "YES", but if I make it PREFIXB1231k4j3jj1kj32cj, it will display "NO". I want to get it so that PREFIXB will be displayed in the cell that I put the formula in. So if A1 = "PREFIX1AAA100CF" and cell B1 = "=ABC(A1)", cell B1 will display "PREFIX1AAA".
Now the thing is that these prefixes can have different lengths, but will never encompass the exact prefix of another. So if I had a prefix of: PRE1AB, I won't have a prefix of PRE1A.
View 2 Replies
View Related
Jun 13, 2013
Working with a scheduling report and trying ultimately to get it down to a line item report with the associate information and their earliest start and latest end for the week. Already made a lot of fixes to the formatting involving junking empty cells and trash data, but running into a road block on some of the trash data.
Here is the format of the report at this point. Names have been changed to protect the innocent.
Agent: 2366 Bacon, Kevin
Date
Start
End
[Code].....
So what I need is something that will iterate through the report...such as an while and find the cells in column A that start with "Agent:" It needs to compare that cell to a stored value to see if this "Agent:" is a repeat. If it is then it needs to delete that row and the next one and shift up. If it is not then it overwrites this "Agent:" with the previous one in the stored value and continues the while.
I know what I want to do...but not shure on the code cause I am not great at VBA.
Oh...if it matters probably need to allow for about 10k line items to iterate through...right now report is at 6k...but allow room to grow.
View 6 Replies
View Related
Sep 5, 2007
I need to do a lookup in a column for part of a string and return the adjacent value in a different column. Works fine using VLOOKUP as seen in the following example except that the return value is being truncated.
Lookup range in column A:
A001,A002
A003,A004
A005,A006
I need to find A001 and return the value in column I of the same row. I am currently using {VLOOKUP("A001", LEFT(A1:I500, 4), 9, 0)} This does work entered as an array formula but it is only returning the left 4 characters of the column I value. I need to lookup the left 4 characters in the lookup range but return all the characters in the return range.
View 2 Replies
View Related
Jan 30, 2013
I haven't had the need to work with partial strings till now and having difficulty finding the right context in other threads. I need to put the first 4 characters of one cell into another cell. The line in the below code with the comment is the one I need. It's the only one where I need only part of what is in the cell.
It should be = the first four characters of cells(zRow, "A")
Code:
Dim LastRow As Long
Dim zRow As Long
Dim cRow as Long
LastRow = Sheets("Datasheet").Range("N65536").End(xlUp).Row
zRow = 1
cRow = 2
[Code] ......
View 1 Replies
View Related
Feb 12, 2013
I have a comment that I'm putting into a variable for the purpose of obtaining the persons name.
Cell C3 (in this example) just contains their employee number, but it's comment contains...
Tech ID: 123456
Name: John Doe
The Tech ID is variable from 3 chars to 6 chars and obviously the name is size is also variable. The end result will be a variable with just the persons first and last name extracted from the comment.
Code:
Dim mycmt As String
Dim techname As String
mycmt = Sheets("tech").Cells(3, "C").Comment.Text
techname = Right(mycmt, BIG-OLE-BALL-OF-CONFUSION)
MsgBox techname
My 2nd question. This is the 3rd time in recent days that I've needed a partial string and Mid, Left, Right, Len, Find just boggles my mind.
View 6 Replies
View Related
Oct 21, 2008
I want to write an macro where it searches for text in a column, but the text may be a partial string. The text is in column B, with account numbers in column A, like this:
Column A Column B
1100 Jay
1101 Jack
1102 Jackson
1103 Jacksony
1104 Jefferson
For example, I want to search on Jack in column B. In the above example, I want it to find accounts 1101, 1102, and 1103, and to copy that information to another spreadsheet, say, sheet2.
If possible, I'd like it to copy the first account number and name, 1101 Jack, and if that's not what the user wants, they click next and they see 102 Jackson and so forth.
View 9 Replies
View Related
Jun 3, 2014
I'm working on a formula to make it enable a part of the text then return the best possible match. Below is my formula
=MATCH("*"&$A11&"*",'[Customer Master List - 05.30.xlsx]Export Worksheet'!$B$82:$B$1298,0)
However, it works with some text but won't work for some.
For example, I have this text CARE-A-LOT, INC and in the master sheet there is a similar text like this CARE-A-LOT. I want it to return CARE-A-LOT as this the best match possible.
View 2 Replies
View Related
Jan 28, 2014
I'm trying to come up with a VBA function that would return cell values based on another cell value.
I can do this by comparing a range (column of cells) with the cell value to match and then return the value of the cell next to it.
For instance,
The function should search a column for partial text, as follows:
TS ID
PDT ID
TS 1.1
PDT 1
TS 1.2
PDT 2
TS 1.3
PDT 3
TS 2.1
PDT 4
TS 2.2
PDT 5
TS 3.1
PDT 6
TS 3.2
PDT 7
In the above table, the function should for partial text and return string as follows:
TS covered
PDTs
TS 1.1, TS 1.2, TS 1.3
PDT 1, PDT 2, PDT 3
TS 2.1, TS 2.2
PDT 4, PDT 5
TS 3.1, TS 3.2
PDT 6, PDT 7
So basically I am searching for partial text TS 1. and so on
View 4 Replies
View Related
Sep 30, 2009
I tend to stumble when trying to pull certain characters from strings, not sure how to stop and start my search.
View 4 Replies
View Related
Dec 10, 2009
I have 2 columns A and B. The data is in column B.
What I'm trying to do is delete entire rows from my current selection if they do not begin with mailto:
View 9 Replies
View Related
Feb 13, 2008
I would like to search cells in column D for the partial string, "PIPE," (A full string may look like this: 'PIPE, 24"ODx0.375"WT API-5LX-65,ERW OR SMLS'). Then, if it's there, return the value "LF" in the corresponding cell in column C. If that string isn't found, then I'd like it to return "EA".
I know this seems pretty easy, but there's a small problem. The word "BENT PIPE," could be in Column D, in which case, I would want it to return "EA" instead of "LF".
View 3 Replies
View Related
Feb 20, 2008
I have created a Userform for entry data's in a Excel2003 file. I would like to retrieve a complete record (= a row) by searching on a string contained in a cell of this searched record-row. Here an example of a row and I'm searching upon "2041" via an inputbox.
idn° claims Dateréf. Suppl supplier Fax n° PO
1AAA2041 2/01/08200039 BOSCH (RAS) 056/20.26.75 774634
View 9 Replies
View Related
Oct 6, 2008
I've got a group of data within a pivot table that I want to be sorted based on only part of the string. For example,
Standard Kitchen
Standard Master Bath
Standard Hall Bath
Optional Kitchen
Optional Basement Bath
First they would be sorted by whether they begin with Standard or Optional. Then within those groups, they would be sorted whether they contain Kitchen anywhere in the string. So that the order would be Standard Kitchens, Standard Anything Else, Optional Kitchens, Optional Anything Else.
View 3 Replies
View Related
Feb 15, 2010
I've posted this query before, not on this forum, but I don't think the replies I've had so far are going to do what I want. Initially I was looking for a formula, but the suggested pile of nested IFs won't work for the number of conditions. I saw a previous post on here for a VBA macro to search for a text value in a cell against the cell contents of a range and it seemed to do at least the first part of what I wanted. I attempted to manipulate it a little to test its applicability for my own nefarious purposes but for the life of me I can't get it working.
This is complicated by the fact that the actual data is commercially confidential, so I can't show you the actual file, but I can fake what I want with two simpler ones. I've attached them to this post. What I want is a fair bit more complex than the other post I found - I want to be able to compare a partial text string from a given cell in a range ('Check Value' in the attached TestBook2 ) against the strings in a range of cells ('Value 1' in TestBook1), and return the corresponding value from 'Test Value' to the corresponding adjacent cell to the tested 'Check Value', with an order of precedence, for example...
Testbook2 contains an entry in C5 of 'a, e, h, z, x, y'. Testbook1 shows that the return for a, b, c, or d is 'moo', for e, f, or g is 'steve' and for g through q is 'fred', all others being no returned value. Moo>steve>fred, so I want the corresponding 'moo, steve. fred or <blank>' cell to contain 'moo'. Conversely, C6 contains 't, u, z' and therefore shouldn't have a value in 'moo, steve, fred or <blank>'. C12 contains 'f, z, s, y, u' and C15 'i, x, z, s', and therefore should display 'steve' and 'fred' respectively.
View 3 Replies
View Related
Jun 30, 2006
I have two questions:
a. Check the code below:
Dim news1 As String, news2 As String
news1 = "new"
news2 = "polygon"
Dim countie As Integer
For buddie = 2 To b1.UsedRange.Rows.Count
'If b1. Cells(buddie, 1).Value Like "new*polygon" Then
If b1.Cells(buddie, 1).Value Like news1*news2 Then
countie = countie + 1
n1.Cells(buddie, 10).Value = "test"
End If
Next buddie
I have 2 strings, news1 and news2, i need to use the like function to check the occurance of these two strings in all the cells, and just for testing purpose, im printing 'test' it out in another excel sheet.
How do i make that work? the commented line shows what exactly i want, only that i dont want to hard code the string values.
b. If i have a value in the cell:
/new/blahblah/anycra/polygon
how do i split it so as to assign two variables to have 'new' and 'polygon'.
View 9 Replies
View Related
Mar 22, 2007
I have a simple lists containing two columns. One column contains a five digit number and the other a vendor name. The vendor name in most cases is two to four words. I am wanting to type in a partial string of the vendor name and it return to me the 5 digit 'vendor' number.
Col A Col B
20567 3M Electrical, Inc.
I want to type in '3m' or '3M' or 'electrical' or 'ELECTRI' and it return the 20567. The other part of this is that there may be two rows with the same info in which I would need to see both...
Col A Col B
20567 3M Electrical, Inc.
21789 3M Tape Division
Is this possible with standard lookup features in Excel or does someone have a VbScript or macro that will accomplish this?
View 9 Replies
View Related
Nov 13, 2009
In cells A1:A10 I have the following list;
FLOUR
DOUGH
CHICKEN
BEEF
PEPPERS
TOMATOES
CHEESE
ONIONS
POTATOES
SALAMI
I want to be able to get a partial match that will allow for spelling mistakes by the user. The wild card (*) seems to only look at the left-most text and return #N/A if the text isn't matched sequencially.
For example ....
View 12 Replies
View Related
Sep 21, 2002
How do you do a vlookup with a partial match?
View 5 Replies
View Related
May 6, 2007
I have a worksheet with 2 tabs. Tab 1 has data in two columns, column A contains a part number (6 characters long) and column B contains a value for each part number.
Tab 2 is a sheet with multiple colums of data. I need tab 2 to look at the data in tab 1 and display the value of each part number in a specific column that has corresponding part numbers but are 15 characters in length. The part numbers in tab 1 are the last 6 characters of the part numbers in tab 2.
I have been trying to use vlookup in tab 2 but it won't work because the column of part numbers in tab 2 is a full part number and the part numbers in tab 1 are only a partial part number (last 6 characters of part number in tab 2).
Tab 1 is a worksheet that is created from a Access query of a sql database. I cannot control the abbreviated part number that the query generates.
Tab 2 part numbers (the full 15 character number) also is generated from another access query which I also can't control.
Is there any way I can have tab 2 sheet look at the data in tab 1 and display the values for the part numbers in tab 2.
View 9 Replies
View Related
Jan 17, 2013
I do PPC work and you can export query data from Adwords with Column A being the actual search query. Then column B - E are impressions, clicks, cost & conversions (from each query). In this example, cell A1 might be running shoes, cell A2 might be walking shoes, cell A3 is running pants, and cell A4 is baseball pants. I want to add a new column (F) that does product category groupings based on partial text matches in column A. For example, anything that includes the text string shoe or shoes should be labeled as shoes in column F. Similarly, all queries that contain the characters pant would be listed as product category pants in column F.
Currently I do this manually by conditional formatting on column A for contains text and then color the cells. Then I sort by cell colors. Then I manually type in the product category into F for each color block in A. Needless to say, this is slow and manual.
What I would prefer is to have a master table (like a vlookup) on sheet 2 where column A is the list of partial text matches and column B is the product category to be returned if that the partial text in sheet 2 column A is found in the query list on sheet 1 column A. This way, on sheet 2 I could have cell A1 sneaker, cell A2 shoe, cell B1 shoes, cell B2 shoes, etc to manage the correlation between text strings and product category groupings.
Basically I want a formula to put in the cells on sheet 1 in column F that searches all of column A on sheet 1, looking for partial text matches from column A on sheet 2 and returning corresponding product category in column B on sheet 2.
View 6 Replies
View Related
Mar 15, 2007
Cell H1 has a variable string of references for eg
"FI570783AQ3516346EQ3516346FXVB123456"
I want to return the reference beginiing with "FXVA" so it would ignore the rest and only return FXVB123456 - this should always be 10 characters.
Just to add another complication to the mix, there may be 2 "FXVA" references in the string and i want to get both (these can be in the same string so FXVB123456FXEL123456 - but this might not happen regularly.
View 9 Replies
View Related
May 20, 2014
This is for Excel 2007,I have two sets of model numbers. One set is the full model numbers of the units we use, and the other is an abbreviated form used to lookup up certificate numbers. I need a way to match these up so I can use one set of search criteria to find out if there is a match. Here is an example of what I need to match with a partial text match:
H,AE35(6,9)36+TD and AE3563636D145C2501AP
H,RE36(6,9)36 and RE36936C145B2505AP
if I could do this with a formula that matches multiple items at the same time (ie; if A & B & C match=true) with the above model number being one of those items (certificate numbers are issued for sets, but the other model numbers are fine).If that's not possible, a one time VBA run to match all of the abbreviations at once would also work. If these items are matched up with a one time VBA, the VBA needs to account for their being more than one match for each abbreviation depending on the size of the unit.
So H,AE35(6,9)36+TD could be matched to:
AE3563636D145C2501AP
AE3563636D175C2501AP
AE3563636D210C2501AP
View 11 Replies
View Related
Apr 8, 2014
I have a standard lookup table in Sheet1 with all the zipcodes and FIPS codes for the counties in 3 states:
Col-A.....Col-B.....Col-C
Zipcode..County..State
19316........029.....PA
19317........045.....PA
In Sheet2 I have data exported from a database from which I need to generate columns of county codes and states in a third worksheet.
The problem is, most of the zipcodes in Sheet2 are 9-digit and some are 5-digit:
Col-F
Zip
191493712
19133
191203510
I can extract the 5-digit zipcode using LEFT, but I can't get it to work in a Vlookup formula in a single formula.
I tried creating an intermediate column to display just the 5 digits, but when I reference that in a Vlookup formula I get N/A. I can copy that intermediate column, paste values, and convert to number and it works fine in the Vlookup (but the end user is never going to be able to do this).
How can I--in one formula--extract the first 5 digits from each cell and use that string in a VLookup formula to display the proper FIPS codes?
View 2 Replies
View Related
Apr 13, 2012
My sheet contains values like these:
A/5
A/3
B/4
What I want to do is sum all the values after the / for a particalur value before the /:
A 8
B 4
I already tried VLOOKUP, but this function wants you to point to a certain cell to return instead of a just a part of the cell. It should return 5 for the first row to be able to perform a sum for every value of A.
How can I perform such a calculation without having to copy the values to separate cells? Can I contain all of this in one function?
View 5 Replies
View Related
Oct 4, 2012
Im sure this cant be done because it defies the logic of the LOOKUP function but I know there are some clever people on here so i thought id ask!!
I have 2 workbooks containing similar data from 2 different sources; the one common data item in both is a clients address but have the age old problem of data quality, which needs sorting eventually but i need a quick fix!
In workbook one for example the address will be recorded as RUSKIN RD
In workbook two the address will be recorded as RUSKIN ROAD
A simple look up wont work obviously as the 2 values dont match, then i thought of using the LEFT function but again this goes against the logic of the VLOOKUP.
View 2 Replies
View Related
Nov 17, 2009
I am wanting to look for a string of text within the "lookup_value" that matches the text that's in the "table_array".
View 9 Replies
View Related
Feb 12, 2010
I have a issue with VLOOKUP and LEFT STRING ETC I have a lookup table see below
AB10 Grampian
AB12 Grampian
AL8 Anglia West
AL9 Carlton LWT
B1 Central West
B10 Central West
B11 Central West
B13 Central West
B14 Central West
B15 Central West
View 9 Replies
View Related
