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
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
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".
Is there a way I could achieve copying cell's content minus some characters?
For example in the cell A1 would be: "two apples"
I would put into the B1 cell something like =A1(-4charactersincludingspace) - the result in B1 should be "apples"
Is it understandable what I mean?
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.
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.
I am looking for a lookup to look for text within Column A that matches text in column D and brings the word in that is in column E ...
View 9 Replies View RelatedIs there a command out there that i can use to search a cell or textbox for part of a word, eg. my word being phone, and for it to look at cellphone and recognize it.
View 4 Replies View RelatedI 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.
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.
If I have the following in different cells of a worksheet:
ANFKLD
or
AAB
and so on
How do retrieve the second to last letter of any of these strings?
I have a spreadsheet that is sent to me monthly. Here is the issue:
Each workbook contains multiple tabs. The position of those tabs may be different positions when received.
The part of each tab's naming convention does not change, while the rest of the tabs' name contains random information.
My question: Is it possible to to search/find the tab based on part of the name?
example: Name on tab:
Pivot Table Data June 2011
Search for: Pivot
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] ......
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.
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.
I am trying to search partial txt with in data range.
View 4 Replies View RelatedI am trying to set a file name as a string to use it as an attachment. The user inputs a number into a textbox. That number is used to locate the file in location. But the file name is different with each file. So im trying to do something like
"C:Desktop*TextBox1*.docx"
Whereas the actual file name is "C:DesktopMCJC RFI 1 MISSING ROOM NUMBERS.docx"
The location "C:Desktop" will always remain the same as will ".docx". I have everything else working just fine.
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.
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 RelatedI 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:
i want to extract a value from a column into another column in excel.
this first column contains text and i want to extract a value that in located between the characters 'FOR' and 'MTHS'.
how do i do that?
the situation is the same for all the rows; the value that i want is just located between these characters.
is there a simpler way other than using the MID functn? because everytime i use it i need to count the no. of characters and its quite difficult to keep up with the numbers.
I am trying to make a summary from a table showing the newest value available for a set of items(1,2,3,4), this is according to an investigation date the summary table shoul display only the values for the investigation date, but if there are no values for the investigation date, them should take the previous recent date from the investigation date, and if there is not value at all, then shown N/A
I am attaching the excel sheet for a better ilustration of the problem
I tryed a combination of formulas that I tooked from other threads but is not working properly.
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.
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 ? ?
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.
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'.
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?
I have a monthly report. Worksheets for each month, each row is a type of fruit (column A), and the number sold (column B). A new worksheet is added each month with the information. I also have a Summary worksheet - running total of all months: Type of Fruit (column A), Number sold (column B), and Total of all fruits - number sold. I would like a formula for the Summary worksheet - to match that fruit (column A) to any of the other worksheets - matching the fruit (column A), and bring back the number sold (column B). (note some apples are Fuji and some are Honey crisp - those would be totaled together)
ex:
Tab = Jan 14
Fuji apples3
Bananas2
grapes9
TOTAL14
[Code]....
I'm trying to use VBA to pick records with a certain string in them using the following:
Code:
"SELECT * FROM tblIncidents WHERE tblIncidents.[Detail] LIKE %" & txtSearch.Value & "%"
But it's not working. It says it has a missing operator...
I have developed a functional User Form that works like a charm. The form will Add New Records to my database and Get Next and Get Previous records. This all works like a charm. I am stuck on how to search by Last Name or Employee Number and then to Update the Existing Record. If I change anything in an existing record as the VBA is written today, my only choice is to add a new record to the end of the database. I have added Command Buttons, but I'm lost as to how to write the code. Here's what I have so far:
VB:
Dim currentrow As Long
Private Sub cmdGetNext_Click()
Range("A2").Select
ActiveCell.End(xlDown).Select
lastrow = ActiveCell.Row
[Code]...