# Obtaining Largest Number In A Range But Then Pulling Back Name Of Team

Oct 16, 2013
I have a set of data like below, I am trying to get a formula to look at a range i.e. May column and then select the highest number in that range but rather than returning the number returning the team name, is that possible? Not sure where to start...

Jan

Feb

Mar

Apr

May

Jun

Team 1

25087

23029

32991

21319

25271

25136.8

[Code] .........

View 4 Replies
ADVERTISEMENT
Feb 25, 2010

I have a worksheet that is full of names (column A) and dollar amounts (column B). Many of the names are repeats.

How do I pull the largest dollar amount for each unique name in the worksheet?

View 9 Replies
View Related
Jul 8, 2009

How to automate the process of finding the highest number in a dynamic range meeting a criteria?

New data added/updated every day (new come, old gone).

The attached file has a properly displayed data.

View 11 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
Mar 2, 2009

I have four Columns A, B, C, D. I need to pull back the value in B when the following is met:

A is the same value Closest value in column C to Column D.

View 3 Replies
View Related
Jun 19, 2009

I need to create a macro that will examine a range of cells, for example D12:T12, find the cell with the largest number value in that range then set the font for that cell to bold (or red, or change the cell background color).

View 2 Replies
View Related
Aug 2, 2014

I am a new to VBA. I want to add a range of numbers to another range of numbers. However second range should always add to itself with first corresponding array values. For example my firsr array is A1:F10 and second array is A15:F25. Now i want A15 = A15+A1, A16= A16+A2, A17= A17+A3 and so on. Every time second array should retain itself value and get added with first corresponding array values.

View 5 Replies
View Related
Jun 9, 2009

I have an inventory list (non-sequential) and I want to pull only the items with id codes that are with in 4 different ranges.

I would like to populate column A with the results, search paramiters are in Q3 and S3.

I can get it to work on a small scale but my inventory list is 10143 rows long.

View 3 Replies
View Related
Sep 4, 2013

I need a formula to check a range of parts and bring back a value of 0 if matched or the value in another cell if it doesn't match.

Part number H-200-3

Part number H-200-3-A

Part number H-200-3-B

If I enter any of the above part numbers then cell B1 returns 0 if it is a different part number then B1 would need to match cell A1.

View 2 Replies
View Related
Jul 31, 2009

I am going to create a large drop down list with data validation the list is to do with fitness the list will containt a sport with a numeral after it

for example : GOLF 4.5 then under that Basketball 12

what i am wanting to do is make a calculation based on what sport is selected in that cell for instance GOLF 4.5 is selected in the cell so i need to say valueinA43*D43/B9 can this be done?? Or if not. can i extract the value from the list to another cell without the text and then perform the calculation.........

View 5 Replies
View Related
May 2, 2006

I need to obtain a range of cells starting on cell B10 to the end of records (vertical)

I need to do two things with this:

1) Count the number of records from cell B10 to the last row populated with data

2) Format the cells in this range

FYI - "R" is always the last column.

View 3 Replies
View Related
Mar 15, 2014

I've got a problem where I need to assign values to ranges between 0 and 3.

Eg.

RANGE VALUE

2-3 2.2

1-3 1.6

0-3 0.8

0-2 0.5

0-2 0.5

0-1 0.1

0-1 0.1

0-1 0.1

0-1 0.1

The values above are simply approximates done manually but I need it done automatically using a formula. The resulting values should be proportioned similarly to the ones above but they need to all sum up to 6 and be no more than 3 each.

View 9 Replies
View Related
Nov 15, 2013

I have a sheet with 5000+ entries, column A is an ID number, column B has one of 4 outcomes C,D and E are just times and quantities and are not important.

In column A there could be multiple entries of the same ID number.

In column B it will show Pcid243A, Pcid243B, Ddar400A or Ddar400B.

I need to export (to another sheet) any entries with the following conditions:

A = ID number with any instance in B showing both a 243 and 400 condition.

E.G

A B C D E

AA565677878D - Pcid243B - C - D - E

AA837632823G - Pcid243B - C - D - E

AA837632823G - Pcid243A - C - D - E

YA74846CC - Ddar400A - C - D - E

WW768765364F - Ddar400A - C - D - E

WW768765364F - Ddar400B - C - D - E

WW768765364F - Pcid243B - C - D - E

AA73262362G - Ddar400B - C - D - E

In the example above, I'd need the red cells exporting to a new sheet as they meet the conditions of having the same ID and both a 243 & 400 outcome:

A B C D E

AA565677878D - Pcid243B - C - D - E

AA837632823G - Pcid243B - C - D - E

AA837632823G - Pcid243A - C - D - E

YA74846CC - Ddar400A - C - D - E

WW768765364F - Ddar400A - C - D - E

WW768765364F - Ddar400B - C - D - E

WW768765364F - Pcid243B - C - D - E

AA73262362G - Ddar400B - C - D - E

Now I've spent ages trying different things and can't get anything to work...

View 3 Replies
View Related
Feb 22, 2010

I have roster that has 12 teams on it, each team has their own column with the supervisors name in the first row. I want to look up the supervisor and count the number of team members listed under that supervisor.

View 4 Replies
View Related
Jul 3, 2014

I would like to be able to find the largest visible area of continuous rows in a filtered table. I know one possible way would be to loop through visible cells using the "xlCellTypeVisible" property and count cells in each visible area. However, the data is consisted of tens and sometimes hundreds of thousands of rows so I was wondering if there is a faster, more efficient way to do this.

View 3 Replies
View Related
Mar 14, 2014

1

0

0

4

6

=largeodd(a1:e1)

=largeeven(a1:e1)

write a udf function to deal with the above ?I know large(a1:e1,1) for picking up the largest one in the row,but no idea to find the largest one when these five numbers are combined to build a 5-digit number.

[ want to find largest 5-digit (also 4 ,3 digit ) numbers combined by thess five numbers]

View 9 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
Jan 25, 2014

I want to find the largest and smallest value for a number... E.g.

68734 the largest value is 87643 and the smallest value is 34678

39823 the largest value is 98332 and the smallest value is 23389

43089 the largest value is 98430 and the smallest value is 3489

View 4 Replies
View Related
Jul 31, 2013

How can I get the largest number in this list by using a function?

View 4 Replies
View Related
Jun 7, 2013

I have huge sorting job in Excel.I have a excel file from each year.And I put these together to a huge excel file. And I need to track down the last activity if the customer is listed multiple times.I could just go through and delete the rows, but the last row.But, that takes a lot of time.

Format:

Last activity (year only) - Customer number - Customer name

2010 - 1001 - Company A

2011 - 1001 - Company A

2012 - 1001 - Company A

View 3 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
Oct 10, 2009

I am trying to subtract the highest number from a row of numbers then find the average for the remaining numbers in Excel 2003, as: =SUM(B2:E2)-MAX(B2:E2)AVERAGE(B2:E2).

View 5 Replies
View Related
May 15, 2014

I have data one sheet which is

Date Ref No Type %complete

Monday 1 Erect 75

Monday 2 Erect 50

Tuesday 1 Erect 95

Wednesday 1 Erect 100

On the next tab I am consolidating all the reference numbers and want to capture the latest % complete

I have been trying =Max(if ref no=1 and type = Erect, % complete),1

But when I try it for reference number 2, it just says 100% aswell, when that should be 50%.

View 1 Replies
View Related
Mar 29, 2014

I basically I have a column with numbers. All the numbers are positive integers. What I like to do is have a VBA function that extracts the integers with the largest number of digits. So for example if we have the following column:

12

123

234

12346

2345

[code].....

So basically we search for largest number of digits, and extract the numbers that fit this category, which could be just one number or multiple numbers.

View 5 Replies
View Related
May 12, 2012

I have a table showing interest levels in training courses from a group of schools, eg:

English Maths Science

School1 3 4 2

School2 7 1 0

School3 3 2 5

I want to identify the column heading for the first, second and third most popular courses. ie for School1 the most popular course is Maths, second most popular is English and so on.

I have tried using the OFFSET function, which worked if I provided the cell location of the required value. I then looked at the ADDRESS function to provide the cell location: eg For School2 find the 2nd most popular course:

=ADDRESS(ROW(A3),COLUMN(data?)+MATCH(LARGE(B3:B5,2),B3:B5,0)-1)

But I have got stuck with what I should enter for COLUMN(data?) as I do not know in which column the second largest value is.

I am sure Excel has the required functionality.

View 12 Replies
View Related
Jan 11, 2008

I am trying to get the addresses of different cells containing numbers. This addresses should be ranked by the size of the value from largest to smallest.

Currently I am using this formula

=CELL("address";OFFSET($A$2;MATCH(LARGE($A$2:$A$10;1);$A$2:$A$10;0)-1;0))

and just increasing the variable of the LARGE function for every rank.

This works fine up to the point some numbers are the same. If I have for example two cells containing a 80 and 80 is the largest value, rank 1 and 2 result in the same address (of the first cell containing the 80). But I want the formula to result in specific addresses for every number.

I thought about using MAX instead of LARGE, but I don't get a working solution for the second (third, fourth, ...) largest value.

View 9 Replies
View Related
Sep 13, 2007

I need a formula which would take the last number that appears in a column and place it in A1.

The column is D10 through to D44 and the last number can appear in any one of those cells.

View 13 Replies
View Related
Jun 30, 2009

How to extract only the number out from the description?

the number can be in left, middle, right..

Description Result

1930 profit 5301000 Meal allowance1930 5301000

expenses 1930 meal the 2701000001930 270100000

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
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