From smallest to largest without repeating

[vacho]

Hello,

I have data consisting of 20 numbers from 1 to 63. Within 20 some numbers can appear twice or even more. I want excel to sort this numbers in smallest to largest format ignoring repeating numbers.

Thank you

 

[Teethless mama]

Slightly shorter from post #10

Excel Workbook
	A	B
2	7	7
3	59	8
4	38	12
5	12	15
6	36	27
7	29	29
8	34	32
9	15	34
10	40	36
11	58	38
12	12	40
13	8	49
14	38	57
15	32	58
16	57	59
17	7
18	36
19	15
20	27
21	49
Sheet1

 

[mirabeau]

Sorry, I forgot to mention that my data are scattered and I want to paste them in one column

try this macro. it assumes your data are scattered down column A, if otherwise post back details. also assumes your data are integers, you didn't explicitly specify this

Sub sjsj()
Dim a as Range, b(63) As Boolean, c As Variant, d As Long
Set a = Intersect(ActiveSheet.UsedRange, Range("A:A"))
For Each c In a
    If Len(c) > 0 Then b(c) = True
Next
a.ClearContents
For c = 1 To 63
    If b(c) Then d = d + 1: a(d) = c
Next c
End Sub

 

[vacho]

Thanks everyone and I apologize for incomplete definition of my problem. This image should make things clearer </SPAN>

 

[T. Valko]

Thanks everyone and I apologize for incomplete definition of my problem. This image should make things clearer </SPAN>

Try this...

Enter this formula in V22. This will return the count of unique numbers in the ranges of interest.

=SUM(--(FREQUENCY((D22:H22,D29:H29,D36:H36,D43:H43),(D22:H22,D29:H29,D36:H36,D43:H44))>0))

Enter this formula in V24:

=MIN(D22:H22,D29:H29,D36:H36,D43:H43)

Enter this array formula** in V25:

=IF(ROWS(V$24:V25)>V$22,"",MIN(IF(MOD(ROW(D$22:H$43)-ROW(D$22),7)=0,IF(D$22:H$43>V24,D$22:H$43))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down until you get blanks.

 

[T. Valko]

Try this...

Enter this formula in V22. This will return the count of unique numbers in the ranges of interest.

=SUM(--(FREQUENCY((D22:H22,D29:H29,D36:H36,D43:H43),(D22:H22,D29:H29,D36:H36,D43:H44))>0))

Enter this formula in V24:

=MIN(D22:H22,D29:H29,D36:H36,D43:H43)

Enter this array formula** in V25:

=IF(ROWS(V$24:V25)>V$22,"",MIN(IF(MOD(ROW(D$22:H$43)-ROW(D$22),7)=0,IF(D$22:H$43>V24,D$22:H$43))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down until you get blanks.

Here's a non-array formula option.

Entered in V25 and copied down until you get blanks:

=IF(ROWS(V$24:V25)>V$22,"",SMALL((D$22:H$22,D$29:H$29,D$36:H$36,D$43:H$43),FREQUENCY((D$22:H$22,D$29:H$29,D$36:H$36,D$43:H$43),V24)+1))

 

[vacho]

Here's a non-array formula option.

Entered in V25 and copied down until you get blanks:

=IF(ROWS(V$24:V25)>V$22,"",SMALL((D$22:H$22,D$29:H$29,D$36:H$36,D$43:H$43),FREQUENCY((D$22:H$22,D$29:H$29,D$36:H$36,D$43:H$43),V24)+1))

this pormula does the trick until i get to the last value. for some reason 51 didn't display. Also can I move V22 value to another place in row24 for example?

Thanks anyway. You have already given enough info for me to continue

 

[T. Valko]

this pormula does the trick until i get to the last value. for some reason 51 didn't display. Also can I move V22 value to another place in row24 for example?

Yes, you can put the formula in cell V22 anywhere you want.

Tell me exactly where you want that and where you want the other results to appear.

What version of Excel are you using?

 

[mirabeau]

seems that you want a formula for this job.

however, just for interest, you may like to run this brief macro...easy to modify in whatever way

Sub sorted_norepeat()

Dim b(63) As Boolean, c As Long
Dim d As Long, e As Variant

For c = 22 To 43 Step 7
    For Each e In Cells(c, "d").Resize(, 5)
        b(e) = True
    Next
Next

For c = 1 To 63
    If b(c) Then d = d + 1: Cells(24, "v")(d) = c
Next c

End Sub

 

[vacho]

Yes, you can put the formula in cell V22 anywhere you want.

Tell me exactly where you want that and where you want the other results to appear.

What version of Excel are you using?

Thanks, I moved the formula in an empty cell and also added -1 after ROWS(V$24:V25) in If statement as shown below and now it works just fine. I am using 2010 version.

=IF(ROWS(V$24:V25)-1>U$24,"",SMALL((D$22:H$22,D$29:H$29,D$36:H$36,D$43:H$43),FREQUENCY((D$22:H$22,D$29:H$29,D$36:H$36,D$43:H$43),V24)+1))

there is no way I could have thought of this solution, so thanks again

 

[vacho]

Thank mirabeau, I will give it a try this evening. The reason why I don't want macros is, I have to activate it each time numbers change. Formulas change automatically