# Rank Based On Criteria And Exclude Duplicates

Mar 20, 2014

I have a large spreadsheet where I've successfully ranked a large series of data based on revenue, using product classification as a criteria & summarising this on another page with the top 20 products. The problem I'm having is that within certain criteria's I have some products with the exact same revenue figure, so if the 2nd ranked product within classification "C" for example has revenue of \$100, & there's another product within the same classification also with \$100 of revenue, on my top 20 report I have results for the 1st & 2nd ranked product, but a blank for the third product & then results again for the fourth

The formula I'm using is

=IF(Y12="NA",0,SUMPRODUCT(--(Y12=\$Y\$12:\$Y\$6000),--(AC12

## Rank Based Upon Two Criteria?

Oct 14, 2011

Sheet1 *BMNO1GenderAge GroupTotalRank2Male6-72103*3Male6-71437*4Female6-71294*

I will have 200+ records similar to the above. I would like a formula that ranks the totals. The Males in the 6-7 Age Group would be in 1 Ranking while the Females in the 6-7 Age Group would be in another.

I prefer a formula over a macro. My original thought was =rank(n2,offset(?,?,?,?,?),1) but alas I'm missing something.

## Calculate Rank Based On Criteria

May 20, 2007

I want to calculate the sales rank of the customer whose CUSTOMERID is equal to my ID? Note that sales rank is what position the salesperson is in terms of DOLLAR VALUE OF SALES.

Just a bit stuck on this. How do I calculate the rank? Is it a If statement or a Vlookup Question?

The spreadsheet can be found here: http://www.megaupload.com/?d=5D0YCHIG (It's too big to be uploaded to the forum hosting).

## Dynamically Rank Items Based On Two Criteria’s

Jan 3, 2007

I need to have excel dynamically rank items based on two criteria’s (category and sales).

For Example:
I have a list of items with its category in column A, column B has the sales information. I need to rank the items by category and sales, the data is not sorted and I cannot use a macros/VB.

## Conditional Rank Based On Whether A Criteria Cell Includes A Specific Text String

May 8, 2009

I'm racking my brains as to how I can structure a formula to conditionally rank a value in an array against only those values in the array whose corresponding criteria cell includes a specific letter.

So for example I have a list of 12 values, say 126; 239; 0; 171; 162; 157; 130; 199; 122; 153; 0; 15.
Each of those values corresponds to a heading, say: CDE; DFE; FGE; DFE; ERD; DEA; BDF; DFB; CDE; CEF; CAB; FAB. As you will note some of the headings may or may not be the same and may or may not include the same letters in different orders.

How can I write a formula that ranks in ascending order a given value drawn from the above list (which will be in another cell but which in this case is, let's say, the first value: 126) only against those values whose heading includes a specific character, for example the character C (the character in question will vary and be defined in a specific cell).

As an added complication I need the ranking calculation to exclude any zero values. So in the above example what the formula needs to do is rank the value 126 against a sub-set of the whole array comprising only the values 126; 122; 153; 0.

The answer I need is 2 because, discounting the zero value, 126 is the second highest value.

## Sum Column Exclude Duplicates

Nov 13, 2009

I've got data for different regions of our company. For this example, what I need to do is count the number of jobs in the "South Atlantic" region (Column T) that are NOT "Wal-Mart Stores, Inc" jobs. In order to do this, I put a "1" in Column F so that the formula would have something to sum:

=SUMPRODUCT(--(T2:T1079="SOUTH ATLANTIC"),--(H2:H1079<>"WAL-MART STORES, INC"),(F2:F1079))

The problem is that some job numbers, which are located in Column G, are duplicated. As a result, this number is higher than it should be.

So what I need to do is only count each job number once. How do I do that? Is there a way to modify the existing formula? Or do I need something completely different?

## Get All Names In A Column And Exclude Duplicates?

Sep 11, 2013

I am trying to get a list of all names found in a column but exclude duplicates... I know this can be done in a pivot table but I need the names in a drop down box... if I use the pivot table as the source it will pick up "Grand Total" as a name.

The number of people can increase from week to week...

## Count By Criteria Based On Duplicates In Different Range

Feb 18, 2008

I have 2 columns on column contains 2 numbers, the other comlumn has names. We'll use A1:A4 and B1:B4 for the example.

Row A Row B
25 John Smith
50 Mike Jones
25 Chris Carter
50 Mike Jones

I want to be able to count one number in "A" but only if the name is not duplicated in "B".

My result for #25 would be 2.

My result for #50 would be 1, because Mike Jones is duplicated.

The results would be displayed in two seperate boxes, so I would only need on formula to count #25, then one formula to count #50.

## Formula To Rank Without Duplicates

Jan 6, 2008

I am using the following formula to rank a list of data:

=SUMPRODUCT(--(\$A\$2:\$A\$140=A2),--(\$B\$2:\$B\$140=B2),--(M2

## Removing Duplicates Based On Column Values And Criteria?

Apr 3, 2013

removing duplicate names. Students were allowed to take a quiz as many times as they wanted. I need to remove the duplicate entry by keeping the highest grade.

Here is the setup of my excel file. Column 1 has surnames, Column 2, has first name, and column 3 has grade.

I can't figure out how to filter them based on first and last name because some students have the same name. with the grade as the criteria

I would need an excel formula not macro

## Count Excluding Duplicates Based On Multiple Criteria

Jul 4, 2014

I have the below table of data and what I am looking for is a formula that will count the number for unique numbers (col E) depending on a several criteria. So far I have got this formula but it's not working!

=IFERROR((SUMPRODUCT(--(A3="2014"),--(B3="1"),--(D3="Boc Limited"),)),"")

A
B
C
D
E

year
week
smsupplier_code
supplier name
orders_no

[code].....

## Count Duplicates In A Cell Then Rank Them And Sort Them

Sep 13, 2006

I would like to have the duplicate numbers in position one ( cell D9:D18), position two (cell E9:E18) and position three (cell F9:18) all counted and then ranked based upon date range in column A.

The number order of precendence is 0-9 with zero being the lowest.

Column (I9:I18) counts the duplicates, draws and no draws of digits 0-9. Column (J9:J18) converts results from column I9:18 into a percent.

I need to add a sort button at the bottom of position 1 (columns H & I 22), position 2 (columns L & M 22) and position 3 (columns P & Q 22)

When the sort button is selected it will sort the data by # of repeats, draws, and no draws as illustrated in (columns I, M & Q) or sort data by Position number from 0-9 as illustrated in (columns H) which will then show data of repeats and percentages out of rank order, because it is in order of number precedence.

Last but not least a graphic bar chart that shows the results ranked highest to lowest from when the sort button is selected with the number at the top of the bar colored black. Bar graph colors to be: top 3 red, middle 4 yellow and bottom 3 blue. This should show a vertical cascade effect.

## Rank A List Then Re-rank The List While Excluding Certain (or By Criteria) Items

Dec 30, 2013

I'm trying to Rank a list and than re-rank the list while excluding certain (or by Criteria) items

Vendor Co
Cost Fee
Rank

Vertox
500
4

BV
1520
3

[code].....

## Rank On Two Different Criteria?

Dec 8, 2012

I have a list of names that come from another sheet. I need to rank these by score and have used this formula:

[Code] ........

These names compete within their class but also within the class I need to consider (rank) a second category for example No or Yes. Also all names could be competing in the Yes category or for example 7 in the Yes and 3 in the NO. This will vary and is based on entry in another sheet.

The format is Class (column B), category (Yes or NO, Column C), Name (Column D), Score (Column S) and Rank (Column T)

If I include an IF statement =IF(S33="Yes", at the beginning of the above formula then I get all the Yes ranked perfectly. Is it possible to rank the others with the NO consideration?

## Advanced Filter: Exclude Blank/Empty Cells Criteria

Sep 11, 2006

Im using an advanced filter that uses the following criteria
Days Late Note(s) Note(s)
>90 <>*agreement* <>*QTR*

This shows all data over 90 that do not incl the words agreement or QTR in a column marked Note(s).

My problem is that I do not want to show records that are Null in the Note(s) column.

Note(s)
<> Does not work (possibly because it is text and not Numbers)

as this does work if used on records that contain numbers.

## Multi Criteria Rankings With Consecutive Rank Order

Oct 27, 2012

Need to rank by score1 in descending order and Serial # in ascending order. My biggest problem is to get the rank number to be consecutive. I have listed the rank I would like to see.

Score1
Serial #
Rank

90
146168
1

[Code] .....

## Excel 2003 :: Rank With 4 Criteria That Are Ranked Differently?

Nov 20, 2012

How can I rank with 4 criteria that are ranked differently? (Greater/ lesser etc)

Ranking - Online Spreadsheets - EditGrid

I had a look into this, but I got a bit confused with how isnumber, big, small and rank actually work

Using Excel 2003.

## Ranking Values And Ranking Duplicates The Same Rank

Mar 30, 2014

Ranking values and ranking duplicates the same rank I'm looking to rank a group of numbers that will possibly have duplicate numbers that I want to show as a tie but have no break in the rankings.

Example:
20
18
18
15
12
11
11
9

The ranking I am looking for is:
1
2
2
3
4
5
5
6

## Exclude Sheets Based On Tab Color

Jul 9, 2007

Is there a way to exclude sheets from index based on tab color

## Exclude Rows Based On Condition In 1 Column

Feb 7, 2009

I have a worksheet that I want to export to a csv file. However, there are multiple rows that I want excluded in the export. These lines are recognized by the word "No" in column E. Is this possible?

## Exclude Items In Dropdown Based On User Data

Jul 10, 2013

I'm trying to set up a diet/training spreadsheet so that when a client enters foods and exercises they don't want/can't do, those foods and exercises are made unavailable in dropdown menus (on my programming sheet) so that I can't inadvertently put them into their plans. I have a webform they fill out, which automatically puts the data into a spreadsheet and I know how to pull data from there into the clients' workbooks...

## Exclude Network Path In PivotTable Based On Sharepoint

Sep 4, 2007

I have an excel file linked to a sharepoint 2007 network space. Everytime I open the file, my pivot tables will not refresh, because the source of the pivottable seems to update automatically to include the network drive name on them which somehow causes a problem. Is there anyway to solve this? I put a snippet of the source in the pivottable as an example.

"http ... /PMO/Shared%20Documents/ Finance/Budget/2008/2008%20What%20If%20Budget.xls'!=WhatIf"

## Rank Based On Score

Feb 23, 2010

I have a workbook with two tabs: Results (containing exam results) and Percentile (containing percentile rankings based on scores). There are different rankings for different exams.

I need the percentile ranking returned (on the Results tab) based on the exam result for each student. e.g. for exam M111 a score of 36 would return a ranking of 95, a score of 18 would return a ranking of 35, etc. I've attached a sample workbook.

## How To Rank Based On Text In Another Cell

Dec 12, 2013

how to rank values in one column based on criteria in another column?

For example: How do I rank Cells in Column A that only contain the text "AL" in column B?

COL...A....B
ROW
1......5....NL
2......6....AL
3......7....AL
4......8....NL

## Sort Names Based On Rank.

Feb 12, 2009

See the attached spreadsheet. I have people's names in cells A2:A5. These names have a rank value in cells B2:B5. What I would like to do is create a formula that sorts/orders the names in cells A2:A5 according to the rank in cells B2:B5. The results I would like to achieve are shown in A8:A11. I am aware of the "Sort" function in the "Data" drop down menu, but need to leave cells A1:B5 as they are.

## Lookup A Value Based On A Conditional Rank

Oct 25, 2007

I need to return the project codes for the project with the top 5 sales volumes within each Region for each product. If say, I have the following relevant columns (Project, ProdCode, Region, Sales) in columns A:D

## Formula That Will Rank Based On Highest Data

Mar 12, 2014

I am building a football stat database which I am looking to put in an individual sheet which will search the other 20 sheets, collate the information and then display a top 5 ranking in each area. For example, this is our setup for the data:

Screenshot 2014-03-12 21.56.18.jpg

So what I want to to is have a separate sheet which will have a section for say over 1.5 goals %. The formula would go and retrieve all of the % results for each individual team. Then it would rank the 5 best teams and display the team name in the first cell and the % in the other cell.

## VLookup :: Data Based On Material Rank

Oct 29, 2009

I need to sort the material data based on the material rank but i can't use the 'sort/filter' function. Therefore, I used the VLOOPUP function. For some reason the vlookup formula is not working could you let me know what is the problem? see attchment.

## Rank Based On Date Value And VLookup From Several Sheets

Jan 24, 2014

I have date data in Column O, which appears like. At any given point in time, i would have such date data for 5 to 10 days, and I want to just assign 1 to first date, and next date to 2 and so on.

What formula (can i use RANK formula, and how) I can use to get the desired result. SEcond part of the query may require macro, so will ask later.

Basic Data Desired Output
Column O Column P
20-Jan-13 1
20-Jan-13 1
20-Jan-13 1
20-Jan-13 1

21-Jan-13 2
21-Jan-13 2
21-Jan-13 2
21-Jan-13 2
21-Jan-13 2

22-Jan-13 3
22-Jan-13 3
22-Jan-13 3
22-Jan-13 3

26-Jan-13 4
26-Jan-13 4
26-Jan-13 4
26-Jan-13 4

## Sum Without Duplicates With Other Criteria?

Nov 20, 2013

I need to do a sum with multiple criteria and that can account for duplicate entries and select the correct duplicate to add. I have a sheet with 6 columns of data: TripDate, Company, DepartureTime, BookedTravelers, TripNumber, DataEntryDate. It's just a running data dump from multiple sources and times and can therefore have duplicate entries for a specific trip but where the number of people booked has changed and I need the sum to use the most recent number (dataentrydate is newest).

Example:
TripDate Company DepartureTime BookedTravelers TripNumber DataEntryDate
10/31/13 A 15:30 10 111 10/01/13
10/31/13 B 15:30 11 999 10/01/13
10/31/13 C 15:30 12 999 10/01/13
10/31/13 A 16:00 20 222 10/01/13
10/31/13 A 17:00 30 333 10/01/13
10/31/13 A 15:30 50 111 10/15/13
10/31/13 A 15:30 40 111 10/25/13

Currently I have this in a seperate worksheet to break down the hourly loads where A2 is a date reference I can change to what date I need, and each company is listed in a different column with hours running down Column B from 0400-2200:

=SUMIFS(BookedTravelers,TripDate,Template2!\$A\$2,Company,Template2!J\$4,DepartureTime,">=" & \$B5,DepartureTime,"