The formula cant always match words from the arrayand gives me N/A
I have attached the formula i am trying to use . Is there an earier way anyone can recommend. Im sure im over complicating things
Take a look at the attached workbook.
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 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 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.
See attached file, "Rate Sample Index-Match Formula".
I need a formula to return the value at the cross section of two (2) lookup values that match. This formula will be input into column D under, "RATE" on the 1st tab, "TEST FILE".
In the 1st tab, "TEST FILE" there are a series of columns as follows;
A = Service
B = From
C = To
D = Rate
In the 2nd tab, "RATES" there is a series of rates with drivers From (green) & To (blue)
The formula needs to do the following;
1. Lookup the "From" value in column B on tab, "TEST FILE" and match to column B2:B59 on tab, "RATES" both highlighted in green
2. Then Lookup the "To" value in column C on tab, "TEST FILE" and match to row C1:BH1 on tab, "RATES" both highlighted in blue
3. Then return the value at the cross section of the match "From" (point 1 above) & "To" (point 2 above) in range C2:BH59
For Example;
The rate From SYD To CBR = 0.33. I have highlighted this in yellow on both tabs to show where the formula needs to lookup the data to return the answer.
Additionally, if we were to add service as an additional lookup match how would this work?
In sheet 1, I have
Col A Col B
John Sedgwy - R
In sheet 2, I have the following names
Col A Col B
Peter Walker
John Sedgwicky
When I did an array match -> ={MATCH(1,(TRIM($a2)=Sheet2!$A$1:$A$2)*(left(b2,5)&"*"=Sheet2!$b$1:$b$2),0)}
gave me an error but when I did an individual match to both John and Sedgw, it works.
Can we unmerge a complete address in seperate seperate coloumns? Like Complete address is "1st Floor, 2a Harrison Road, Erdington, West Midlands, B24 9AA" and i want to split it like Address[1] "1st Floor" , Address[2]: "2a Harrison Road", Address[3]: "Erdington"...
Is there is any formula which can split data with the help of seperators (Comma or space) ??
EXCEL FORMULA CHALLENGE:Needs a formula like as above , expand the formula to generate from One to Nine Hundred Ninety Nine Thousand Nine Hundred & Ninety Nine? 1 to 999,999,999 all numbers into words between this range ?
View 3 Replies View RelatedI have a column of words in Column A and I want to replace all the times that these words appear in the rest of the excel sheet with the words in Column B. If someone has already answered a similar problem link me to the thread because I can't find anything.
View 5 Replies View RelatedI need a formula to count the total number words occurred in an array. It has to separate the "," and count.
Here is a scenario:
1.Apple
2.Banana
3.Orange
4.Mango
5.Banana
6.Mango
7.Orange
8.Apple, Banana
9.Banana, Mango
10.Mango, Apple
11.Banana
Excel. Let's say this how a column looks like:
a
b
b
c
d
Is there a formula to get this outcome:
a 1
b 2
c 1
d 1
If not, is there a way to create a list of words that are in a column?
I'm trying to find a formula to add spaces in between capitalized words
example :
cell A1 = MtVernonRoad
trying to make it say..
Mt Vernon Road
What I need is a formula for the following:
If Column A equals "No Show" and Column B equals "Anthem" then sum of cell C2 divided by D2
Every time I input this simple little formula I get an error.
A
B
C
D
Status
Coverage
Counts
No Shows
No Show
Anthem
5
10
If I have ABC DEF in a1, is there a formula I can put in a2 to remove the space and display ABCDEF
View 2 Replies View RelatedThere are five levels of Performance.
Not Evident(o), Emerging(1),Operational(2),Highly Functional(4),Exemplary(5)
There are ten rows of Categories of Performance.
There is one final column of Performance Summary for each category.
There are seven columns that are report sources of Performance.
There is one final row of Performance Summary for each report source.
There is one cell that is a final Summary of Performance Overall.
The cells have drop down lists of words only.
I need the Summary cells to show the result of calculation of the average for each row and column.
Is there a formula that can count the number of words in a cell?
View 3 Replies View RelatedI have a column filled dates in the format 01 January 2013 and I want a formula to count all the cells containing "January" for example.
View 3 Replies View RelatedI need a a formula that will count the number of times a word or phrase appears in a column of cells and assign a number
For example....
Big Red Ford Truck = 1
Ford Truck = 3
Red Ford Truck = 2
After i get the formula, I can sort by "greater than 2"
I'm looking for a macro to remove all words (in a single word per cell format) in a range (approx 100 columns & 7000 rows), except for a list of 100 words.
I'd prefer to email the file if that's okay.
I've been using conventional method to do this and it's time consuming. I would like to total up 2 column. A multiply B to be exact. Below are some examples:
Table 1 - Before totaling up:
Quantity
Product
5
2 x Button A White
3
4 x Button B Pink
4
5 x Ribbon A Black
2
3 x Thread A White
6
2 x Cloth A Blue
Table 2 - After totaling up:
Quantity
Product
10
Button A White
12
Button B Pink
20
Ribbon A Black
6
Thread A White
12
Cloth A Blue
I need to have the sum of the "Quantity" multiply "Product". Or in short A x B.
And the end result need to have the number and "x" sign removed while keeping on the the products names. (2 x ) Take note it's "number" space "symbol" space.
I currently have a spreadsheet with two sheets. The first is my reporting sheet and the second is called Stores. I currently am using the following formula in the reporting sheet:
View 3 Replies View RelatedFormula that will convert uppercase words longer than three letters, into lowercase. Leaving the rest of the string unchanged?
For example,
Mary and John at IBM thought this was a GREAT OPPORTUNITY. Becomes:
Mary and John at IBM thought this was a great opportunity.
I have also tried using more func's REGEX.SUBSTITUTE but I couldn't get that to work either!
"Use a formula to fill in column F (brand name) in the data worksheet. The Brand Name is the Branded Description minus the last word.
NOTE extra mark: If your formula can’t find a space (is error = true) then it takes whatever is in the cell and uses that."
Would I be using the CONCANATE formula or something similar?
I am trying to find certain words in a column and delete the word and characters following. For example, Say I have a column of info as seen below. This is a test of me. I am just experimenting with this stuff. Deleted (6/15/01) Let me know what you think. I am not sure about it all, but I guess I will figure it out. riviledge1 (01/05/06) Now let's see what happens when I try to test it.
I want to find all the "Priviledge1 (01/05/06)" and replace with nothing. Please note, the date will change with each record, so I need to figure out how to tell Excel to find "Priviledge1", delete it and the date behind it. So I want to delete "Priviledge1" and the next 11 characters including the space.
My current formula is this: =SUMPRODUCT((Str311A=1)*(Str311C=15)*(Str311G<>" DM "),Str311L). All the items starting with Str311 are formula's associate with a name. What I'm trying to do is add one more factor into the formula. I need it to look in column I for any cell that contains the words L/S. I tried modifying the formula to this but it does not work:
SUMPRODUCT((Str311A=1)*(Str311C=15)*(Str311I="*L/S*")*(Str311G<>" DM "),Str311L)
The formula contained inside the names are:
Str311=OFFSET(Outlet!$A$1,MATCH("STORE # 163311",Outlet!$A:$A,0)-1,0,MATCH("TOTAL FOR STORE : 163311 SAN MARCOS",Outlet!$A:$A,0)-MATCH("STORE # 163311",Outlet!$A:$A,0)+1,1)
Str311A=OFFSET(Str311,0,0)
Str311C=OFFSET(Str311,0,2)
Str311I=OFFSET(Str311,0,8)
Str311G=OFFSET(Str311,0,6)
What I need is a way to modify my original formula to now also only match cells that contain the phrase L/S. The phrase can be located at the beggining, middle, or end of the cell characters.
Code
Description
Rate
Formulas
123
Afghanistan
0.07
=MAX((B2:B30000="*Afghanistan*")*(C2:C30000))
[code].....
I am trying to find the Maximum Value in Column C based on the criteria that Column B contains "Afghanistan but NOT Mobile.
In a second cell I also want to find the maximum value in Column C based on the criteria that Column B contains "Afghanistan" AND "Mobile"
The code I have come up with is in the table however it is only showing the max value for cells containing "Afghanistan". If I add the wildcard "*" it does not return a value. how I can achieve my objectives?
I have a question with picking out certain word in the column. I am trying to write a formula that would allow me to pick out names that has first 3 alphabetical letters from the columns. The column has a lot of different names, but the one that I would want my automation to pick out is similar to this, C, AR, AA-103, BG-2056, HJE-1100, etc. However, in the same column, I have other words like elevtar, hsvte, lvnrm, etc. that I do not want my formula to pick out.
View 9 Replies View RelatedOn to the topic, I have all the US and Canadian states abbreviated (CO = Colorado, etc.) and was wondering if there is a way to make all of them convert to their respective names in one fell swoop instead of writing it in for each one, one at a time. Something along the lines where I can make, CO = Colorado, TX = Texas and then hit enter and all of the abbreviations would convert. Some kind of command.
View 2 Replies View RelatedI want to sort on number of words, i.e. if a cell contains 1 or 2 words etc, with the cells containing 1 word coming first then cells containing 2 words. Also, if possible, first the cells with fewer characters.
View 9 Replies View RelatedI have tried Sumproduct and Countif and can not get a proper count of rows that contain specific words in multiple columns. I have attached an example spreadsheet where I am looking for a result of 7 rows that contain 1 to 4 specific words within phrases. The example looks for the words: "virus", "spyware", "malware" & "adaware", and ignores any other words like "aware" or "ware".
View 7 Replies View Related