# Small/Large () without repeating the same value in each list

#### ExcelBasicBro

##### New Member
I am creating two lists. List one is the Top 5 and the second list is the bottom 5. I am pulling the data from the same data set. I get can return both the Large() and the Small() but if the data has less than 10 items to start, it ends up repeating of the values on the both lists. I'd like to avoid that if possible. Any ideas on how to say if a value shows up on the Top 5, do not put it on the bottom 5?

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### jasonb75

##### Well-known Member
Using the dynamic array functions in office 365,
Book1 (version 1).xlsb
ABCDE
1Original Top 5Bottom 5
2191
3282
4373
5464
655
76
87
98
109
11
Sheet3
Cell Formulas
RangeFormula
C2:C6C2=IFERROR(LARGE(A2:A21,SEQUENCE(5)),"")
E2:E5E2=IFERROR(SMALL(A2:A21,SEQUENCE(MIN(5,COUNT(A2:A21)-5),,1)),"")
Dynamic array formulas.

#### ExcelBasicBro

##### New Member
That worked! Thank you!

Replies
2
Views
69
Replies
8
Views
113
Replies
4
Views
118
Replies
4
Views
206
Replies
0
Views
63

1,148,159
Messages
5,745,116
Members
423,925
Latest member
globaltlg

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back