Using Excel Formulas to Pull the Top 10, Including Ties or Duplicates, Across Two Criteria

**IGoCougsI** · 10-31-2018, 06:00 PM

Hello,

I have used a video by Excel Magic Trick to develop a spreadsheet that shows the top-10, including ties and duplicates, for a data set. The problem is that it shows the top10 by sales, but it doesn't allow me to create separate tables based on two criteria (Category and Sales). So, if I have a data set like that listed below (imagine 20 territories and multiple categories and random sales figures) I want to know within just Category = TV what the top 10 sales results were.

Territory Category Sales
1 TV $200
1 Computer $100
1 Software $300
2 TV $150
2 Computer $325
2 Software $270

This video from Excel Magic Trick gets me close (ranking top by sales) but doesn't get me to the point where I can only include those with Category=TV.

www[dot]youtube[dot]com/watch?v=rKDI-kdBsjY

Thanks,
IGoCougsI

**benishiryo** · 10-31-2018, 08:43 PM

hey there. assuming your data in A1:C7, and desired result for top 10 in E1:F10 (category and price), try this array formula in F2:
=IFERROR(LARGE(IF($B$2:$B$7="TV",$C$2:$C$7),ROWS($F$2:F2)),"")

and in E2:
=IFERROR(INDEX($B$2:$B$7,SMALL(IF($B$2:$B$7="TV",IF($C$2:$C$7=F2,ROW($C$2:$C$7))),COUNTIF($F$2:F2,F2))-ROW($B$2)+1),"")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

OR using AGGREGATE (CTRL + SHIFT + ENTER not needed):
=IFERROR(INDEX($B$2:$B$7,AGGREGATE(15,6,ROW($C$2:$C$7)/(($B$2:$B$7="TV")*($C$2:$C$7=F2)),COUNTIF($F$2:F2,F2))-ROW($B$2)+1),"")
=IFERROR(AGGREGATE(14,6,$C$2:$C$7/($B$2:$B$7="TV"),ROWS($F$2:F2)),"")

**IGoCougsI** · 11-01-2018, 06:10 PM

Hi b50enishiryo,

Thank you, this has largely helped me out. I have a follow-up question. While your formulas enabled me to build a top10 table, I am struggling now with how to take those ten sales records for TVs (the two criteria for my top-10 list) and incorporate in the table the unique ID# for each of those records in the dataset. There needs to be some logic that ensures that two identical sales results (i.e. TV sales of $450) which are two separate entries in the data set, reflect the two different unique ID#s in the data set. With this, I could then do simple Vlookups on the unique ID to fill in any additional reporting data associated with the top-10 records.

See attached file which I am using as my sandbox for this.

Appreciate in advance any assistance!

IGoCougsI

**benishiryo** · 11-04-2018, 10:38 AM

you should have came up with this file first. makes it all easier. use this array formula in cell O3:

Formula:

Please Login or Register  to view this content.

since it's a unique ID, your Q3 and R3 is much simpler:
=IFERROR(VLOOKUP(O3,A:D,3,0),"")
=IFERROR(VLOOKUP(O3,A:D,4,0),"")

**IGoCougsI** · 05-13-2019, 06:54 PM

I am struggling again on this as I revisit it. Help is greatly appreciated. See attached file.

1) How to pull the ID# from Column A of the Dataset into this table?

2) How to remove from this table those rows from the DATASET that are marked "Y" within the Non-Exempt column?

**kvsrinivasamurthy** · 05-13-2019, 11:28 PM

ARRAY formulas for columns P, R and S.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

**IGoCougsI** · 05-20-2019, 05:48 PM

Hi, This has been helpful. I'm trying to apply what you have taught me to another scenario with a utility to limit by year. Can you please review the attached and see the comments in red text?

**IGoCougsI** · 05-21-2019, 04:45 PM

Bumping for a response. Thx.

**kvsrinivasamurthy** · 05-24-2019, 03:35 AM

Upload file showing the required result manually (no formulas) for the selections made in Beq sheet.

**IGoCougsI** · 05-28-2019, 07:02 PM

I've uploaded an Excel file that has the source data on a first tab and then there is a Dynamic Report tab which has the report I am trying to build with formulas included. There are errors in it as it is not filtering out duplicate values and isolating on the correct purchase year as desired. I then have two examples of what the dynamic report is producing as compared to the desired results. These are done using a 2-year selection and a 10-year selection. I hope this makes it clear. Within the static reports you will see where I have identified the issues using red and comment notes.

**Bo_Ry** · 05-29-2019, 01:19 AM

Please try at Dynamic report H11:H

=AGGREGATE(15,6,ROW(Data!$G$5:$G$5000)/(Data!$G$5:$G$5000=I11)/(YEAR(Data!$E$5:$E$5092)>=YEAR($E$2)-IF(ISTEXT($E$3),999,$E$3)),COUNTIF(I$11:I11,I11))-ROW(Data!$A$4)

**kvsrinivasamurthy** · 05-29-2019, 01:16 PM

In B2 then drag across upto column E. Note slight changes are done in headings of Dynamic report Sheet.

Please Login or Register  to view this content.

In F2 then copy down

Please Login or Register  to view this content.

Using Excel Formulas to Pull the Top 10, Including Ties or Duplicates, Across Two Criteria

LinkBack

Thread Tools

Rate This Thread

Display

Using Excel Formulas to Pull the Top 10, Including Ties or Duplicates, Across Two Criteria

Re: Using Excel Formulas to Pull the Top 10, Including Ties or Duplicates, Across Two Crit

Re: Using Excel Formulas to Pull the Top 10, Including Ties or Duplicates, Across Two Crit

Re: Using Excel Formulas to Pull the Top 10, Including Ties or Duplicates, Across Two Crit

Re: Using Excel Formulas to Pull the Top 10, Including Ties or Duplicates, Across Two Crit

Re: Using Excel Formulas to Pull the Top 10, Including Ties or Duplicates, Across Two Crit

Re: Using Excel Formulas to Pull the Top 10, Including Ties or Duplicates, Across Two Crit

Re: Using Excel Formulas to Pull the Top 10, Including Ties or Duplicates, Across Two Crit

Re: Using Excel Formulas to Pull the Top 10, Including Ties or Duplicates, Across Two Crit

Re: Using Excel Formulas to Pull the Top 10, Including Ties or Duplicates, Across Two Crit

Re: Using Excel Formulas to Pull the Top 10, Including Ties or Duplicates, Across Two Crit

Re: Using Excel Formulas to Pull the Top 10, Including Ties or Duplicates, Across Two Crit

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] How to break ties when using Large, Small formulas

[SOLVED] Search for criteria and pull all corresponding values with duplicates

Including "Ties" in MIN INDEX Result

Eliminating Ties in MAX/MATCH formulas

Formula to pull values from database. 2 criteria and thousands of duplicates

Including ties to a MIN code

Listing Specific Values Based on Criteria (Including Duplicates)

Bookmarks

Bookmarks

Posting Permissions