Forum Discussion
Extract unique items from a list
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
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.
6 Replies
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.
- THANKS
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,"")))
- 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
Did you confirm it with Ctrl+Shift+Enter?
