Top 10 List without pivot table

[Eskonn]

I would like to create a top 10 list without using a pivot table.
On sheet 1 there will be data that is added to daily. Below is an example of the data.

Then on sheet 2 I would like to add a section that has a top 10 list. As data is entered on sheet 1, I would like the list to update and change accordingly.
From sheet 1 looking at Column D (item #) it just needs to count how many times the item shows up in the data and rank it accordingly.
So from the data example above it would be as follows
1. Item 3000 shows 4 times
2. item 422878 shows 3 times
etc

 

[Eric W]

What version of Excel do you have? This is pretty easy if you have Excel 365, more difficult with earlier versions. Please update your user profile.

 

[Eskonn]

What version of Excel do you have? This is pretty easy if you have Excel 365, more difficult with earlier versions. Please update your user profile.

I am using Excel 365

Updated my profile with that info.

Thanks

 

[Eric W]

Thank you for doing that. Try:

Book1 (version 1).xlsb
	A	B	C	D	E	F	G	H	I	J
1	Month	Invoiced Date	Invoice #	Item #	Description	Size		Top 10 List
2	July			3000	A			Item #	Count	Description
3	July			3000	A			3000	4	A
4	May			395832	B			422878	3	C
5	June			422878	C			395832	1	B
6	July			422878	C			24952	1	D
7	April			24952	D			402954	1	E
8	January			3000	A			479872	1	F
9	March			402954	E
10	April			3000	A
11	June			479872	F
12	February			422878	C
13
Sheet22

Cell Formulas
Range	Formula
H3:H8	H3	=INDEX(UNIQUE(SORTBY(D2:D20,COUNTIF(D2:D12,D2:D20),-1)),SEQUENCE(MIN(10,ROWS(UNIQUE(D2:D20))-1)))
I3:I8	I3	=COUNTIF(D2:D20,H3#)
J3:J8	J3	=INDEX(E2:E20,MATCH(H3#,D2:D20,0))
Dynamic array formulas.

If you're going to add data to your lists, change the upper row number to something that is past your expected upper limit.

 

[Eskonn]

Thank you for doing that. Try:

Book1 (version 1).xlsb
	A	B	C	D	E	F	G	H	I	J
1	Month	Invoiced Date	Invoice #	Item #	Description	Size		Top 10 List
2	July			3000	A			Item #	Count	Description
3	July			3000	A			3000	4	A
4	May			395832	B			422878	3	C
5	June			422878	C			395832	1	B
6	July			422878	C			24952	1	D
7	April			24952	D			402954	1	E
8	January			3000	A			479872	1	F
9	March			402954	E
10	April			3000	A
11	June			479872	F
12	February			422878	C
13
Sheet22

Cell Formulas
Range	Formula
H3:H8	H3	=INDEX(UNIQUE(SORTBY(D2:D20,COUNTIF(D2:D12,D2:D20),-1)),SEQUENCE(MIN(10,ROWS(UNIQUE(D2:D20))-1)))
I3:I8	I3	=COUNTIF(D2:D20,H3#)
J3:J8	J3	=INDEX(E2:E20,MATCH(H3#,D2:D20,0))
Dynamic array formulas.

If you're going to add data to your lists, change the upper row number to something that is past your expected upper limit.

Eric

Thanks for your help with this. I was able to get the formula to work for the current data but as I added data it didn't seem to update properly even with changing the row limits.
I ended up just going with a pivot table.

Thank you for your time with this question.

 

[Eric W]

After I posted it, I noticed I had a typo in the formula:

=INDEX(UNIQUE(SORTBY(D2:D20,COUNTIF(D2:D12,D2:D20),-1)),SEQUENCE(MIN(10,ROWS(UNIQUE(D2:D20))-1)))

should be

=INDEX(UNIQUE(SORTBY(D2:D20,COUNTIF(D2:D20,D2:D20),-1)),SEQUENCE(MIN(10,ROWS(UNIQUE(D2:D20))-1)))

to match the maximum row like all the other "20"s in the formula. Maybe that was the issue. I'm glad you got a solution regardless though. Let us know if you have any other questions.