Return a unique numbers of sales list, values as categorized smallest to largest.

mmr1

Board Regular
Joined
Aug 25, 2020
Messages
80
Office Version
  1. 365
Platform
  1. Windows
Hi,

Required a formula help for generate a unique numbers of sales list as a sequence to be smallest to largest unique numbers without blanks as based on data contains in column A:G.

Sample are enclosed,


Thanks for help,

Book2
ABCDEFGHI
1Total Sales Units
21015101410Return unique numbes Count
3201515147
41014101214
5121014Return unique sales numbers list
64455101415
74141214101414
8
9
10
11
12
13
14
15
Sheet1
Cell Formulas
RangeFormula
I3I3=SUMPRODUCT(($A$2:$G$7<>"")/COUNTIF($A$2:$G$7,$A$2:$G$7&""))
Named Ranges
NameRefers ToCells
Range1=Sheet1!$A$2:$F$7I3
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Try:

Book1
ABCDEFGHI
1Total Sales Units
21015101410Return unique numbes Count
3201515147
41014101214
5121014Return unique sales numbers list
644551014154
741412141014145
810
912
1014
1115
1220
13 
Sheet4
Cell Formulas
RangeFormula
I3I3=SUMPRODUCT(($A$2:$G$7<>"")/COUNTIF($A$2:$G$7,$A$2:$G$7&""))
I6:I13I6=IFERROR(SMALL($A$2:$G$7,SUMPRODUCT(COUNTIF($A$2:$G$7,$I$5:$I5))+1),"")
 
Upvote 0
Solution
Also, since you have Excel 365, you can try this formula:

=UNIQUE(SMALL(A2:G7,SEQUENCE(COUNT(A2:G7))))

and this gives you the number of unique values:

=COUNT(UNIQUE(SMALL(A2:G7,SEQUENCE(COUNT(A2:G7)))))
 
Last edited:
Upvote 0
Many thanks Mr. Eric for your great multiple solutions.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,205
Members
448,554
Latest member
Gleisner2

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?

Disable AdBlock

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
Back
Top