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

[mmr1]

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
	A	B	C	D	E	F	G	H	I
1	Total Sales Units
2	10		15		10	14	10		Return unique numbes Count
3	20	15				15	14		7
4	10	14		10	12		14
5			12	10	14				Return unique sales numbers list
6	4	4	5	5	10	14	15
7	4	14	12	14	10	14	14
8
9
10
11
12
13
14
15
Sheet1

Cell Formulas
Range	Formula
I3	I3	=SUMPRODUCT(($A$2:$G$7<>"")/COUNTIF($A$2:$G$7,$A$2:$G$7&""))

Named Ranges
Name	Refers To	Cells
Range1	=Sheet1!$A$2:$F$7	I3

 

[Eric W]

Try:

Book1
	A	B	C	D	E	F	G	H	I
1	Total Sales Units
2	10		15		10	14	10		Return unique numbes Count
3	20	15				15	14		7
4	10	14		10	12		14
5			12	10	14				Return unique sales numbers list
6	4	4	5	5	10	14	15		4
7	4	14	12	14	10	14	14		5
8									10
9									12
10									14
11									15
12									20
13
Sheet4

Cell Formulas
Range	Formula
I3	I3	=SUMPRODUCT(($A$2:$G$7<>"")/COUNTIF($A$2:$G$7,$A$2:$G$7&""))
I6:I13	I6	=IFERROR(SMALL($A$2:$G$7,SUMPRODUCT(COUNTIF($A$2:$G$7,$I$5:$I5))+1),"")

 

[Eric W]

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)))))

 

[mmr1]

Many thanks Mr. Eric for your great multiple solutions.