Extract unique items from a list

[FANIMOF]

Please help me, I want to use formula to extract numbers that has 4 or 5 at the beginning of a value. Thanks in advance.

Example:

15677

16577

37588

46777

24689

57438

68495

SOLUTION:

46777

57438

[#2]

@FANIMOF 

Let's say your numbers are in A2:A8.

1) A formula that works in all versions of Excel.

Enter the following formula in C2, and confirm it with Ctrl+Shift+Enter (this is essential):

=IFERROR(INDEX($A$2:$A$8, MATCH(0, IF((LEFT($A$2:$A$8)="4")+(LEFT($A$2:$A$8)="5"), COUNTIF($C$1:$C1, $A$2:$A$8), ""), 0)), "")

Fill down as far as you want.

2) A formula that works in Excel 365 only.

Enter the following formula in C2:

=SORT(UNIQUE(IF((LEFT($A$2:$A$8)="4")+(LEFT($A$2:$A$8)="5"),$A$2:$A$8,"")))

[#3]

Thanks for prompt reply, however, it returns first value correctly but it just repeated the same value in the second cell etc as I drag the formular down

[#4]

@FANIMOF 

[#5]

@FANIMOF 

Did you confirm it with Ctrl+Shift+Enter?

[#6 · Accepted solution]

@FANIMOF 

Since you are sorting and filtering numbers you could use

= SORT(FILTER(numbers, (numbers<60000)*(numbers>=40000)))

in Excel 365 or

= IFERROR(
    SMALL(
      IF(
        (numbers<60000)*(numbers>=40000),
         numbers
      ), 
      1+ROW()-MIN(ROW(numbers))
    ),
  "")

in legacy systems.

[#7]

THANKS