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.
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.
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.
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
I am looking for a macro to delete entire rows based on the a partial text contained in column A. For example, I have a list of names that I need to keep but I need to delete the rows in which column A contain cells starting with the following.
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 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.
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.
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
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 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".
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.
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'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.
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 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.
I'd like to adapt my macro so that it would insert a blank row after it detects the the first 16 characters of text as "'Closing Balance"; or it could even detect "'Closing" as the first 8 characters if it would be simpler
The Data Begins in Row 5 of Column A
My Current Macro is as below which I have adapted from another one I used
It is not working since it is detecting for the exact text "'Closing Balance" whereas the data registry would write "'Closing Balance as at 31/10/2009" , of which the "as at dd/mm/yyyy" portion would change every time a report is exported, but the first part "'Closing Balance" or even just the word "'Closing" will always be the same.
I have a data sheet which has a series of dates within it. So for eaxample the data sheet will have date of "01/07/2007" I then want to take this data and create the following field: "June 2008 Pre Data" The problem I have is when I try to join my variables togeother the code crashes (Invalid Procedure call or argument).
I have a cell in workbook X on Sheet1 (cell AB3) that states which columns should be exported from workbook X on Sheet3 to a new workbook. The value of cell AB3 on Sheet1 changes based on what a user selects in some check boxes on Sheet1. I would like my macro to read the value of cell AB3 and interpret it is a range reference of which columns to copy from Sheet3 into a new workbook. The problem I'm having is knowing what line(s) of code I would use in VB to read cell AB3 as a range and what kind of referencing rules I need cell AB3 to contain. Right now this is what the cell looks like to the user:
Code: Sheet3'(A:A,B:B,E:E,F:F).Select
For cell AB3 to look this way I'm just using some hard coded text, such as the sheet number and .Select, plus some concatenated values in between. Perhaps this text string needs to be modified, but I'm also wondering what I would use in my macro to reference the cell and read it as reference to which columns to select in Sheet3.
Is there a way to do a COUNTIF on just partial text?
I know how to count how many cells in a column contain the text "cars" for example, but how would i write a formula that include cells with other text as well?
For example if 55 cells have "cars" as a value and 20 cells have "fast cars" as a value
I would want the formula to count 75.
Basically, it needs to count how many cells cars appears in, in a specific column, regardless of whether its alone in the cell or part of a sentence.
In Sheet1, column Y looks into Sheet2 and returns the status of that specific order - the result displayed in column Y will be either blank or a variety of text strings (eg. received, pending etc).
I need to make a macro that looks into all the cells of column Y in Sheet 1 and copy/pastes as value into that same cell only if the formula in that cell returns text string "Received". It should not affect the other cells where the formula is returning either blank or a different text string.
However, whenever I run the code, if there's even on word that is bold in the cell, then the entire text string in the cell turns bold. How can I stop this from happening? i want to keep the format of the text string the same, only remove and replace the items listed in the code.
