Select registers without best results by category

danielrodrigo

New Member
Joined
Nov 15, 2019
Messages
3
Hi

I Have a Table with a RANKING of employees like this:




NAMECATEGORYPOINTS
JOHNTECH100
MARKAUDIO99
PAULVIDEO99
SARAHTECH99
ANDYTECH99
CARLCOMPUTER99
RAYAUDIO98
DANNYCOMPUTER98
ELLENBAKERY98
CHARLESBAKERY98
FREDTECH98
HARRYTECH97

<tbody>
</tbody>


With PROCV I select all "CATEGORY CHAMPIONS" like this:

NAMECATEGORYPOINTS
JOHNTECH100
MARKAUDIO99
PAULVIDEO99
CARLCOMPUTER99
ELLENBAKERY98

<tbody>
</tbody>



THE PROBLEM:

I need to select next 6 "WILDCARD" employees - GENERAL (without category) - and WITHOUT the best results (the champions) - like this:




NAMECATEGORYPOINTS
SARAHTECH99
ANDYTECH99
RAYAUDIO98
DANNYCOMPUTER98
CHARLESBAKERY98
FREDTECH97

<tbody>
</tbody>


It's possible??

tks a lot !
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
41,722
Office Version
365
Platform
Windows
Welcome to the MrExcel board!

Is this what you mean?

E2 and I2 copied down.
F2 and J2 copied across and down.

<b>Lists</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:80px;" /><col style="width:93px;" /><col style="width:66px;" /><col style="width:23px;" /><col style="width:60px;" /><col style="width:93px;" /><col style="width:66px;" /><col style="width:23px;" /><col style="width:80px;" /><col style="width:93px;" /><col style="width:66px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td><td >K</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-size:10pt; ">NAME</td><td style="font-size:10pt; ">CATEGORY</td><td style="font-size:10pt; text-align:right; ">POINTS</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">NAME</td><td style="font-size:10pt; ">CATEGORY</td><td style="font-size:10pt; text-align:right; ">POINTS</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">NAME</td><td style="font-size:10pt; ">CATEGORY</td><td style="font-size:10pt; text-align:right; ">POINTS</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:10pt; ">JOHN</td><td style="font-size:10pt; ">TECH</td><td style="font-size:10pt; text-align:right; ">100</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">JOHN</td><td style="font-size:10pt; ">TECH</td><td style="font-size:10pt; text-align:right; ">100</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">SARAH</td><td style="font-size:10pt; ">TECH</td><td style="font-size:10pt; text-align:right; ">99</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-size:10pt; ">MARK</td><td style="font-size:10pt; ">AUDIO</td><td style="font-size:10pt; text-align:right; ">99</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">MARK</td><td style="font-size:10pt; ">AUDIO</td><td style="font-size:10pt; text-align:right; ">99</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">ANDY</td><td style="font-size:10pt; ">TECH</td><td style="font-size:10pt; text-align:right; ">99</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-size:10pt; ">PAUL</td><td style="font-size:10pt; ">VIDEO</td><td style="font-size:10pt; text-align:right; ">99</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">PAUL</td><td style="font-size:10pt; ">VIDEO</td><td style="font-size:10pt; text-align:right; ">99</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">RAY</td><td style="font-size:10pt; ">AUDIO</td><td style="font-size:10pt; text-align:right; ">98</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="font-size:10pt; ">SARAH</td><td style="font-size:10pt; ">TECH</td><td style="font-size:10pt; text-align:right; ">99</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">CARL</td><td style="font-size:10pt; ">COMPUTER</td><td style="font-size:10pt; text-align:right; ">99</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">DANNY</td><td style="font-size:10pt; ">COMPUTER</td><td style="font-size:10pt; text-align:right; ">98</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="font-size:10pt; ">ANDY</td><td style="font-size:10pt; ">TECH</td><td style="font-size:10pt; text-align:right; ">99</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">ELLEN</td><td style="font-size:10pt; ">BAKERY</td><td style="font-size:10pt; text-align:right; ">98</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">CHARLES</td><td style="font-size:10pt; ">BAKERY</td><td style="font-size:10pt; text-align:right; ">98</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="font-size:10pt; ">CARL</td><td style="font-size:10pt; ">COMPUTER</td><td style="font-size:10pt; text-align:right; ">99</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">FRED</td><td style="font-size:10pt; ">TECH</td><td style="font-size:10pt; text-align:right; ">98</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="font-size:10pt; ">RAY</td><td style="font-size:10pt; ">AUDIO</td><td style="font-size:10pt; text-align:right; ">98</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="font-size:10pt; ">DANNY</td><td style="font-size:10pt; ">COMPUTER</td><td style="font-size:10pt; text-align:right; ">98</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="font-size:10pt; ">ELLEN</td><td style="font-size:10pt; ">BAKERY</td><td style="font-size:10pt; text-align:right; ">98</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="font-size:10pt; ">CHARLES</td><td style="font-size:10pt; ">BAKERY</td><td style="font-size:10pt; text-align:right; ">98</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="font-size:10pt; ">FRED</td><td style="font-size:10pt; ">TECH</td><td style="font-size:10pt; text-align:right; ">98</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="font-size:10pt; ">HARRY</td><td style="font-size:10pt; ">TECH</td><td style="font-size:10pt; text-align:right; ">97</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >E2</td><td >=IFERROR(INDEX<span style=' color:008000; '>(A:A,AGGREGATE<span style=' color:#0000ff; '>(15,6,ROW<span style=' color:#ff0000; '>(A$2:A$13)</span>/<span style=' color:#ff0000; '>(MATCH<span style=' color:#804000; '>(B$2:B$13,B$2:B$13,0)</span>=ROW<span style=' color:#804000; '>(B$2:B$13)</span>-ROW<span style=' color:#804000; '>(B$2)</span>+1)</span>,ROWS<span style=' color:#ff0000; '>(E$2:E2)</span>)</span>)</span>,"")</td></tr><tr><td >F2</td><td >=IF($E2="","",INDEX<span style=' color:008000; '>(B$2:B$13,MATCH<span style=' color:#0000ff; '>($E2,$A$2:$A$13,0)</span>)</span>)</td></tr><tr><td >I2</td><td >=IFERROR(INDEX<span style=' color:008000; '>(A:A,AGGREGATE<span style=' color:#0000ff; '>(15,6,ROW<span style=' color:#ff0000; '>(A$2:A$13)</span>/ISNA<span style=' color:#ff0000; '>(MATCH<span style=' color:#804000; '>(A$2:A$13,E$2:E$13,0)</span>)</span>,ROWS<span style=' color:#ff0000; '>(I$2:I2)</span>)</span>)</span>,"")</td></tr><tr><td >J2</td><td >=IF($I2="","",INDEX<span style=' color:008000; '>(B$2:B$13,MATCH<span style=' color:#0000ff; '>($I2,$A$2:$A$13,0)</span>)</span>)</td></tr></table></td></tr></table>
 
Last edited:

danielrodrigo

New Member
Joined
Nov 15, 2019
Messages
3
OH MY GOD! Tks Peter! Yes. You are a genius!! tks a lot!!

I will test here (I need to translate the formulas, because my language is different) - and I have 14 categories (in example I simplify with 5) but I think the result will be the same.

tks!!!!!!!! save my day! :D
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
41,722
Office Version
365
Platform
Windows
You're welcome. :)

Post back with details if there are further issues.

BTW, you could also use this shorter formula for E2, copied down. When you first enter it and copy down you would get a whole column of "JOHN" in this example but as soon as you enter the F2 formula and copy across/down, the results will come as shown below.

<b>Lists (2)</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:80px;" /><col style="width:93px;" /><col style="width:66px;" /><col style="width:23px;" /><col style="width:68px;" /><col style="width:93px;" /><col style="width:66px;" /><col style="width:23px;" /><col style="width:80px;" /><col style="width:93px;" /><col style="width:66px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td><td >K</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-size:10pt; ">NAME</td><td style="font-size:10pt; ">CATEGORY</td><td style="font-size:10pt; text-align:right; ">POINTS</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">NAME</td><td style="font-size:10pt; ">CATEGORY</td><td style="font-size:10pt; text-align:right; ">POINTS</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">NAME</td><td style="font-size:10pt; ">CATEGORY</td><td style="font-size:10pt; text-align:right; ">POINTS</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:10pt; ">JOHN</td><td style="font-size:10pt; ">TECH</td><td style="font-size:10pt; text-align:right; ">100</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">JOHN</td><td style="font-size:10pt; ">TECH</td><td style="font-size:10pt; text-align:right; ">100</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">SARAH</td><td style="font-size:10pt; ">TECH</td><td style="font-size:10pt; text-align:right; ">99</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-size:10pt; ">MARK</td><td style="font-size:10pt; ">AUDIO</td><td style="font-size:10pt; text-align:right; ">99</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">MARK</td><td style="font-size:10pt; ">AUDIO</td><td style="font-size:10pt; text-align:right; ">99</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">ANDY</td><td style="font-size:10pt; ">TECH</td><td style="font-size:10pt; text-align:right; ">99</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-size:10pt; ">PAUL</td><td style="font-size:10pt; ">VIDEO</td><td style="font-size:10pt; text-align:right; ">99</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">PAUL</td><td style="font-size:10pt; ">VIDEO</td><td style="font-size:10pt; text-align:right; ">99</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">RAY</td><td style="font-size:10pt; ">AUDIO</td><td style="font-size:10pt; text-align:right; ">98</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="font-size:10pt; ">SARAH</td><td style="font-size:10pt; ">TECH</td><td style="font-size:10pt; text-align:right; ">99</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">CARL</td><td style="font-size:10pt; ">COMPUTER</td><td style="font-size:10pt; text-align:right; ">99</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">DANNY</td><td style="font-size:10pt; ">COMPUTER</td><td style="font-size:10pt; text-align:right; ">98</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="font-size:10pt; ">ANDY</td><td style="font-size:10pt; ">TECH</td><td style="font-size:10pt; text-align:right; ">99</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">ELLEN</td><td style="font-size:10pt; ">BAKERY</td><td style="font-size:10pt; text-align:right; ">98</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">CHARLES</td><td style="font-size:10pt; ">BAKERY</td><td style="font-size:10pt; text-align:right; ">98</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="font-size:10pt; ">CARL</td><td style="font-size:10pt; ">COMPUTER</td><td style="font-size:10pt; text-align:right; ">99</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">FRED</td><td style="font-size:10pt; ">TECH</td><td style="font-size:10pt; text-align:right; ">98</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="font-size:10pt; ">RAY</td><td style="font-size:10pt; ">AUDIO</td><td style="font-size:10pt; text-align:right; ">98</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="font-size:10pt; ">DANNY</td><td style="font-size:10pt; ">COMPUTER</td><td style="font-size:10pt; text-align:right; ">98</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="font-size:10pt; ">ELLEN</td><td style="font-size:10pt; ">BAKERY</td><td style="font-size:10pt; text-align:right; ">98</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="font-size:10pt; ">CHARLES</td><td style="font-size:10pt; ">BAKERY</td><td style="font-size:10pt; text-align:right; ">98</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="font-size:10pt; ">FRED</td><td style="font-size:10pt; ">TECH</td><td style="font-size:10pt; text-align:right; ">98</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="font-size:10pt; ">HARRY</td><td style="font-size:10pt; ">TECH</td><td style="font-size:10pt; text-align:right; ">97</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >E2</td><td >=IFERROR(INDEX<span style=' color:008000; '>(A:A,AGGREGATE<span style=' color:#0000ff; '>(15,6,ROW<span style=' color:#ff0000; '>(A$2:A$13)</span>/ISNA<span style=' color:#ff0000; '>(MATCH<span style=' color:#804000; '>(B$2:B$13,F$1:F1,0)</span>)</span>,1)</span>)</span>,"")</td></tr></table></td></tr></table>
 
Last edited:

danielrodrigo

New Member
Joined
Nov 15, 2019
Messages
3
Hi Peter! tks!! All done! both solutions 100% tested and working.. tks a lot!!! :)
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
41,722
Office Version
365
Platform
Windows
You're welcome. Hopefully the old screen shots will get converted to display properly at some stage. :)
 

Forum statistics

Threads
1,077,823
Messages
5,336,570
Members
399,088
Latest member
Swindlestikz

Some videos you may like

This Week's Hot Topics

Top