Needing To Find Largest Value In Array, Then Return Entire Row Associated With Result
May 14, 2009
I have a table with subtotals that I need to find the largest value for the subtotal results and then return the cell contents for the corresponding row.
I have attempted to use the hlookup function, but keep getting a #ref error (probably because I am just not that familiar with the entire formula requirements).
I attempted to nest in the 'largest' function to the lookup function, but have so far been stymied....
View 14 Replies
ADVERTISEMENT
Aug 9, 2013
I'm trying to find the largest number in a row and then have the column heading (text) as the result. I can find the largest number by using =max(numb1, numb2 ....) but then how do I get the heading of the column as the result. An example of what I want to happen is below
Red
Green
Blue
Orange
Yellow
Result
2
4
3
6
1
[code]...
I anticipate an issue where 2 columns have the same largest number and not sure how to over come this either with multiple answers
View 4 Replies
View Related
May 15, 2007
how I can return the largest letter from an array of letters? e.g. if the array is "A,B,C,D,H,K,L,O,P", the value returned would be P. Kind of like a MAX function, but for letters instead of numbers.
View 9 Replies
View Related
Jan 15, 2007
I'm using the LOOKUP function to find the largest number and then return the name from a different column, it looks like this:
=LOOKUP(LARGE(round1!$F$2:$F$65,1),round1!$A$2:$A$65)
but all I get is hashN/A?
View 14 Replies
View Related
Aug 6, 2009
I need to multiply an array of logical results ( returned as {1,0,0,0,1,0 et.}) with a text array (a reference column) and return the text in the reference column in case the value in the logical array is 1.
View 3 Replies
View Related
Nov 20, 2013
I am looking up a risk matrix to return the risk rating... it is very simple, it works on one sheet, but on a different sheet (looking up different data, but same basic format) it returns exactly the opposite rating.
My formula is: =INDEX($G$6:$K$10,MATCH(I19,$G$5:$K$5,0),MATCH(J19,$F$6:$F$10,0))
Here is F4 to K10
I have data validation on I19 and J19 based on the cells below
Consequence
Priority
Very Low
Low
Medium
High
Very High
[Code] ..........
BUT... when I put Very Low and Very Low in the two cells (I19 and K19) I get Very High as the return.
View 1 Replies
View Related
Feb 20, 2012
I have a worksheet with about 20 columns of info. First of which is a primary key. Call it "Job Number." I would like to create a macro that runs down the list of primary keys searching for a specific Job Number I ask for in the new worksheet, in A1. It then copies and pastes the entire row where it found that job number into the new worksheet starting at row 6.
View 7 Replies
View Related
May 7, 2007
I have two workbooks Raw File and Lookup_Test, I want to find the value of Column A from Raw File, in Column A of Lookup_Test, if the value if found then i want the value in Column D from Lookup_Test, to be copied to the Column B of Raw File .
Well its like using vlookup , however i am trying to avoid using Vlookup and find any other way of doing the same.
View 9 Replies
View Related
Feb 17, 2009
I have a formula that needs some tweaking. This formula is to reference the ACCT and find the “best” contact information and return the result to the Merge sheet. If the ACCT does not have a Parent then you reference the ACCT to the abc_Phonelist sheet. If the ACCT does have a Parent then use the Parent ACCT since it has a more desirable account number to reference against the abc_Phonelist. If the ACCT or the Parent ACCT uses Processors then the ACCT from the Processors sheet has the best reliable account number to reference against the abc_Phonelist.
In sum, there are three different possible “number tiers” that can be used. The first, the ACCT phone number. This means that the ACCT has neither a Parent nor a Processor. The second, the parent ACCT phone number. This means that the ACCT has a Parent, but neither the Parent ACCT nor the ACCT has a Processor.The third, the Processor phone number. This means that either the ACCT or the Parent ACCT has a Processor phone number.....
View 2 Replies
View Related
Dec 2, 2009
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.
View 10 Replies
View Related
Feb 28, 2012
I have four named ranges (Segment, Keyword, Impressions and Dropdown) and I would like to create a formula-based ranking of keywords by impressions and clicks. Using the following array formula, I am able to return the correct values for impressions or clicks:
{=LARGE(IF(Segment=DropDown,Impressions),$H7)} where $H7 is the number ranking 1, 2, 3 etc.
My question is what array formula could be used to find which row in the array returned that number and then pulls the data from the same row in the other named ranges?
Essentially find row of {=LARGE(IF(Segment=DropDown,Impressions),$H7)} but return Keyword and Clicks on that row.
Other Notes: I cannot use pivot tables and some values might be the same which would make Vlookups not accurate for duplicate values.
Link to an example document to clarify this. [URL] .......
View 9 Replies
View Related
Jan 15, 2009
I have an array of data with both positive and negative numbers. I want to locate the largest number (farthest from zero) regardless of sign. But I will need to retain the sign because I will be dividing every number in the array by that number to normalize the array to values between 1 and -1?
View 12 Replies
View Related
Apr 17, 2008
=LARGEST(overview!$AB5:$AL5;11)
I'm using this formula to look up a certain value in "overview" sheet, is there a way to not show the result in this cell but show as result the cell in the same column in row 3?
View 3 Replies
View Related
Mar 13, 2009
In the attached sheet there is a list of horse runs in time order with the latest at the bottom..col C contains the rating for each run. I need a formula to fill down col d and e. Col d is to contain the rating of its previous run and column e the rating of its second last run. for example row 21 ..I have filled in manually Autumn charms last run had a rating of 116 and its second last run was 122..filled in in cell e21. if a horse has not ran(is now shown previously..above in column e..just leave the cols d and e blank...
View 2 Replies
View Related
May 14, 2007
I've a column that contains numerical values. In my vba code, I have to select a block of cells at a time and get the row which has the maximum value.
One lame approach I am doing is wasting another column that extracts the max from the block of cells using Excel's Max function, and then doing a iterative search in the numerical column to get the cell tht contains the max value, and from it get the row number
Is there a better way such tht i can avoid the looping?
so in a nutshell, i want to get the row number from a range of cells that contains the maximum value
View 9 Replies
View Related
Jun 22, 2014
I tried a few searches for what I need but I think my wording might be off or there isn't anything on it yet. So the formula I'm looking for is: I have a single colum of say 700 rows with random values in each. I would like to average rows 1 to 15 then 2 to 16 then 3 to 17.... all the way through to 686 to 700. Now out of all that I need only the highest 15 period average value returned. Seemed simple until I tried to do it. Whats that saying, the idea is 1% of the invention..
View 4 Replies
View Related
Feb 3, 2011
I've a two column dataset with one column for person name and one column for sales.
I'm trying return the nth largest value where the person name equals the value in a referenced cell.
My dataset may grow or shrink over time so I'm trying to avoid array formula.
Basically I'm looking for something like the below that actually works.
View 5 Replies
View Related
Sep 6, 2007
I am making a pilot logbook and I need a formula which gives me the date of my last flight per aircraft type.
Colum 1 is the date of the flight
Colum 2 is the aircraft type
Each row is one flight
I tried lots of things (IF, LARGE, MAX, SOMPRODUCT,...) but nothing works.
View 4 Replies
View Related
Aug 26, 2009
I'm looking for a formula to enter within a particular cell which will return the largest number that appears within a range of cells, for example the range H:133 through L:136. If it's any easier, only one number will appear within the range, though it could appear in any cell within that range.
View 3 Replies
View Related
May 19, 2009
I have a named range which refers to: =MATCH(LARGE($G$5:$G$68,ROW(G5)-4),G$5:G$68,0)+4. It is supposed to find the largest figure in the adjacent column, list its row in cell H5, then the second largest for cell H6 and so on until the end of the data. The data in column G is dynamic and some cells will contain "0" with certain sections of data thus why I need a formula and cannot just use sort each time.
My problem is that when I use the formula as a named range I get "#N/A" and when I evaluate the formula I will get the answer but in array brackets (e.g.{17}) before the final step turns it to "#N/A". The formula will work fine if inputted directly into the cell but as the same formula will be used over three columns and numerous rows I would much prefer to use a named range.
View 8 Replies
View Related
Oct 4, 2009
I am trying to use the Large function to pull out First, second and third highest values in an array. problem is with ties and zeros it returns the wrong value.
View 4 Replies
View Related
Mar 2, 2012
I need to delete entire row of the result of autofiltering i.e. the visible rows only
If I do this manualy it works but if I record this on a macro, it records the absolute cells range I pick, when in fact the result of the filtering is different every time.
Is there a way around this with VBA?
I've Seen Copy paste in this forum but in my case i need to delete the entire row.
View 1 Replies
View Related
Jun 2, 2009
How would I return a cell reference (address) to a cell that contains the largest number in a list?
I tried using "Address(large....) where I get the correct column, but the returned row # is the actual value in the cell (the highest # in the list).
I eventually will want to delete the highest number to leave the cell blank.
View 3 Replies
View Related
Jun 15, 2013
I am trying to write a macro in order to find the largest value in a column in one worksheet, and copy and paste that value into a different workbook. I have found a code similar to what I am looking for and tweaked it, but it needs a few more adjustments. The code below opens the file I want it to, and pastes data into the correct workbook and worksheet, but I would like it to be able to paste in the next empty cell in Row 3, instead of just in the cell "C3". Also i would like for the program to find the largest numeric value in column C, instead of using an if last row statement, as this current program does not always give me the output I am looking for.
VB:
Dim wsMaster As Worksheet, wbDATA As Workbook
Dim NextRow As Long, LastRow As Long
Set wsMaster = ThisWorkbook.Sheets("Contract Metrics")
NextRow = wsMaster.Range("A" & Rows.Count).End(xlUp).Row + 1
[Code] .....
View 1 Replies
View Related
Apr 24, 2009
I have a pivot table that shows values grouped as ranges in the row, Gender as the columns and an average % value in the data.
In a cell outside of the pivot I would like to be able to produce the name of the range that has the largest difference between male and female.
View 5 Replies
View Related
Nov 26, 2008
I need to find the largest value within a range that is less than the result of a formula from another set of values.
Something like this:
=MAX(E98:E115) that is <=(P63+((1.5*(P63-P61))))
View 3 Replies
View Related
Nov 29, 2008
i have a matrix of 1's and 0's. How can i use excel / which functions can i use to give me the biggest submatrix with just 1's in?
View 14 Replies
View Related
Sep 2, 2008
if c2c1 do nothing.
05 =20-5
10 =20-10
15 =20-15
20 =20-20
View 9 Replies
View Related
Dec 4, 2013
I'm logging failed password attempts and need to find the latest time logged for a single user (when user has failed 5 times).
So the 5th attempt, I need that time
So the usual - Application.UserName in the next free cell in a range and time stamp in the next cell.
What I've tried is:
Say log range is A1:B500 and
A=Username, B=Time stamp
In column C I've nested the two together
(this has more code but not required to get this working)
In C1
Code:
=A1&" "&"live"&" "&B1
So this will bring back "Username live 23:12:12". Now, say there is 5 of these (all with different times)
How do I find the latest time for that user?
Code:
IF(COUNTIF("USERNAME LIVE"&"*")=5,[find the latest time in that list",""
Just to wind you up some more, other users name will be logged and muddled up so it's literally -
"Excel - if a user has failed 5 times then give me the time of the 5th attempt"
I've tried a mixture of COUNTIF, MAX, LARGE, TIMEVALUE(...)
View 4 Replies
View Related
Feb 13, 2008
=IF($A2="","",IF(O$1="","",VLOOKUP($A2,HQA01!$A$24:$AG$525,6,FALSE)))
The above statement works great, but when I try to replace the HQA01 (worksheet name) with a cell reference it doesn't work anymore. I want to point to the cell that has that name of the sheet in it rather than hardcode each sheet name.
=IF($A2="","",IF(O$1="","",VLOOKUP($A2,$Q$1&"!$A$24:$AG$525",6,FALSE)))
When I try the &, or the concatenate function it appears to put quotes around the entire result
View 9 Replies
View Related
