Sheet to sheet query

[fparker61]

I have a sheet that has all of the data I am working with. Based on 3 columns I want to get into another sheet the top 5 rows.

I am able to get the columns I want and sort them but I only want the top 5.

Thoughts on doing this without VBA? MS Query is giving me an error when I try to edit the query.

 

[sandy666]

maybe try pivot table then set top 5

 

[Toadstool]

Hi FParker61,

This only shows 5 entries so if the last of the 5 has a duplicate value then you won't see it.

FParker61.xlsx
	A	B	C	D	E	F	G
1	Figurines	Collector	State		Number	Top 5 Collectors	State
2	32	John	CA		44	Bert	NY
3	11	Sarah	UT		32	John	CA
4	44	Bert	NY		32	Vanessa	CO
5	12	Charles	NY		27	Harry	CA
6	32	Vanessa	CO		12	Charles	NY
7	27	Harry	CA
8	11	Jill	UT
9	2	John	CO
10	1	William	CO
Sheet1

Cell Formulas
Range	Formula
E2:E6	E2	=IF(A2="","",AGGREGATE(14,6,$A$2:$A$9999,ROW()-ROW($E$1)))
F2:G6	F2	=IF(ISNUMBER($E2),INDEX(B$2:B$9999,AGGREGATE(15,6,ROW($B$2:$B$9999)-ROW($B$1)/(($A$2:$A$9999=$E2)),COUNTIF($E$2:$E2,$E2))),"")

 

[sandy666]

Thoughts on doing this without VBA? MS Query is giving me an error when I try to edit the query.

I suggest Power Query (Get&Transform) instead of MS Query