Filtering Duplicates

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

These are numbers in cells 5 columns wide and 3 rows down

7 4 2 11 3
7 4 6 11 3
4 7 9 3 11

In another row of cells, I would like to display the smallest to the largest
numbers in the entire range, i.e. without showing the duplicates

2 3 4 6 7 11

Is there a way to filter this?
Thanks
 
I put your data from A1 thru E3. I am using column N as a "helper column"
(you can use any un-used column past N). The results appear in row 5.


The macro:

1 copies the array to a single column
2 sorts the column
3 performs an advanced filter to get only the uniques
4 copies the results to row 5


Sub shu()
Columns("N:N").Clear
k = 2
For i = 1 To 4
For j = 1 To 3
Cells(k, "N").Value = Cells(j, i).Value
k = k + 1
Next
Next
Range("N2:N13").Sort Key1:=Range("N2")
Cells(1, "N").Value = "top"
Range("N1:N13").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"N14"), Unique:=True
k = 1
For i = 15 To 30
Cells(5, k).Value = Cells(i, "N").Value
k = k + 1
If Cells(k, "N").Value = "" Then
Exit Sub
End If
Next
Columns("N:N").Clear
End Sub


If you are not familiar with using macros, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
Probably a better way, but my mind is struggling a bit tonight to find
something shorter, so.....

If your data is in A1:E3 enter this *array* formula is entered in H1 (using
Cntrl+Shift+Enter), then copy across until you get #NUM error.

=SMALL(IF(FREQUENCY(SMALL($A$1:$E$3,ROW(INDIRECT("1:15"))),SMALL($A$1:$E$3,ROW(INDIRECT("1:15"))))>0,SMALL($A$1:$E$3,ROW(INDIRECT("1:15"))),""),COLUMN()-COLUMN($H1)+1)

change range and cell references where needed. If the size of your range
varies (ie has more or less than 15 cells), change INDIRECT("1:15") to
INDIRECT("1:"&COUNT($A$1:$E$1)).

If you'd rather the cells be "" instead of #NUM then try:

=IF(COLUMNS($H1:H1)<=SUMPRODUCT(($A$1:$E$3<>"")/COUNTIF($A$1:$E$3,$A$1:$E$3&"")),SMALL(IF(FREQUENCY(SMALL($A$1:$E$3,ROW(INDIRECT("1:15"))),SMALL($A$1:$E$3,ROW(INDIRECT("1:15"))))>0,SMALL($A$1:$E$3,ROW(INDIRECT("1:15"))),""),COLUMN()-COLUMN($H$1)+1),"")


If your data can have blank cells in it, then try:
=IF(COLUMNS($H1:H1)<=SUMPRODUCT(($A$1:$E$3<>"")/COUNTIF($A$1:$E$3,$A$1:$E$3&"")),SMALL(IF(FREQUENCY(SMALL($A$1:$E$3,ROW(INDIRECT("1:"&COUNT($A$1:$E$3)))),SMALL($A$1:$E$3,ROW(INDIRECT("1:"&COUNT($A$1:$E$3)))))>0,SMALL($A$1:$E$3,ROW(INDIRECT("1:"&COUNT($A$1:$E$3)))),""),COLUMN()-COLUMN($H$1)+1),"")
 
Thanks Garys Student,

The macro works great but in the huge workbook this is in, the array lies in
S5:V9 and the display needs to occur in R27,S28,T29, R29,S29,T29,
R31,S31,T31 if there is 9 unique numbers in the array, if there is not 9
uniqued numbers, then either the cells without need to remain blank or
display a zero. There is rarely 9, I have only seen it once in 1435 tests.
In the macro you created for me, I could not tell where the array was defined
so as to be able to address the location. N is blank in the first 188 rows
so that worked.
Is there a formula or something that may make this automatic without our
users having to perform a macro?
I know this is probably a stupid question but could not R27 look at the
array, find the lowest number and display it, then S28 would do the same
thing but not display the same number as R28 and so on until T31 was
completed?

Just a note, the array I'm dealing with gets its values from tables
EU210:FA320 which are results of other formula's.
 
There was an error in the first posting. It only handled 4x3 rather than
5x3. Here is the corrected code:

Sub shu()
Columns("S:S").Clear
k = 2
For i = 1 To 5
For j = 1 To 3
Cells(k, "S").Value = Cells(j, i).Value
k = k + 1
Next
Next
Range("S2:S16").Sort Key1:=Range("S2")
Cells(1, "S").Value = "top"
Range("S1:S16").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"S17"), Unique:=True

k = 1
For i = 18 To 40
Cells(5, k).Value = Cells(i, "S").Value
k = k + 1
If Cells(i, "S").Value = "" Then
Exit Sub
End If
Next
Columns("S:S").Clear
End Sub


The helper column is now S
 
Thanks JMB, I like your formula. I tried putting in the proper cell
relationships using the formula but keep getting an error. I'm able to
reduce the amount of the array to 12, 4 across and 3 down now. S5:V9. I
also have a place to display the numbers, either in 9 cells, 3 across and 3
down, ( merged ) which is located r27,s27,t27, , r29,s29,t29, ,and
r31,s31,t31 or I and display them in one merged cell of one of those threes
rows. Below is my failed attempt. A note is there will never be more than 9
unique numbers in this array, least I have never seen a time. The remainder
cells can be blank or I can cover the Num ISerror in the conditional format.
Either way works, Thanks for your help.

=IF(COLUMNS($r29:r29)<=SUMPRODUCT(($s$5:$v$9<>"")/COUNTIF($s$5:$v$9,$s$5:$v$9&"")),SMALL(IF(FREQUENCY(SMALL($s$5:$v$9,ROW(INDIRECT("1:"&COUNT($s$5:$v$9))),SMALL($s$5:$v$9,ROW(INDIRECT("1:"&COUNT($s$5:$v$9))>0,SMALL($s$5:$v$9,ROW(INDIRECT("1:"&COUNT($s$5:$v$9))),""),COLUMN()-COLUMN($r$29)+1),"")
 
Another note, there may only be 4 unique numbers also but never more than 9
in the 12 possible cell values.
 
I could not come up ith a pure worksheet solution. If someone else comes up
with a satisfactory worksheet solution, then you should use it.

If you don't get a satisfactory solution, I can update the code again to use
Named Ranges for the inputs and results. This means that instead of the
input table being A1 thru E3, it can be any size,shape, or location.

Just update the post if you need more help from me.


by the way this was an interesting problem
 
Thanks tons JMB, I got it to work perfectly and just had it display where I
wanted it to. Thanks again, you're always a great help.
 
Good to hear you got it work for you (because I'm not sure how much more help
I could have offered you).
 
Back
Top