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
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
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?
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.
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 RelatedI'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
Data Table including-
List of Identifying Code Numbers for customer invoices
Multiple repetitions of individual Identifying Code Numbers in list
Various data in table range including Various Values of invoices from different dates for each repetion of Identifying Code Number.
- Wish to find largest invoice for each Individual Identifying Code Number in the table without using a pivot table.
i have tried combining Max and Large functions with Vlookups etc.
How would I create a formula that would match a number in a range by column and another number in a range by rows, then fin the intersection of those 2 numbers?
View 9 Replies View RelatedMy problem is trying to identify the applicable rate in a range of dates that are not consistent in every case. I have a number of orders that span 4 years. The rate charged has changed over time and therefore I'm trying to find what the applicable rate would be for that time frame. For example one of my orders was created on 2/27/2012 with a specific item, then again the same item was ordered on 9/10/2013 and I need to find what the rate should have been for both of those orders during those rate periods. I've attached a sample sheet with the 2 tables I've been trying to assess. I've tried using mulitple IF and VLOOKUP formula's but it doesn't work they way I need it to due to the inconsistency in Table 2. H
View 2 Replies View RelatedI have attached an example sheet to help visually explain what I am going to try and articulate. Cell A2 and B2's values result in cell E2, Cell C2 and D2's values result in cell F2. The same is true for row 3.
So what I would like to do is, based on the predetermined ranges of the Height+Chest Columns, auto complete column E, and Columns C+D, auto complete column F. My problem is that I cannot simply cell A + B = E, because the maximum range of one layers the minumum range of another. An example would be Maximum of A+B is 101 to result in SXS in Columm C, but the minmum A+B to get a SS in column C would be 97. So what I need is, for another specific example:....
If cell A2 is between 57 and 65, and cell B2 is between 31 and 36, then Cell C2 must be SXS. If Cell A2 is between 66 and 68, and Cell B2 is between 31 and 36, then Cell C2 must be SS.
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] .....
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.
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))))
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 Relatedif c2c1 do nothing.
05 =20-5
10 =20-10
15 =20-15
20 =20-20
Any way to determine which is the best selling single of that week and print a 1 in the row associated with that CD.
View 1 Replies View RelatedI have a data set where I am trying to find the nth largest value based on one criteria? How would one use do this using the large function?
Example:
Dept. Name / Widgets
ABC / 10
ABC / 8
XYZ / 9
ABC 7
ABC / 3
XYZ / 4
If I need the third most widget production of Dept ABC, the formula return 7.
I don't know too much about standard deviation, but I was wondering if somebody could help me with a function to find 1 standard deviation (68%??) from the largest value (not the mean value) within a column of values. What i'm really after is the 'standard deviation boundaries' either side (i.e. 34% of values above the largest value and 34% below the lowest value) as a highlight.
im using excel mac 2008 so no vba im afraid.
here is an example of real data: ...
I am having some data running into 60 (C:BJ) columns and 200 rows. It is the record of dates query raised and answered with refernce to query number in the A coulmn. I have zero value as entry for the unused cells in this range due to some constraints. I need to find the smallest and the largest non-zero date in this range for analysis purpose. I plan to use the difference between these 2 dates to calculate turn around time for a query.
Problem is to find the smallest and the largest non zero date within range C6:BJ6 which would be used as input in the Networkdays formula.
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]
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
I have a large list of students (Col a,b) who do several sports (up to 10) each, which are arranged as sport name (Col c) followed by score (col d), repeated on pairs of columns for the other 9 sports.
I need a macro to find the 4 highest scoring sports for each student in turn and to highlight the score and sport cells. I have tried to find permutations using the LARGE function but I cannot see how I can do this.
I am trying to create a macro that works with a range of cells and identify the five largest numbers in the range. The following are the steps.
1. I select a range of cells and run the marco.
2. The macro will identify the largest number in the selection and make the cell color red.
3. Then it will find the cell with next largest number and turn the cell orange...and so on till the 5th largest number.
How can I get the largest number in this list by using a function?
View 4 Replies View RelatedI 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?
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....
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 RelatedI'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 RelatedMy data is set out in columns, where alternate columns provide day numbers for given years (we can call these type 1 columns), with adjacent columns containing values which correspond to those type 1 column day numbers (we can call these type 2 columns). There are about a hundred columns in total (50 of each type). I would like to get excel to return the three largest numbers within each type 2 column, but I want to exclude data within the type 2 column above the point which is adjacent to a specific (varying) day number in the type 1 column. The location of this point varies for all the type 1 columns, according to a third row of numbers (the look up start point), which are currently listed below the dataset in every type 1 column. So, for the type 1 column "year 1", I would want Excel to ignore the values 0 and 1, which are listed next to day numbers 78 and 79, and begin looking for the three largest values down the column starting from the value which is adjacent to 81 (which is a 2). In type 1 column "year 2", excel would start looking for the largest values from the cell adjacent to 78, so it would ignore the 18 at the top of the column,and would return 2 and 12. And so on.
Year1
Value
Year2
Value
Year3
[Code]...
look up startpoint
81
78
62
83
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%.