Select registers without best results by category

[danielrodrigo]

Hi

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

NAME	CATEGORY	POINTS
JOHN	TECH	100
MARK	AUDIO	99
PAUL	VIDEO	99
SARAH	TECH	99
ANDY	TECH	99
CARL	COMPUTER	99
RAY	AUDIO	98
DANNY	COMPUTER	98
ELLEN	BAKERY	98
CHARLES	BAKERY	98
FRED	TECH	98
HARRY	TECH	97

<tbody>
</tbody>

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

NAME	CATEGORY	POINTS
JOHN	TECH	100
MARK	AUDIO	99
PAUL	VIDEO	99
CARL	COMPUTER	99
ELLEN	BAKERY	98

<tbody>
</tbody>

THE PROBLEM:

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

NAME	CATEGORY	POINTS
SARAH	TECH	99
ANDY	TECH	99
RAY	AUDIO	98
DANNY	COMPUTER	98
CHARLES	BAKERY	98
FRED	TECH	97

<tbody>
</tbody>

It's possible??

tks a lot !

 

[Peter_SSs]

Welcome to the MrExcel board!

Is this what you mean?

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

00 Old HTML Conversions 2020-04-08 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	NAME	CATEGORY	POINTS		NAME	CATEGORY	POINTS		NAME	CATEGORY	POINTS
2	JOHN	TECH	100		JOHN	TECH	100		SARAH	TECH	99
3	MARK	AUDIO	99		MARK	AUDIO	99		ANDY	TECH	99
4	PAUL	VIDEO	99		PAUL	VIDEO	99		RAY	AUDIO	98
5	SARAH	TECH	99		CARL	COMPUTER	99		DANNY	COMPUTER	98
6	ANDY	TECH	99		ELLEN	BAKERY	98		CHARLES	BAKERY	98
7	CARL	COMPUTER	99						FRED	TECH	98
8	RAY	AUDIO	98						HARRY	TECH	97
9	DANNY	COMPUTER	98
10	ELLEN	BAKERY	98
11	CHARLES	BAKERY	98
12	FRED	TECH	98
13	HARRY	TECH	97
Lists

Cell Formulas
Range	Formula
E2:E13	E2	=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW(A$2:A$13)/(MATCH(B$2:B$13,B$2:B$13,0)=ROW(B$2:B$13)-ROW(B$2)+1),ROWS(E$2:E2))),"")
F2:G13	F2	=IF($E2="","",INDEX(B$2:B$13,MATCH($E2,$A$2:$A$13,0)))
I2:I13	I2	=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW(A$2:A$13)/ISNA(MATCH(A$2:A$13,E$2:E$13,0)),ROWS(I$2:I2))),"")
J2:K13	J2	=IF($I2="","",INDEX(B$2:B$13,MATCH($I2,$A$2:$A$13,0)))

 

[danielrodrigo]

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]

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.

00 Old HTML Conversions 2020-04-08 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	NAME	CATEGORY	POINTS		NAME	CATEGORY	POINTS		NAME	CATEGORY	POINTS
2	JOHN	TECH	100		JOHN	TECH	100		SARAH	TECH	99
3	MARK	AUDIO	99		MARK	AUDIO	99		ANDY	TECH	99
4	PAUL	VIDEO	99		PAUL	VIDEO	99		RAY	AUDIO	98
5	SARAH	TECH	99		CARL	COMPUTER	99		DANNY	COMPUTER	98
6	ANDY	TECH	99		ELLEN	BAKERY	98		CHARLES	BAKERY	98
7	CARL	COMPUTER	99						FRED	TECH	98
8	RAY	AUDIO	98						HARRY	TECH	97
9	DANNY	COMPUTER	98
10	ELLEN	BAKERY	98
11	CHARLES	BAKERY	98
12	FRED	TECH	98
13	HARRY	TECH	97
Lists (2)

Cell Formulas
Range	Formula
E2:E13	E2	=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW(A$2:A$13)/ISNA(MATCH(B$2:B$13,F$1:F1,0)),1)),"")
F2:G13	F2	=IF($E2="","",INDEX(B$2:B$13,MATCH($E2,$A$2:$A$13,0)))
I2:I13	I2	=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW(A$2:A$13)/ISNA(MATCH(A$2:A$13,E$2:E$13,0)),ROWS(I$2:I2))),"")
J2:K13	J2	=IF($I2="","",INDEX(B$2:B$13,MATCH($I2,$A$2:$A$13,0)))

 

[danielrodrigo]

Hi Peter! tks!! All done! both solutions 100% tested and working.. tks a lot!!!

 

[Peter_SSs]

You're welcome. Hopefully the old screen shots will get converted to display properly at some stage.

 

[Tom.Jones]

Hi,

Why I see this:

instead of table?

 

[Peter_SSs]

Why I see this:

instead of table?

Because the forum has changed platforms and not all content has yet been converted.
Refer to: Welcome to Version 4 of the New MrExcel Message Board!