I have an Excel Worksheet with 80,000 lines on it. The Columns are arranged thus:
Col A
Col B
Col C
Col D
Col E
Col F
Code
Description
Cost
Rate
Mobile
0.13
1234
Australia Mobile
0.12
Not Mobile
[Code] ........
I am looking for an Excel Formula that will look up Australia & Mobile and then return the highest value of the range of cells it finds that meets that criteria and enters that value in the Rate column next to mobile (F3).
Then I just want a variant of the same formula that will look up Australia but exclude Mobile and then return the highest value of the range of cells it finds that meets that criteria and enters that value in the Rate column next to Not mobile (F4).
I recently asked how to locate a max value within a variable range using a macro and got the following responses: Get Maximum Value From Graph / Chart, all of which worked great.
Dim r As Range Set r = Range("D2", Range("D50")) Range("K1").Value = Application.WorksheetFunction.Max(r)
Now I would like for a different cell to return the time value located one column to the left of the max value found above. I've tried to adapt other offset formulas that I've found to my purposes and haven't been able to make it work. how to do that using the max value as it is found in any of the above three ways
Looking to find 1 of 2 words in a cell in column B and return the word found in the same row in column E. This seemed easy but I am not having any luck.
the cells in column B have several words in them but I am looking for 2 specific words "PLAT" and "ORIG". If the word is not in the cell, it should show a blank cell in column E in the same row, otherwise one of the 2 words should be in that row in column E. A VBA loop would be ideal but a formula that can do it might work as well.
I am looking to select two values from a range based upon their relationship to the maximum value. In this specific case I am trying to calculate the Quality factor based upon the frequency response of a transducer. So I need to identify the Maximum impedance and its corresponding frequency. I also need to identify the 3db points either side of the maximum impedance.
The 3db points are found at 2/3rds of the maximum value of the impedance (Impedance Mag in attached spreadsheet). Calculating the first 3db point isn't overly difficult as I just calculate the value for the 3db point and use the MATCH function to find the closest Impedance value and then work out the corresponding frequency from that. I am stumped as to how to calculate the second 3db point (above the Max impedance value). I have attached a spreadsheet with frequency information in column A, the other columns are used to derive the values found in columns M (Impedance Mag) and N.
I am looking for a function that will search a range like 101-199, 200-249, 250-299 and so on.... Say I have a list of numbers like 155, 179, and 210. How can I find out what range they belong to? I am kind of looking at SumIf's but I cant seem to get that to work.
I need my macros to search for the word "Cancel" or "Cancelled" in columns "T" and "U". Once found, I need the macros to make that entire row an opaque shading.
There will be other wording in these cells that contain "Cancel" or "Cancelled". Is it possible for the macros to search in the sentence and find the words "Cancel" or "Cancelled"
I read and used the Find_Range custom function provided by Aaron Blood. It's a great function, for which I have many uses, but, as I currently have it set up in conjunction with a userform, it doesn't work fully until I use it twice in a row. The first time it's used to return more than one row, it seems to stop short and only display a few of the appropriate rows. Not until it's used twice in a row does it display all the rows containing that product. What do I need to do to have it work fully each time?
I have a large worksheet from which I want to extract only the rows which contain a certain product, selected by a combobox, and paste the rows on another worksheet. I have a userform set up with an oversized listbox which displays the contents of the data worksheet. Below that I have a 'products' combobox, and a button to initiate the Find_Range. Another oversized listbox displays the results.
Here's the function contained in a module:
Function Find_Range(Find_Item As Variant, _ Search_Range As Range, _ Optional LookIn As XlFindLookIn = xlValues, _ Optional LookAt As XlLookAt = xlPart, _ Optional MatchCase As Boolean = False) As Range Dim c As Range, FirstAddress As String With Search_Range Set c = . Find( _ What:=Find_Item, _ LookIn:=LookIn, _ LookAt:=LookAt, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=MatchCase, _ SearchFormat:=False) 'Delete this term for XL2000 and earlier If Not c Is Nothing Then Set Find_Range = c FirstAddress = c.Address................................
The formula has to include a range across each row (for each student). The formula has to search for specific text contained in the classnames, and then return the result along the same row.
Below is an example, which I hope saves OK in the thread. If not I'll upload an Excel file:
A B C D Results:- 1 class1 class2 class3 class4 Gg Hi Fr Sp 2 10m/Gg1 10m/Hi2 10m/Fr1 10m/En1 TRUE TRUE TRUE FALSE 3 10n/Hi1 10n/En2 10n/Sp1 10n/Ma1 FALSE TRUE FALSE TRUE
So I'm trying to merge some of the information found within two separate worksheets. I'm working with a list of ID#s that may or may not be found on both worksheets, and in a column on Worksheet1 I want to put some of the information found for the corresponding ID# on Worksheet2.
Now I've figured out how to check if an ID# on Worksheet1 exists within a column in Worksheet2 using the formula:
(Excuse the syntax errors if they exist, I'm doing this off the top of my head for expediency)
However, instead of returning the word "Match" if it does indeed exist on Worksheet2, I would like to return the value found in Column E, on the row of the matched ID#, in Worksheet2.
I want to a macro to start of with a Find method Eg Account..something like this
Cells.Find(What:="Account").Activate
Account could be in any row or may not be in the document.
My problem is when it is not in the workbook my macro will come up error. how to recode my macro to fix this.
Dim Finder as string Set Finder = Cells.Find(What:="Account",............... 'Problem here ' If Finder is not found then skip till next part of macro. If it is then just select the Cell and I can work of that.
Also Can I do a Find formula to Find Either 'Account' Or 'Debt' Or Asset'?? Just want to know save me copy and pasting it down to change a name.
I would like to use the following code to see if the word "reservoir" shows up in any of the rows in a certain column. If it does I want to insert the formula = SUM(D7:D257)*0.1 into cell C2 and if it doesn't then I want to put a 0 into cell C2. I've tried many routes but can't get past the error if it can't find the word.
I have 10 worksheets in my workbook. The two that I'm concerned with in this post are "Main" and "Completed". If Column AA in "Main" contains the word "Complete" (which is from a drop down list) I want to copy the entire row from "Main" and paste in the "Complete" sheet without over-writting previous pasted rows.
Back in sheet "Main" I want the data in that row to be cleared with the exception of Column A as it contains a formula for sorting blanks. If I delete that row it will mess up my links on other sheets.
I have two different functions, first is importing website to excel and the a second is testing string according to pattern. Each one of them is working ok. I'm trying to find a word " finance" in URL and put it into cell "A1".
Sub ParseWebsite() Application.DisplayAlerts = False On Error Resume Next For i = 1 To 10 SiteURL = "URL;http://www.cnn.com" With ActiveSheet.QueryTables.Add(Connection:=SiteURL, Destination:=Range("A" & i)) .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False................
I have a file with over 20,000 rows that contain a date (Mon~Sun), What I need to do is remove every day that contains Mon~Sat and only keep Sun, this is the code that I have come up with and is working.
Code: Dim c As Range Dim SrchRng Set SrchRng = ActiveSheet.Range("A1", ActiveSheet.Range("A" & Lastrow).End(xlUp))3
index| a| b| c| 0001 |1| 2| 2.22| ( probably it will be much clear to see the attachment)
I need to lookup for the largest number in B2:D2 (in this case 2.22) and return the corresponding header ("C"). I though it would be simple with hlookup() and max() but i can't make it work.
If I have names in one column and amounts in the next column, how do I determine the maximum of the amounts and return the associated name from the other column?
This is data for an electric car. T= Trip and C=charge.
Column F is the parking time between Trips (T).
For each "T" event in column E, I would like to return the MAX parking time available before the last "T" event for that day (date). I've highlight the last "T" events in red.
I have attached an example : forum2.xlsx
Column G is an example of the output that I would like to achieve.
Note: For the last "T" event of the day it can just return the actual parking time (shown in green).
What I'm trying to do is get the MAX function to return a value, but that value needs to come before the values reach zero. It's a cubic equation that basically starts, at the beginning of the data range, under 10 then hits zero and ends up near 30 at the end of the data range. The end data range max doesn't matter to me if the data range hits zero along the way. Is there any way to make the MAX function stop at a certain value, or is there another function that could accomplish what I'm trying to do?
I am currently using an array to return the MAX date value. This formula will enter 1/0/00 if the referenced cells are blank. I'd like the furmula to LEAVE THE CELL BLANK if the referenced cells are blank. {=MAX(IF(Bid_Circuits=$A45,Bid_Trim_Completed,""))}
A B C Country Revenue Month 1 UK 10 Jan 2 France 20 Jan 3 US 30 Jan 4 UK 25 Feb 5 US 35 Feb 6 France 5 Jan
and so on...
So where country = UK, France or US I want to retrieve the MAX revenue from all months and which month it was in. Eg UK max revenue was in Feb of 25. I am not sure how to apply the max formula with criteria. Is there any way to do this?
I am writing a VBA function that will search for a value in a column(say column B) in a table. This column can have duplicates. So my search needs to get all the records that match the value (including duplicates) and from these records i need to return only one value among records fetched that has maximum in another column in the same sheet(say column D).
Eg :
sno dept name marks r1 cs sush 55 r2 ece ram 68 r3 cs harish 77 r
So if i give "cs" value to search it must give me 77 as output. I tried to write my own Lookup function but isn't working and getting #value.
Please see attached file which is data for horse races (this is a small example of the data i will be working with). I need to find the largest values from the MIN and MAX column (shaded Grey) range for each race. I want to be able to add a function that will allow me to quickly identify which horse has the largest number in both the MIN & MAX columns example on spreadsheet is highlighted RED. It would be handy if it would inform me if the criteria has been met by highlighting it or by placing some text value in an adjacent cell on my spreadsheet i have used the example "Y".
It would also be beneficial if that race be deleted in its entirity if there are no horses which meet the criteria above or similary if all other selections within that race be deleted if ther is a horse which meets the criteria. If there is a selection within that race i will need to keep the row that includes the heading which is coloured blue on the attachment.
I will be working with 20,000 or more selections at a time so it is important that they can be identified quickly for ease of use.
I have tried the functions i know such as MAX and LARGE but this requires me to do alot of work when you consider the ammount of data i'm required to work with.
I have searched your forums and thought I had found a sufficient answer but could not get the vba to work. So any help is greatly appreciated. I am trying to determine a max value from a list then put that value in a cell. Next I want to determine how many times and on what day that max value occured. From there take the value and concatenate them adding a "," between them I have attached an example. I would like the values placed in cells F1 and H1 (the other is a min value and when it occurred)
In attached example, I have 3 columns of fractions with a final row of sumtotals (taken from elsewhere). I test to see which column has the maximum value in each row and allocate the result with the column's heading. Sometimes, two or more columns have the same max value. In this scenario, I wish to select the column heading that has the largest sumtotal.
