I have a bunch of domain names (including subdomains) in column B
In column A i need just the raw domain name...
example
a1 [domain.com]
b1 [domain.com]
a2 [domain.com]
b2 [mail.domain.com]
a3 [domain.com]
b3 [subdomain.domain.com]
I need to create a formula that searches column E for text, let's say "BANANA", and when it finds that text, the formula returns the numerical value of the cell in that same row in column B. Let me know how this can be done.
View 2 Replies View RelatedI am trying to log a specific portion of code from a webpage. The line of code looks like this:
View Details
I need to extract the userid protion, the part between "=" and "'target...." and then
Working with project information which is about 1,000 project. I have two worksheets one has the start date/finish dates for each phase of a project -Define, Measure, I, Analyze, Control. Depending on the date I need it to be read and insert a value in my summary worksheet. The summary sheet contains the 12 months. I need the formula to read two columns start/finish, if the month is the same as the header in the second report return a letter of D, M, I, A, C if not leave blank.
View 9 Replies View RelatedIs there any formula to find text and return its cell address? This is like when we use Ctrl+F function to find a text, and Excel then highlight the cell contains the referred text. At the same time, the name-bar displays the address of the cell. Now, is there any formula that can perform such task like this?
View 9 Replies View RelatedI have the following text
BKG 4.0 CL G 3210x2550 LS 28 PKGL NE
BKG 6.0 CL G 3210x2250 LS 25 PKGL NE
FAB 6.0 AB G 3300x2440 LS 16
FOP 4.0 BZ G 3210x2250 LS 28
FWH10.0 WH L 3210x2550 LS 9
I need a formula which searches within the text to find "LS" and then to return the number after it, this would be either 1 or two digits.
I am thinking if there was a forma that searched for "LS " (Note the space) and then returned the next two digits from the left from the found search ("LS ". I have attached a spreadsheet.
I have a drop down list in a merged cell B12-F12 and B13-F13 and B14-F14 . . . B30-F30.
I need the adjacent merged cell to populate a reason (text) based on the text answer in the drop down list or the entered text in the first merged cell. For example in the cell B12-F12 the user picks from the list or types in "Amiodarone." I want the adjacent merged cell G12-J12to automatically fill with "Heart Rhythm." I also want to be able to set up multiple if - them statements like if Amiodarone is entered then fill adjacent cell with Heart Rhythm and if Toprol XL then fill adjacent cell with Heart / Blood pressure and if simvastatin then fill adjacent cell with Cholesterol, etc. I have about 30 different options for cell 1 that I want to have auto fill in cell 2 based on the contents of cell 1. I've attached my file.
I want the user to be able to choose from the list or type the drug name in.
I have an array 4x4 in size
I need to be able to tell it to look up and find a specific value..
possibly within a chunk above or below a value
and then spit back a true/false which I will turn into text
example
E1 = 99
I want to look up in the 4x4 array of B5:E8
when it finds any value that matches it , in this case its in B1 (99), it spits back a true into box E2
In an ideal world I want it to find any value that is within 0.25 of the value either way
meaning if the value in E1 = 99
then I want it to return true for any value that is greater than 98.75 and less than 99.25
Is there a way with the following formula to tell it that if value return is = to value of cell above then find return next value?
View 6 Replies View RelatedI'm trying to get something like - If a cell contains certain text AND certain text then display set text OR If a cell contain certain text AND certain text then display set text.
For example:
Vauxhall Corsa 1.5Litre Petrol = vauxhall corsa petrol
Vauxhall Corsa 1.5Litre Deisel = vauxhall corsa Deisel
Vauxhall Nova 1 litre Petrol = vauxhall nova petrol
So it the formula is effectively searching for the car type and the fuel type and then returning the relevant set text. So far I'ver only been able to find IF functions criteria to identify one feature of the text.
I'm thinking it's got to be variation on =IF(ISNUMBER(FIND("Vauxhall Corsa",A1)),"Vauxhall Corsa Petrol") but there should be something else in there like =IF(ISNUMBER(FIND("Vauxhall Corsa"&"Petrol",A1)),"Vauxhall Corsa Petrol")
I know this is wrong but just trying illustrate my thinking. I need it to pick up vauxhall corsa AND petrol. I understand that I would have to stick a lot of IF functions together in a string, but that is fine as long as I can ask it to pick certain text AND certain text within a cell.
how to Chk the text string in particular cell, compare it with a super set column and get the full from of the text string from another corresponsing column and the output will be corresponsing full form of the chked text string?
View 6 Replies View RelatedI have a dataset of shifts and want to compare each shift that needs to be filled to a list of requests for time off (vacation, etc).
For one cell the code is: =IF(ISERROR(SEARCH(B1, A1), 0,1)
Where A1 is the column of requests in comma delimited forme (ie: "AB,CD,EF").
Where B1 is the column containing the person assigned to shift 1 (ie: "AB")
In this case, would return a "1" as no error was returned, as AB was found in the list. Here "1" would represent a schedule conflict.
Without creating many cells for each shift- there are 20 shifts- can I create an array or string together this type of "SEARCH" function?
I am having trouble getting my IF statement to test if the cell contains the text "sale" return "X" if not "Y". I need it to search through the text string in that cell and find a certain word, and if it finds that word, retrn a value. I am really having difficulty with is what symbol or function do I use for the logical test? (i.e. =, <>, MATCH, INDEX?)
View 4 Replies View RelatedAttempting to hide columns (of cities) via VBA generates an error when that same city is reselected (either individually, or as part of the group) in the list box, upon clicking the 'Hide' button.
The error happens at this point: ...
I've been searching the forums and web trying to figure out how to extract a text value from a 3D array result, to no avail or well beyond my understanding.
I've attached a file which shows an abbreviated version of the search (from the original 49 col x 400 row, which shows the name to date array which can return a sum.
What I'm looking to do is identify within this array result if there has been a text entry, and return the text as opposed to a sum of the remaining values. This is essentially a double check to make certain that there aren't hours scheduled when other events such as vac/loa/etc are also booked.
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 am trying to Lookup a country to see if it is classified as a "Developed Country".
My formula would be in cell AA4. I want to see if the country in cell B4 is on a list of developed country's on another worksheet. IF the country is found on that list cell AA4 displays Developed. If the country is not on the list, cell AA4 displays Emerging.
I have tried an IF statement using the Match function and it does not work.
=IF(MATCH(B4,'Developed Country List'!$A$2:$A$37,0), "Developed", "Emerging")
I have 10 cells in a column. I have a drop down list in each that is the same in each. If all 10 cells have the same item selected from the drop down list, I want a separate cell to list Yes or No. I've tried a few variations with no luck.
View 4 Replies View RelatedHow would I pull only a specific word from a text cell? If you see my example, in one column I have various job titles. If A2 contains "Manager", then in B2 return "Manager. If A1 contains "Coordinator", return "Coordinator. Etc.
Excel Forum.jpg
I need find the date 1/1/2014 in Row A and from the same colum get the average from row B:z.
View 1 Replies View RelatedI have a huge data base (daily temperatures dating back to 1872), but for simplicity I have limited the values to just a month for this question. What I want to do it list the ten warmest temperatures and then return the date in which it occurred. I am using the following LARGE function to get the ten warmest temperatures (Column B - high temperatures).
=LARGE($B$2:$B$31,$D2)
Column B = High Temp in Data Table
Column D = Rank in Results Table
This works great. However when I use the following Index function to get the date in which it occurred (Column A - Date), it keeps returning the first date in which it occurred when there are multiple occurrences of the same temperature.
=INDEX($A$2:$A$31,MATCH(LARGE($B$2:$B$31,$D2),$B$2:$B$31,0))
Column A = Date in Data Table
Column B = High Temp in Data Table
Column D = Rank in Results Table
In some cases, I have more than 2 occurrences of the same temperatures. For example, the high temperature of 23 degrees occurs 3 times during the month (1/4/2013, 1/11/2013, and 1/12/2013), but only the first one 1/4/2013 shows up in my results table. I would like the other dates to show up. These tables are listed below.
Data TableResults Table
DateHigh TempRankHigh TempDate
1/2/201311271/5/2013
[Code].....
I need a formula to put into cell B19 to Find X in the corresponding row and to return the header value
******** ******************** ************************************************************************>Microsoft Excel - Book2___Running: 11.0 : OS = Windows .NET Server (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=ABCDEF1Part NumberDescriptionSubframe BuildCut And ChopHopper BuildCabBuild2026319Assy Air Ram Auto Blanking X 3026321Assy Air Ram Rear Boom L/H X 4026320Assy Air Ram Rear Boom R/H X 5022587Assy Air Ram Side BrushX 6031159Assy Air Ram Side Brush Large DiameterX 7030501Assy Blanking Flap Valve Single Vm Minor 8034783Assy Brake Pedal Lh Vm Minor Fl X9034784Assy Brake Pedal Rh Vm Minor FlX 10036349Assy Brush Control D/S No Hp Opt Mer/Mag X 11024553Assy Brush Core 1300 Large Widesweep X 12024554Assy Brush Core 1300 Small Widesweep X 13023169Assy Brush Core Large 7000 X 14025662Assy Brush Core Large 80/85 X 15032482Assy Brush Core Magnum Poly/Wire Mix X 16 17 18 19034784Subframe Build 20023169Cut And Chop Sheet1 [HtmlMaker 2.41] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
Need to convert price on transactions throughout the year to different currencies - using historic currency exchange rates.
Entered data in columns A-D:
Date1 | Value1 | Date2 | Value2 |
Required Formula to generate column E for each row individually (to 2dp using d/m/y format):
Value1xValue2whereDate1=Date2
Date1/Value1 will be prices on certain dates while Date2/Value2 will be historic exchange rates for every day of the year.
I have a 10x10 table. First row contains column titles (plain text, hardcoded). Each line below them has a 1 in *just one* cell. So, each line contains 9 zeros and one 1.
In the 11th column I want to check which column has the 1 in it and return the column's title. So, if the 5th row has a 1 in cell E5 I should get, in K5, the value of E1 (the title of the E column that is).
I am having trouble using teh find method. I'm using it to search for a string in a column, then give me the row number, which is fine when the string is found, but when it is not I get an error of "variable not set." I'm pretty certain it is returning void, but how to I capture that? Here's the
voucher_row = Worksheets("Table").Columns("D:D").Find(voucher, LookIn:=xlValues, LookAt:=xlWhole).Row
"voucher" is a string.
I would like to use a macro to find a number in a worksheet, other than the worksheet i'm using the macro in. The number - in this case a material code located in Column A - must be entered by the user in an inputbox, and then return (copy&paste?) the value of another cell (the price of the material - in Column D) in the same row.
I recorded a macro envoking the find function, but then it only finds the number which I used during the recording. How do I change the code so that the user can input any number, or what code must I write?
Im trying to create a marco that will feed cells in one sheet from data from another sheet. I want to be able to enter a "project number" into the corresponding cell, then all other related areas will populate across. refer file attached. If i enter the project number in D4 (sheet 1), then the "Buyer", "Project name", and "Category" data will pull across from sheet 2.
View 3 Replies View RelatedI'm trying to do in vba is search for a number and then return what the row number is for that number.
View 4 Replies View RelatedI'm trying to find
1) the last and
2) first values in a time series of data in which the the data points are scattered all over the place
AND I'd also like to
3) get the corresponding year ( Heading name) for both the
3.1) last and the
3.2) first data point
I've found solutions for all but 3.2. Thus the question remains:
How could I lookup the heading description for the first value on a row?
example:
row #; lookup; year1; year2; year3; year4;
1; year3; ; ; 23; ;
2; year2; ; 21; ; 53;
3; year1; 12; ; 45; 2;
In case it helps anyone looking for same information or providing help to solve my problem 3.2 the other solutions I've found are:
1) lookup last value in row
i) Find Last Number In Row & Return Heading
=LOOKUP(9.99999999999999E+307,R5:AI5)
... also an additional VB solution
ii) Find Last Number In Row & Return Heading
=INDEX(M12:M500;MATCH(9.99999999999999E+307;M12:M500))
... page also includes solution for 2), below
iii) Find Last Number In Row & Return Heading
... 2 VB solutions
2) Lookup first value in row
Find Last Number In Row & Return Heading
=INDEX(M12:500,MATCH(TRUE,INDEX(M12:M500<>0,0,0),0))
... page also includes solution for 1), above
and
3) Lookup heading of last populated cell in row
Find Last Number In Row & Return Heading
=LOOKUP(2,1/(I9:CN9<>""),I$5:CN$5)
4) Could I e.g. modify the above to lookup the year of first value?
I have an existing Cash Flow Report that has a column of abbreviated/shortened (WBS Element) title where each cell contains a unique three lettered/numbered amount of characters (Example: 200). These three abbreviated character cells are specific and relate to their full/longer (SAP WBS Element) title (Example: WBS DWRRI-BW066-200).
In my attached excel model (Find, Locate, and Align WBS Elements to Cash Flow Report.xlsx), I need a formula for the, "Cash Flow Report WBS Elements" sheet in cells B3 thru B11 that will look at the abbreviated three lettered/numbered (WBS Element) titles in cells C3 thru C11, then search and recognize its unique counterpart contained in the, "SAP WBS Elements Export" sheet and return this full/longer (SAP WBS Element) title to the, "Cash Flow Report WBS Elements" sheet in cells B3 thru. B11, just to the left of its abbreviated/shortened (WBS Element) title.