1

Since I've exhausted about every resource I could find in regards to this question, I figured it was finally time to ask this community.

I have a very large (15k+ row) dataset that I'm looking to generate a report on giving the top 25 largest values based on one of the columns, HOWEVER, there is additional criteria that needs to be considered other than just the values in one column. I have done this already with less criteria, but adding more is giving me trouble.

My (working) formula for Top N with some criteria:

{=LARGE(IF('IMPORTED DATA'!$X$4:$X$1048576 = IF('Data Cleanup'!$AX$3 = 1, "Gaming Designed", "Not Gaming Designed"), 'IMPORTED DATA'!$BH$4:$BH$1048576), ROW(A2) - ROW(A$1))}

The issue comes when I have another criteria I need to add that uses wildcard characters to distinguish the 'correct' criteria. Here is what I've come up with so far, but this just results in the COUNTIF portion always resulting in true, so not actually applying the added criteria:

{=LARGE(IF(COUNTIF('IMPORTED DATA'!$P$6:$P$1048576, IF('Data Cleanup'!$AX$3 = 1, "?????", "????")) * ('IMPORTED DATA'!$X$6:$X$1048576 = IF('Data Cleanup'!$AX$3 = 1, "Gaming Designed", "Not Gaming Designed")) * ('IMPORTED DATA'!$E$6:$E$1048576 <> "All Other (Suppressed)"), 'IMPORTED DATA'!$BH$6:$BH$1048576), ROW(A2) - ROW(A$1))}

I tried to work-around IF statements not accepting wildcard characters using the COUNTIF method but to no avail.

I understand that this is a bit of a rough question, but I'll do my best to respond to as many questions as I can to help clarify.

A couple more bits of information that may be helpful:

  • This is entirely based in Excel 2019, I know that FILTER would be an easy solution, but I don't have access to that in this version of excel.
  • The reason for using wildcards is because it was the easiest way to distinguish between the two categories to sort: above or below 100Hz. Anything under 100Hz will be 4 characters, while anything above will be 5.
  • I also need other data from the same row as the results, so any methods must be also applicable to MATCH criteria so that I can look up the rest of the data with the same search parameters.
Aram Howard
  • 11
  • 2
  • instead of `COUNTIF('IMPORTED DATA'!$P$6:$P$1048576, IF('Data Cleanup'!$AX$3 = 1, "?????", "????"))` use `LEN('IMPORTED DATA'!$P$6:$P$1048576)=IF('Data Cleanup'!$AX$3 =,5,4)` But really if your data is always roughly 15000 rows make the ranges much smaller ie: `'IMPORTED DATA'!$P$6:$P$20000` it will make your formula run soooooo much quicker. – Scott Craner Jun 24 '22 at 16:56
  • Maybe it's an idea extracting the value from the Hz column `and rank them or calculate the nth largest values from there? – P.b Jun 24 '22 at 16:58
  • Also `ROW(A2) - ROW(A$1)` can just be `ROW(A1)` – Scott Craner Jun 24 '22 at 16:58
  • 1
    Have a look at this and edit to suit: https://stackoverflow.com/a/58640367/4961700 give it a vote if it helps. – Solar Mike Jun 24 '22 at 17:13
  • Do you mind wrapping/splitting your formulas so that we don't have to scroll to view them – Rohit Gupta Jun 25 '22 at 12:46

2 Answers2

0

its very hard to understand without seeing the data. What i understood is that if you make a helper column in the dataset as per the criteria you want that would solve your problem. at least thats how i am also using.

You need to create a ranking column in the data sheet.

Ranking Formula = =COUNTIFS($M$3:$M$233,">="&M3,$K$3:$K$233,K3) with thise formula you can add as many as criteria as you want.

Index Formula = =INDEX($K$3:$K$233,MATCH(1,($K$3:$K$233=$B$1)*($N$3:$N$233=A3),0)) you need to change the columns names you want.

no need row() functions just try always to use simple sequence will work

Good luck

Example

Saif Ulislam
  • 56
  • 5
0

Ended up solving this in a very simple method thanks to Scott Craner's comment.

Since wildcards don't work in if statements, using LEN did the trick. Final formula ended up being:

{=LARGE(IF(LEN('IMPORTED DATA'!$P$6:$P$30000)=IF('Data Cleanup'!$AX$3 =1,5,4) * ('IMPORTED DATA'!$X$6:$X$30000 = IF('Data Cleanup'!$AX$3 = 1, "Gaming Designed", "Not Gaming Designed")) * ('IMPORTED DATA'!$E$6:$E$30000 <> "All Other (Suppressed)"), 'IMPORTED DATA'!$BH$6:$BH$30000), ROW(A2) - ROW(A$1))}

Thank you to everyone for your help!

Aram Howard
  • 11
  • 2