jeffreyweir
Active Member
Okay, so:
* Formula Challenge 007 dealt with extracting an array of distinct/duplicate/unique values from a range containing only numerical values
* Formula Challenge 008 dealt with extracting an array of distinct/duplicate/unique values from a range containing only text values
Can you come up with a formula that extracts an array of distinct/duplicate/unique items from a range containing numerical and text values?
- You should assign a 1-digit named range to the original data called m (m for mixed), and refer to that within your formula. This makes it easier for me to compare formula lengths and performance on my master spreadsheet.Other than that, you should not use any named ranges or intermediate cells.
- The output of each formula must be an array that can be used directly by other formula (such as COUNTA etc). That is, we're after a single formula that you can directly incorporate into other formulas - not a formula that has to be entered over a worksheet range in order to work.
- Each formula should be completely dynamic, and able to be used on a 1D array of any size.
- Each formula must return an array of the exact size containing only the distinct, duplicate, and unique values. So as per the sample data and output below:
* The array produced by the Distinct formula will be exactly 14 elements long
* The array produced by the Unique formula will be exactly 9 elements long
* The array produced by the Duplicate formula will be exactly 5 elements long
[pre]
[/pre]
Note that the previous shortest formulas to return Distinct/Unique/Duplicate arrays for text were as follows:
=T(OFFSET(A1,SMALL(IF(FREQUENCY(MATCH(t,t,),MATCH(t,t,))>0,ROW(t)),ROW(OFFSET(A1,,,SUM(1/COUNTIF(t,t)))))-1,0))
=T(OFFSET(A1,SMALL(IF(COUNTIF(t,t)=1,ROW(t)),ROW(OFFSET(A1,,,SUM(N(COUNTIF(t,t)=1)))))-1,))
=T(OFFSET(A1,SMALL(IF(FREQUENCY(MATCH(t,t,),MATCH(t,t,))>1,ROW(t)),ROW(OFFSET(A1,,,SUM(N(FREQUENCY(MATCH(t,t,),MATCH(t,t,))>1)))))-1,))
Similarly, the previous shortest formulas to return Distinct/Unique/Duplicate arrays for numbers were as follows:
=SMALL(IF(FREQUENCY(n,n)>=1,n),ROW(OFFSET(A1,,,SUM(N(FREQUENCY(n,n)>=1)))))
=SMALL(IF(FREQUENCY(n,n)=1,n),ROW(OFFSET(K1,,,SUM(N(FREQUENCY(n,n)=1)))))
=SMALL(IF(FREQUENCY(n,n)>1,n),ROW(OFFSET(U1,,,SUM(N(FREQUENCY(n,n)>1)))))
* Formula Challenge 007 dealt with extracting an array of distinct/duplicate/unique values from a range containing only numerical values
* Formula Challenge 008 dealt with extracting an array of distinct/duplicate/unique values from a range containing only text values
Can you come up with a formula that extracts an array of distinct/duplicate/unique items from a range containing numerical and text values?
- You should assign a 1-digit named range to the original data called m (m for mixed), and refer to that within your formula. This makes it easier for me to compare formula lengths and performance on my master spreadsheet.Other than that, you should not use any named ranges or intermediate cells.
- The output of each formula must be an array that can be used directly by other formula (such as COUNTA etc). That is, we're after a single formula that you can directly incorporate into other formulas - not a formula that has to be entered over a worksheet range in order to work.
- Each formula should be completely dynamic, and able to be used on a 1D array of any size.
- Each formula must return an array of the exact size containing only the distinct, duplicate, and unique values. So as per the sample data and output below:
* The array produced by the Distinct formula will be exactly 14 elements long
* The array produced by the Unique formula will be exactly 9 elements long
* The array produced by the Duplicate formula will be exactly 5 elements long
[pre]
Code:
m Distnct Unique Dups
1 1 1 2
2 2 35 45
45 45 22 49
45 49 39 jake
49 35 23 simon
35 jake Peter #N/A
jake 22 jeff #N/A
jake 39 99 #N/A
22 23 0 #N/A
49 simon #N/A #N/A
39 Peter #N/A #N/A
23 jeff #N/A #N/A
simon 99 #N/A #N/A
Peter 0 #N/A #N/A
simon #N/A #N/A #N/A
jeff #N/A #N/A #N/A
simon #N/A #N/A #N/A
2 #N/A #N/A #N/A
99 #N/A #N/A #N/A
0 #N/A #N/A #N/A
Note that the previous shortest formulas to return Distinct/Unique/Duplicate arrays for text were as follows:
=T(OFFSET(A1,SMALL(IF(FREQUENCY(MATCH(t,t,),MATCH(t,t,))>0,ROW(t)),ROW(OFFSET(A1,,,SUM(1/COUNTIF(t,t)))))-1,0))
=T(OFFSET(A1,SMALL(IF(COUNTIF(t,t)=1,ROW(t)),ROW(OFFSET(A1,,,SUM(N(COUNTIF(t,t)=1)))))-1,))
=T(OFFSET(A1,SMALL(IF(FREQUENCY(MATCH(t,t,),MATCH(t,t,))>1,ROW(t)),ROW(OFFSET(A1,,,SUM(N(FREQUENCY(MATCH(t,t,),MATCH(t,t,))>1)))))-1,))
Similarly, the previous shortest formulas to return Distinct/Unique/Duplicate arrays for numbers were as follows:
=SMALL(IF(FREQUENCY(n,n)>=1,n),ROW(OFFSET(A1,,,SUM(N(FREQUENCY(n,n)>=1)))))
=SMALL(IF(FREQUENCY(n,n)=1,n),ROW(OFFSET(K1,,,SUM(N(FREQUENCY(n,n)=1)))))
=SMALL(IF(FREQUENCY(n,n)>1,n),ROW(OFFSET(U1,,,SUM(N(FREQUENCY(n,n)>1)))))