Tab 'Internal' is an example of our internal customers and how much they spent with us.
Tab 'External' is a list of companies in our area that we could do business with, it came from a marketing firm. Columns B:J contain my formulas. B is my lookup.
I need to match the two sheets together and pull the dollars spent from Internal to External. The problem is that the marketing firm did not use the same naming conventions that are in our systems. This means a simple Vlookup will not work.
Here is what I did so far: used VBA and formulas to break apart the company names in External, then using a modified Vlookup to match the words to the names in Internal. My accuracy rate is less than 1%.
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'm attempting to extract the Street Names from an Address.
For Example, if given "123 Very Big Street" I'd want to extract "Very Big".
If given, "123 Very Big" I'd want the same answer.
I've written code to do this, with a simple IF statement to identify mathcing last words like "Street" and "Drive".
How can I efficiently do this with a large list of last words? I'm drawing a blank on how to query against an array that would contain all these values.
What I am trying to do isn't very complicated. I started on some code, but it's just not good enough to do all of what needs to happen.
A B C
1 Cupcake Monster Aisle 5 Shelf H
2 The Cupcake Ghoul Aisle 2 Shelf P
3 Fred Baxter's Diary Aisle 1 Shelf X
4 Angry Cupcake Beast Aisle 3 Shelf A
5 Everyone Loves Cake Aisle 4 Shelf R
So I have a list of titles that I want to search for a particular string. The title in this list is the "proper" title,whereas the rest of my workbook typically uses a shortened version of the title. Sometimes the two will match 100%, but usually, they will be close.
If the string in the table above has more than 2 words, I want to use the first 2 words to check for a match. If the string has 2 words (will never have fewer), I want to match the first word. I want to do this IF there is no 100% match (if possible or reasonable). I will settle for just matching 1 or 2 words. I want to copy the values to another sheet and then delete the row that I originally got the data from.
What I want to do is search the list for "Angry Cupcake" and return the Aisle and Shelf location of Angry Cupcake Beast. There will never be any other "Angry Cupcake", so I am not worried about mismatches there.
Code: Dim SearchRow As Integer Dim StoryTitle As String[code]....
how to do and can get to work without issues is an exact match. This still leaves out all of the shortened titles though.
Code: Sub SplitApart() Dim data As String data = Sheets(1).Cells(20, 1).Text For Each EachSplit in Split(data) n = n + 1 Sheets(1).Cells(20, n + 1) = EachSplit Next End Sub
Error code when Debugging on "Split":
"Wrong Number of arguments or invalid property assignment"
How can I count the number of gaps (spaces) of a string? (Adding one we have number of words!). I need to save the position of each gap (space) on a array. How can I do it? With InStr()? The variable (string) that i'm working is GlbTargetRange.Value
I need to compare a list of keywords (stored in a string variable) with the content of a cell in order to see if any of the keywords do appear in the cell. I have to work with many such lists which can grow over time. What I'm doing is using InStr(cell with content, keyword) for each keyword. My goal is to have a macro which can automatically create the long formula from any list. My actual problem is that the formula I've build and stored acts more like a string. I'd like it to say either true or false. Anyway, here's a sample of what I came up with so far.
Sub build_formula() Dim i As Integer, j As Integer, m(20) As Integer, n As Integer, z As Integer Dim List As String, f(2) As String, s(20) As String, sp As String, Source As String Dim Formula As Variant List = "black, blue, green, red, yellow, white, z" Source = Cells(3, 1).Value z = InStr(1, List, "z") i = 1 n = 1 Do i = InStr(i + 1, List, ",") m(n) = i ..........................
I have three worksheets with the first worksheet having several dropdown boxes containing key words.
The second worksheet has three cells with different combinations of the key words and a fourth cell with equipment names. There are nearly one hundred entries in this format.
What I am trying to do is on the third worksheet to display the equipment name for which the three key words from the second worksheet match the user selected key words from the first worksheet.
I have a column of text strings on Sheet1, Column A, which I need to check for the presence of keywords listed on Sheet2, Column A
So if any word from the keyword list on Sheet2, Column A is found in, say, cell A2 of Sheet1, the cell to its right (B1) should have a formula to display the count of keywords found in A2. I also would like to see each keyword identified either through a highlight or a list. I need the formula to NOT be case sensitive and the match does not have to be for whole words).
I need to extract all instances of words that have format xnnnnnn, where x is an alpha character (letter of alphabet, to be precise) and nnnnnn are numbers. The words could something like u435586. The problem is I do not know how many instances of these words are in the string. The entire string is contained in a cell. A sample string could be something like:
This code is not accepting the values which is starting from strings like this
for example
bearing 15/16 IN LG, 1-1/16 IN OD, 11/32 IN THK,21/128 IN ID 19/128 IN LG, 2-3/64 IN OD, 1/2 IN THK, 5/64 IN ID steel 15/16 IN LG, 1-1/16 IN OD, 11/32 IN THK,21/128 IN ID 19/128 IN LG, 2-3/64 IN OD, 1/2 IN THK, 5/64 IN ID spares 15/16 IN LG, 1-1/16 IN OD, 11/32 IN THK,21/128 IN ID
I have a text string with 3 data to be extracted and then split throu range, but only specific parts is needed.
string is like that: Name Surname - Number : ################ - Exp : YYMM - Centre etc etc....
Lets say its allways happens in cell A3, for example: Adrian Kukuwas - Number : 1235467890123456 - Exp: 1009 - Centre... I would like a macro:
*that runs only if string starts with a letter, then if so *takes Adrian Kukuwas to cell D3 *takes 1009 (the exp) to B3 and makes it look like 09 10 (or 09_10) *in A3 leaving only the number 1235467890123456 text formated (sometimes the copy source formats the cell for bad)
I am trying to match the first few words in a spreadsheet with another column via vlookup. I tried [URL] ..... but it didn't work.
see attached spreadsheet. If column A and column N match up, I need a YES in column, otherwise a NO. I need formulas for the 4 first words matching, 5 words matching etc.
I'm if I can create a table of words that I want removed from a column.
For example:
Red Fast Cars Red Slow Cars Blue Fast Cars Blue Slow Cars
Can I setup a VLOOKUP function to remove 'Red' and 'Blue'
I know I can create multiple work sheets and use Control + H, to remove words. But that entails a lot of cutting, pasting, and filtering. Plus this example is simple, I'll be using longer text strings AND some examples might need to have 3 words removed from the same string
I am having some trouble getting a formula to work. I am building a report that pulls figures from a pivot table in another workbook. I am using a vlookup with match function to get the column index to find the relevant data I want. Where I need to add two columns together I am using sum, with the vlookup & match formulas nested in them e.g.:
Where: F13 = Employee number Column C on the pivot 156 workbook is where the employee number is based. The Match formula is then getting the column index from the column headings of the pivot table ie. "FAID"
This in itself works fine, as long as it finds a match in the column headings. This is where i get the error as in the above function "COMD" is not in the pivot table. However I need to keep it included as it may appear on a future pivot table. Is there a way of getting the sum function to complete even though later in the formula it can't complete the vlookup? So it will ignore it, or assume the value is zero if it can't find it? The formula probably needs to do this for all the vlookups as some headings may drop off in future pivot tables.
I have a list of customer names. These include one-off customers and companies. Unfortunately, they are not always keyed correctly and in the same format. What I need to be able to do is flag any companies. These will be those that contain key words, such as Ltd, Ltd., Limited, PLC, & Co, & Sons and so on.
My customer list may look like this:
1. Bill Jones & Son 2. Mr B Smith 3. Posh Homes Ltd 4. Posh Homes Limited 5. Mr A Singh 6. Bill Jones & Sons
I then need to flag no.'s 1, 3, 4 and 6 as Companies while 2 and 5 are flagged as non-company.
I have two lists, one that is 99,000 lines and the other is 150. I am trying to find out where the word(s) in the list of 150 is present in the list 99,000.
1st list of 99,000 is in Sheet1 A1-A99000 and the second list is in Sheet2 B1-B150.
The caveat is that in column A there is additional words in that cell so you can not do a simple vlookup, because there may or may not be an exact match.
I have been stuck for hours and the best i can get is to use the match function but it is not working because it is not exact. Last thing i want to do is use the CTRL F key to lookup all the words.
I want to make a lookup on a string of words from one of my worksheets and I also whant to make a hyper link so that when I click on the lookup command I should pop to the other worksheet were my database is.
I'd like to extract just "blue.red.yellow.green" to the following spreadsheet column, keeping the intermediate dots, but getting rid of the first/last words and their succeeding or preceding dots.
I've tried using RIGHT, LEFT, MID formulas unsuccessfully
I am trying to match data from 2 independent sets, formatted slightly differently so not sure which function would work best for me. From the attached file, I am trying to match the date and time stamp (in cell A1) with that from the other data (in this example in cell E1) and return the data (from cell F1) to cell C1. So basically any date and time stamp before 04/03/09 04:00 will return a value of 44 (this value should appear, therefore in cells C1 - C30)
The idea is try to search the words from Column C in Column A and if some key is found, show value from Column C (As a simple Vlookup but column A contains sentences):
A B C D
This is a test test 1 This is a tst excel 2 test case 3 tst 3 case for excel test 4 tst nº 5
Excel1.JPG
And expected result should be: (column B, contains the formula):
A B C D
This is a test 1 test 1 This is a tst 3 excel 2 test case 3 1 tst 3 case for excel 2 test 4 1 tst nº 5 3
Excel2.JPG
The idea is not to use. Column C could have more of 1000 entries.
Sheet B contains a column called "Name" and for each name, and for each name there are corresponding numbers. In sheet A, I have a "list of interest" in column A. What i want to do look through the data in sheetB to find a match from the list of interest and return the corresponding letters, located in column A.
I have filled out the first two rows of results that should be returned as an example.
One idea i had was to put a vlookup formula in each column result 1 to result 6 so i can catch all 6 "Serves" columns from column B, but there may be duplicates in the serve columns and vlookup only reports the first match.
