Search Partial String, Return Value In Next Column, Vba
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".
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 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 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.
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.
Here is an example of the data I get each day Letter order granting Sabine Pass Liquefaction, LLC's et al 4/16/12 request to add an alternate water source etc under CP11-72.Letter order granting Cameron LNG, LLC?s 4/5/13 filing of a request to introduce natural gas or process fluids into the BOG Liquefaction Project under CP12-15.Letter order accepting NorthWestern Corporation's 8/7/12 submittal of revisions to its transmission planning process to comply with the Commission's June 8, 2012 Order under ER11-2932.Letter order approving Public Service Company of New Mexico's 12/7/12 filing of a joint Offer of Settlement with Navopache Electric Cooperative, Inc under ER11-4534 et al. How can I set up my spreadsheet and what formulas can I use to search and return a value for each text string based on the attached table (column B)?
1) search column A for a string 2) as that string is found, move the entire contents of that cell to the same row, column F 3) insert a blank row above the row where the text was found 4) continue to search and perform the same steps
I was able to come up with the code to find a single instance and perform the necessary steps, but I've tried 'til I'm blue in the face to incorporate the FindNext object to parse through the entire worksheet. I thought I was on the right track with the attached code (which could be completely wrong), but it returns my MsgBox "Value Could Not Be Found."
Column A contains an alphanumeric string up to 50 characters in length. I need a formula to search the string in Column A for the word "Appeal" and place that word in Column B when found. Likewise for the words "Grievance" and "Complaint". If none of the three words are found in Column A, the contents of Column B should be blank. Here's the formula I've played around with (it works on the first line but returns #VALUE! on following lines):
I have an array 20 Rows x 42 Columns, which contains a competition draw.
I need to search this array for a unique value and return whatever the time is in the first column on the same row as the value appears, and enter it into column C in the Womens Times sheet.
I also want to return into column D the court number from row 3.
The reason i want this automated is as teams enter / withdraw we may need to drag the games from court to court to fill gaps, so i want the Womens Times sheet to update accordingly.
I have been messing around with index and match, but cannot quite get it to return what i need.
I have attached an example ... on the sheet "Womens Times" in column A there is a list of game numbers ( #1W etc etc) indicating womens game #1 and so on. The main sheet i am using also has a seperate tab for the mens games, hence the designator of W or M on the end of the game number.
I am trying to get a formula that will search one column range “B” and pull data from another, “D”. Dates are down column “B” and some of those dates are repeated several times. In column “D” there is only one piece of data (a number) entered for each day. Eg, if 08/06/2009 has been entered 3 times in column “A”, there will only be data entered in 1 of the cells of column “D” and blank cells in the other 2.
Column B -- Column D 07/06/09 -- 54000 08/06/09 08/06/09 -- 62000 08/06/09 09/06/09 -- 61000
I couldn’t get the LOOKUP function to work properly, as there are duplicate dates in column “B” and I often got a result of 0 returned.
As I’ve only got 1 piece of data added in column “D” per day and any duplicate days would just have blank cells in “D”, I can actually get a SUMIF function to work, SUMIF(B3:B60,DATE(2009,6,8),D3:D60). Although it does work, it doesn’t feel right using it and I would prefer a formula that would return just the one cell, instead of the sum of a range of cells.
Each Cell in Column A has a different long text string.
I need to see which (if any) of 10 specific small text strings exist within each long text string. Depending on which small text string is found I want to return a 3 digit code. If no small text string is found I want to return "Not Found"
E.g.: - Cell A2 contains "randomtext,randomtext,APPLE,randomntext" - I want to see if Cell A2 contains any of the words APPLE, ORANGE, CARROT. - I want to return "APP", "ORG", "CAR" or "Not Found"
Q: What is the most elegant way to accomplish this within a single formula that I could paste into each cell in Column B?
ok I have multiple columns where Col-A can have any number between 1 and 5. Col-B has a 3 digit number in it. i want to search all of column A and determine if there is a 1 in it, if so i want it to print the value of col b into a specified area.
this is basics, i will be using this to search col-a, determine what number is in the column, then print the number found in col b, on another page in a specific place, on the other page i have squares labeled 1-50. in a grid pattern, 10 squares per row. im trying to get all the entries in col-a, that are a 1, to put whatever value is in col-b in the first box of the grid, whatever is found in col-a with a 2, the value found n col-b of that row, into the grid box labeled 2 etc......
I have a value stored in variable A. I need to search one column for the location of that value and return the row value. there is no chance for a duplicate entry. Is a loop my only option, or is there a find command in VB?
I'm trying to create a spreadsheet using several others in several different formats. What i need to do is search for a text string eg "EVP" in one column and return the numerical value of the cell in the column next to it - then sum all the values and create a table in the new sheet eg.
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:
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
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 made this function that returns the string value for a column based on it's number but it starts getting more complicated after I've been thru the alphabet once and have to return two string values to move across the chart.
PHP Function colnum_string(ByVal num As Integer) As String colnum_string = "IV" If num = 1 Then colnum_string = "A" End If If num = 2 Then colnum_string = "B" End If If num = 3 Then colnum_string = "C" End If If num = 4 Then colnum_string = "D" End If If num = 5 Then colnum_string = "E" End If If num = 6 Then colnum_string = "F" End If If num = 7 Then colnum_string = "G" End If If num = 8 Then colnum_string = "H" End If If num = 9 Then colnum_string = "I" End If If num = 10 Then colnum_string = "J" End If If num = 11 Then colnum_string = "K" End If If num = 12 Then colnum_string = "L" End If If num = 13 Then colnum_string = "M" End If If num = 14 Then colnum_string = "N" End If If num = 15 Then colnum_string = "O" End If If num = 16 Then colnum_string = "P" End If If num = 17 Then colnum_string = "Q" End If If num = 18 Then colnum_string = "R" End If If num = 19 Then colnum_string = "S" End If If num = 20 Then colnum_string = "T" End If If num = 21 Then colnum_string = "U" End If If num = 22 Then colnum_string = "V" End If If num = 23 Then colnum_string = "W" End If If num = 24 Then colnum_string = "X" End If If num = 25 Then colnum_string = "Y" End If If num = 26 Then colnum_string = "Z" End IfEnd Function
Is there a better, more simple way to return the string value for a column from it's number reference?