| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filtering Duplicates
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Filtering Duplicates
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Filtering Duplicates
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Filtering Duplicates
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Filtering Duplicates
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(FREQUENC Y(SMALL($A$1:$E$3,ROW(INDIRECT("1:15"))),SMALL($A$ 1:$E$3,ROW(INDIRECT("1:15"))))0,SMALL($A$1:$E$3,R OW(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(FREQUENC Y(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:"&COU NT($A$1:$E$3)))),""),COLUMN()-COLUMN($H$1)+1),"") "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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Filtering Duplicates
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Filtering Duplicates
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 -- 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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Filtering Duplicates
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(FREQUENC Y(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),"") "JMB" wrote: 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(FREQUENC Y(SMALL($A$1:$E$3,ROW(INDIRECT("1:15"))),SMALL($A$ 1:$E$3,ROW(INDIRECT("1:15"))))0,SMALL($A$1:$E$3,R OW(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(FREQUENC Y(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:"&COU NT($A$1:$E$3)))),""),COLUMN()-COLUMN($H$1)+1),"") "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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Filtering Duplicates
Another note, there may only be 4 unique numbers also but never more than 9
in the 12 possible cell values. "JMB" wrote: 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(FREQUENC Y(SMALL($A$1:$E$3,ROW(INDIRECT("1:15"))),SMALL($A$ 1:$E$3,ROW(INDIRECT("1:15"))))0,SMALL($A$1:$E$3,R OW(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(FREQUENC Y(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:"&COU NT($A$1:$E$3)))),""),COLUMN()-COLUMN($H$1)+1),"") "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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Filtering Duplicates
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 -- Gary's Student "Shu of AZ" wrote: 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 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Filtering Duplicates
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. "JMB" wrote: 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(FREQUENC Y(SMALL($A$1:$E$3,ROW(INDIRECT("1:15"))),SMALL($A$ 1:$E$3,ROW(INDIRECT("1:15"))))0,SMALL($A$1:$E$3,R OW(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(FREQUENC Y(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:"&COU NT($A$1:$E$3)))),""),COLUMN()-COLUMN($H$1)+1),"") "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 |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Filtering Duplicates
Good to hear you got it work for you (because I'm not sure how much more help
I could have offered you). "Shu of AZ" wrote: 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. "JMB" wrote: 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(FREQUENC Y(SMALL($A$1:$E$3,ROW(INDIRECT("1:15"))),SMALL($A$ 1:$E$3,ROW(INDIRECT("1:15"))))0,SMALL($A$1:$E$3,R OW(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(FREQUENC Y(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:"&COU NT($A$1:$E$3)))),""),COLUMN()-COLUMN($H$1)+1),"") "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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Help: Sorting 2 columns according to matching cells, and fishing for duplicates | Excel Discussion (Misc queries) | |||
| Excel appending to Access - no duplicates | Excel Discussion (Misc queries) | |||
| Excel 2003; spreadsheet with filtering; deleting rows | Excel Worksheet Functions | |||
| Filtering a list with duplicates | Excel Worksheet Functions | |||
| Finding Duplicates | Excel Worksheet Functions | |||
Linear Mode
