 SOLVED

# 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

6 Replies

# Re: Extract unique items from a list

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,"")))

# Re: Extract unique items from a list

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

# Re: Extract unique items from a list

Did you confirm it with Ctrl+Shift+Enter?

best response confirmed by allyreckerman (Microsoft)
Solution

# Re: Extract unique items from a list

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