In column A i will be entering Jobs in the format : 'Customer1 Job4', 'Customer 5 Job 4'. In column B i would liek to identify the jobs automatically, so a formula that goes something like : IF(colum A contains "Job 2", "Job 2",If (Column A contains "Job 3","Job 3","")). There are only about 6 jobs but there could be many customers.
I have a spreadsheet with 2 worksheets. On the first "active parts" I have a list of active part numbers and on the second "All Parts" I have all of the parts available.
I want to compare every part in the All Parts worksheet to see if the part number exists on the Active Parts sheet - if it's there, I would like it to return the value "Active" in column B in All Parts. I have a formula in column B in All Parts that seems to work for the first few, but as soon as it finds one that is active, the rest of the cells below all return "Active".
Is there any way to remove the first part of a string of text in a cell and save the second part?
The first part of the text string is a team code that has a variable number of numbers, capital letters and sometimes spaces. The second part of the text string is a variable number of words in a team name that all start with a capital letter and have lower case letters. Every line has a different team code and team name.
The original spreadsheet also has a column with just team code. Is there a way of using this column to "subtract" the team code from the text string to just leave the team name?
I try to write the function that gets some "ref" and returns appropriate item. For example: if I give to function "C3", it returns "AAA" if I give to function "R18", it returns "BBB" (cause it between R15 to R26) if I give to function "R9", it returns "BBB" also.
I have a column of data with letters in each cell, no numerical, only alpha. Now, some of those cells contain the letters "adj sub" as part of the text string in each cell. "Adj sub" is always at the beginning of the text string. As an example, a cell will look like this - "adj sub mhm". I want to delete rows whose cell description does not contain "adj sub" as part of the text in the cell.
I am working with a lot of data and have been making a table of statistical analysis for several rock types. I have it worked out to where i can filter the data and with the use of a bunch of IF functions, below, and it keeps everything the way I want.
The problem is I have to introduce new rock types, but if I just copy the formulas over i then have to go through hundreds of cells and switch sedimentary rocks to volcanic or whatever new class i have.
How this can be done faster. Maybe some way to say "find "text" within this range and replace" i can do it for actual values but when its embedded in a function im not sure.
I'm trying to create an Excel function that allows me to choose a row from a table based on text found within entered data. For example, say I have a table called "categories" that looks like this:
Categories cat dog mouse
Now in a different section of the workbook, I want to be able to enter an arbitrary number of sentences, one per row, that look something like: "It's raining cats." "The mouse is hiding."....................
I have been working on this worksheet part of which is attached herewith. I would like excel to automatically enter Updated/Inserted in column B against Individuals' names as per the instructions given in column A. For example: As per instruction in A9, B13:B16 should show Updated. I have tried to use the nested if function, but it does not work as I want it to. Also as I am not used to macros or VBA codes, could this problem be solved with formulas?
i am using the IF script, and i am also using the COUNT script in the logical part of the IF statement. i need to make sure that both K3 and V3 have a value inside of the cell in order for the statement to work, although when just K3 has a value, the IF - TRUE statement works anyway. any ideas on how to fix this? =IF(COUNT(K3,V3),SUM(K3+V3),"")
I'm working on a long list of cells that have multiple entries of the same part number at different point on the list. I want to create a function that consolidates those part numbers in two columns to the right of the raw count.
I want to create a user defined function to search a column of data for a part number.
If it exists I want to have a the UDf returna "fail" otherwise "pass"
Here is the code I was trying to use
Function firstpass(SN As String) As String ws = Worksheets("Defects") c = "" With ws.Range("a1:a9999") Set c = .Find(SN, LookIn:=xlValues, lookat:=xlWhole) End With If Not c Is Nothing Then firstpass = "Pass" Else firstpass = "Fail" End If
This function only returns a "#value" and I don't quite know how to troubleshoot it.
I am trying to create my household budget in Excel, but there is a sub-category.
I need a formula which will calculate the following:
Under the "Daily living" section, I have typed the names of the sub-categories, eg: , Groceries General Household Cleaning Education/Lessons Cash
I want it to find all totals next to the word "groceries" in the main part of the spreadsheet, where I type in my credit card statements according to date.
In other words, I want it to add up all my entries/totals next to the word 'groceries' and show the total up at the top of the document, in the "amount" column so I can see the monthly category totals at a glance.
My file is included with alot of cell in the following format: aaaa. bbbbbbb (ccccccc)
I want to copy the first part (aaaa) into column A and to copy the second part (bbbbbbb) in column B. Both part can vary in length: in this case their length were 4 and 7 but basically they can have any length. How can i do this. See also the attached file.
I have a spreadsheet in excel with lots of rows. In column A I have some descriptions such as:
British Pound Danish Krone Japanese Yen
I have created another spreadsheet 2 columns that would have something like: column A/columnB British/Yes Danish/No Japananese/Yes
This 2nd spreadsheet can grow and have more lines. In column Z of the 1st spreadsheet, I want a formula to look at the second spreadsheet and if any word in the column A of the 1st spreadsheet matches the word in column A of the second spreadsheet, I want it to the value in column B of the second spreadsheet.
I am trying to do a vlookup and am having soem issues. I have data like this in the cell i am using as the reference: "60030 - COLUMBUS, OH". The first column of the range I am searching would have "60030" in it. What i need to know is how do I get the vlookup to just reference the first 5 digits in the reference cell? As you can see by the formula below, I set the logical value to true hoping that it would find the closest value, but it did not work. =VLOOKUP(A7,sdoalign,3,TRUE)
I have a column of cells with similar to the following text.
First Marine Avenue 18 1303 11 1
There will always be a final 4 groups of text numbers, each with a space between, but each having different number of numbers (this particular sequence has 2,4,2,1 - but others may be different, but always 4 groups)
The true text ( a series of Road names are potentiaaly all different, having a sequence of words that may be up to 5 words long before the numbers start)
I need to extract the 4 groups of numbers into seperate cells, leaving the Road name text in a singe cell. I would then have a table of 5 columns, the first the Road text, and the next 4 columns being the group of numbers, which I would then format as numbers.
I belong to a fantasy cycling league and after every race, I copy the race results to award each rider their points for the day. The site I visit most frequently lists the riders results in this format:
1. Alberto Contador (Spa) Team Sax Bank Tinkoff 4:05:00 2. Lance Armstrong (USA) Team Livestrong etc... 4:06:00
I usually copy the results to my spreadsheet then calculate each riders points.
The'results end up looking like this in Excel:
1. Alberto Contador (Spa) Team Sax Bank Tinkoff 4:05:00 2. Lance Armstrong (USA) Team Livestrong 4:06:00
So, the data ends up in 3 cells. The position in one cell, the rider and their nationality and their team in another, and the time of arrival in another cell
Is there a way in Excel where I can get rid of or delete all the data after a rider's name?
I usually end up pasting the rider information to Word, then replacing the (***) with a tab symbol, then pasting back to Excel and get rid of the data that's after the name by deleting the cells to which that data was transferred.
Is there an easier way to do this in Excel, or a macro I can create?...
Is there any way I can Vlookup for just part of a text stiring in a cell. I am trying to vlook if a part number contains the letters “HFR” if it does I want It to refer to the comment I have made in the second column of my vlook table against HFR.
I have a simple sentence in cells C3, D3 and E3. The sentence in C3 is: Find Airfare to Chicago, Airline Tickets to Chicago, and More | XXXXXXXXX®
D3 contains: XXXXXXXXX highlights outstanding airfares to Chicago from quality airlines and agencies. E3 contains my list of keywords: Chicago Flights, Cheap Flights to Chicago, Cheap Chicago airline tickets, chicago flight, chicago airfare, chicago airline ticket, chicago airline tickets
The XXXXXXXXX in the C&D cells is the client name which I had to blank out in order to post here. I wasn't sure if you guys would need to know exactly how many characters there are in each cell. In column B3 is the name of the city, in this case the city is Chicago. I have 8000 destinations so I need a formula that will take the city (or any information) in the B column and replace that in the C, D and E columns in the appropriate place. I've attached a small, representative file to demonstrate.
Is there a formula where it will find all that reads 07-06 and be able to change it to 08-06? Below are samples of formulas where I am looking to change 07-06 to 08-06 without having to go into each cell and manually changing 07-06 to 08-06:
I am trying to reference text and another document. The text I want is " stock option expense negatively impacted 1Q:07 by:" and the number i want it to bring back is currently in the model as a percentage. However I would like to bring it back in basis points which would be the percentage times 10,000. So if its in the model as 0.4%, I'd like to bring it back as 40 bps. Here is the formula I am using that is insufficient.