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.
Gary''s Student" wrote:
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
--
Gary's Student
"Shu of AZ" wrote:
my mistake, I do
"Gary''s Student" wrote:
Why don't you want the 9??
--
Gary's Student
"Shu of AZ" wrote:
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