Return Maximum Value Among Multiple Records With Same Key Using VBA
Mar 1, 2013
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.
View 6 Replies
ADVERTISEMENT
Aug 21, 2013
I am trying to lookup data from a list and return multiple records. I cannot use row or column funtion as the tool will not allow
I have tried something similar to this but its not working =INDEX(C$2:C$102,SMALL(IF($A$2:$A$102=$G$3,$K7-$K$7+1),$K7))
the attached excel has the data set. The attached picture has also a sample data that I need formula for.
View 12 Replies
View Related
Jan 16, 2009
I have a spreadsheet with multiple columns and rows of data. I want to be able to type in a criteria and all the rows containing the criteria are called up. For example
Col A Col B Col C
Row 1 Apple Fruit 12
Row 2 Banana Fruit 15
Row 3 Carrot Veg 13
I want to have a cell on another sheet in which I can place a criteria, eg Fruit, and then the entire row 1 and 2 are displayed on the second spreadsheet.
View 9 Replies
View Related
Dec 11, 2008
What I am try to do is look up the highest (maximum) date in a lookup up that has multiple results.
Please see attachment.
Column in question is 'U' (FinishDate)
Maximum date that I want to recover is in column 'P' (DATE_COM)
LOOKUP VALUE is found in column 'A' (QUOTE_NUM)
View 6 Replies
View Related
Aug 10, 2014
I have a range of columns i.e. 23 columns (i.e. B through X). Someone can write records in these columns (starting from B21).
Duplicates are considered the rows with similar data in columns 3 and 11. I know about the removeduplicate method and works really well but i want the duplicates not to be removed. Instead another column shall be checked for date of entry (user will entry date in format dd/mm/yyyy). The newest entry will change the value of the cell in column 4 (islatest column)to TRUE while all other records will be FALSE. This will work with the filtering of data on a pivot table on another worksheet.
View 7 Replies
View Related
Nov 28, 2006
i have a matrix
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.
View 2 Replies
View Related
Jan 8, 2008
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?
View 2 Replies
View Related
Jul 4, 2012
Formula to return the max of certain data.
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).
View 3 Replies
View Related
May 8, 2009
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?
View 5 Replies
View Related
Dec 9, 2009
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,""))}
View 5 Replies
View Related
Aug 13, 2008
i have a spreadsheet like the following
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?
View 5 Replies
View Related
Nov 26, 2008
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.
View 9 Replies
View Related
Nov 2, 2006
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)
View 4 Replies
View Related
Sep 16, 2007
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.
View 4 Replies
View Related
Oct 22, 2007
* I hav two columns B and N having some data. From column N i need to find the Maximum valued cell.
* And now i need the content of a cell in column B in the same row , which matches with maximum value.
View 2 Replies
View Related
Jan 23, 2008
How can I select specific values either side of a peak value?
In the attached file I have a peak value of 10 and would like to select both values of 5 so that I can then identify the frequency they occur at.
View 5 Replies
View Related
Mar 8, 2008
I have inventory in 9 cities. Cities names are in B2-J2. Line items are rows 3-100
In column K, I have the max formula for that row. What I need is, in column L, the name of the location city from row 2 where the max qty is located also color the max qty in the row.
View 2 Replies
View Related
Mar 13, 2008
I have two columns of numbers in column A & B. In column A I am trying to locate the maximum value and get the cell address of the number next to it in column B. I am currently using this formula to do this ..... =ADDRESS(MATCH(MAX(A1:A4),A1:A4),2,4)
It works ok, but if the maximum number is the first value in the column it is returning the last cell address in column B. I am not sure what I am doing wrong.
View 2 Replies
View Related
Jan 20, 2009
i'm looking to return the cell address of the max number in a given range?
View 3 Replies
View Related
Jul 15, 2009
I am a novice to VBA in excel, i have a table of more then 5000 rows. I would like to find a maximum value from each row and return the column title as "GL_Weld" or "Bend" or "Header" into another worksheet cell. i.e First Row max value = 0.011510 should return "Bend" into another worksheet
GL_WeldBendHeader
0.0001700.0115100.000500
0.0000000.0007800.000000
0.0002400.0110700.000480
0.0000000.0006600.000000
0.0000000.0037700.000000
0.0000000.0000100.000000
0.0002800.0108700.000480
0.0000000.0007100.000000
0.0002000.0112900.000490
0.0000000.0008400.000000
000.00002
00.000310.00001
000.00004
00.00030.00002
000.00001
00.000270.00003
000.00002
00.000260.00004
View 4 Replies
View Related
Mar 13, 2014
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).
View 1 Replies
View Related
Jan 2, 2010
I have a got a table ( 10x30) with random numerical values.
How can I return a column number with maximum value?
Values will be changed periodically so the maximum number could be in different column each time
Example:
A-B-C-D-E-F-G
1-2-3-4-5-6-7
2-6-8-18-1-1-8
2-5-6-9-12-1-7
The results will be 4.
View 9 Replies
View Related
Nov 18, 2007
What is wrong with this formula
=MAX(ADDRESS(VALUE(D2),1),1 & ":" & ADDRESS(VALUE(D1),1))
when D2 is 2, D1 is 6 and
=ADDRESS(VALUE(D2),1),1 returns $A$2
View 9 Replies
View Related
Dec 7, 2012
I need to get dates from one file to another. They can be matched on ID.
So if the ID for a record from FileA matches the ID for a record in FileB, return the date from the date from the same row in FileA to FileB. I would just sort and copy them over, but FileA has more records than FileB. All distinct IDs share the same date.
View 3 Replies
View Related
Aug 2, 2012
I have a large workbook in which I do some data analisys. One of the analysis I have to do is to figure out the top 20 oldest items. Therefore, I have the following formula in T55:
=LARGE(OpenTickets!N:N,1)
=LARGE(OpenTickets!N:N,2)
All the way down to 20
hen I need to match that number, say 534, to the person who has that old item related to them, so I have this formula on u55:
=INDEX(OpenTickets!A:A,MATCH(T55,OpenTickets!$N:$N,FALSE)) and so on
this worked great for a while, but I just came up with an issue. I have 2 items that are 534 days old so the formulas =LARGE(OpenTickets!N:N,1) and =LARGE(OpenTickets!N:N,2) correctly returned 534 as the 1 and 2 oldest items.
The problem came when the formula
=INDEX(OpenTickets!A:A,MATCH(T55,OpenTickets!$N:$N,FALSE)) and =INDEX(OpenTickets!A:A,MATCH(T56,OpenTickets!$N:$N,FALSE))
Matched the same employee name. The issue is that the formula looks only at the first instance where 534 occurs and then matches the name.
Instead I need to match both 534 to each employee name whose name appear next to 534. In other words my master workbook (Open Tickets) looks like this and must return:
534 Employee1
534 Employee2
My current formulas return:
534 Employee 1
534 Employee 1
View 4 Replies
View Related
Dec 6, 2012
I want to search a data set and return the maximum value based on 3 criteria using (array) formulas.
Say the data set contained the following:
Row 1: column headings (date, name, data 1, data 2, data 3, etc)
Column A: dates
Column B: names
Columns C through G: data
I want to find the maximum value for a given name, in a certain month, and a nominated data column (e.g. data 2). For example:
Month = June
Name = Geoff
Data column = Data 2
Max value = ????
All of the data in the data columns is numeric.
View 4 Replies
View Related
Jan 22, 2008
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.
View 5 Replies
View Related
Nov 20, 2009
I have one Column (Col A) which has a list of values. In Col B I would like to filter out the values in Col A based on some criteria. For example, below I would like Col B to show all of the records in Col A where the LEFT six characters = "Active".
Col A:
Inactive_Jim
Active_John
Active_Frank
Inactive_Betty
Inactive_Lucy
Active_Tina
Inactive_Jill
Active_Tom
Col B would result in:
Col B:
Active_John
Active_Frank
Active_Tina
Active_Tom
I'm sure this is elementary, but I can't seem to figure it out.
View 4 Replies
View Related
Sep 16, 2009
I have, on one sheet, a list of incumbent employees that includes the following columns: Job Title [text] and Salary [number].
I am trying to automatically populate a second sheet with columns designating: A list of each unique job title (no duplicates), the maximum salary corresponding to each unique job title, and the minimum salary corresponding to each unique job title.
Lastly, I'm an attorney dealing with confidential client data, so I'll have to go through a lot of trouble to post an example... but I'll do it if necessary.
View 4 Replies
View Related
Feb 7, 2014
Certification and Training tracking.xlsx
I want to create a certification only list on a separate tab of training that has been completed where a certification has been issued (as indicated by a "Y" in the "Certification?" column on the training tracking tab) and then populate from some of the fields vs. all of the fields.
What I have now, only pulls the first occurence, not all occurences. I saw that I could have identified the multiple columns that needed to be populated, but it didn't work either, so I'm fine putting a separate vlookup in each column.
View 6 Replies
View Related
