SOLVED

Extract unique items from a list

%3CLINGO-SUB%20id%3D%22lingo-sub-2722480%22%20slang%3D%22en-US%22%3EExtract%20unique%20items%20from%20a%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2722480%22%20slang%3D%22en-US%22%3E%3CP%3EPlease%20help%20me%2C%20I%20want%20to%20use%20formula%20to%20extract%20numbers%20that%20has%204%20or%205%20at%20the%20beginning%20of%20a%20value.%20Thanks%20in%20advance.%3C%2FP%3E%3CP%3EExample%3A%3C%2FP%3E%3CP%3E15677%3C%2FP%3E%3CP%3E16577%3C%2FP%3E%3CP%3E37588%3C%2FP%3E%3CP%3E46777%3C%2FP%3E%3CP%3E24689%3C%2FP%3E%3CP%3E57438%3C%2FP%3E%3CP%3E68495%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESOLUTION%3A%3C%2FP%3E%3CP%3E46777%3C%2FP%3E%3CP%3E57438%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2722480%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2722510%22%20slang%3D%22en-US%22%3ERe%3A%20Extract%20unique%20items%20from%20a%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2722510%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1141209%22%20target%3D%22_blank%22%3E%40FANIMOF%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet's%20say%20your%20numbers%20are%20in%20A2%3AA8.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E1)%20A%20formula%20that%20works%20in%20all%20versions%20of%20Excel.%3C%2FP%3E%0A%3CP%3EEnter%20the%20following%20formula%20in%20C2%2C%20and%20confirm%20it%20with%20Ctrl%2BShift%2BEnter%20(this%20is%20essential)%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIFERROR(INDEX(%24A%242%3A%24A%248%2C%20MATCH(0%2C%20IF((LEFT(%24A%242%3A%24A%248)%3D%224%22)%2B(LEFT(%24A%242%3A%24A%248)%3D%225%22)%2C%20COUNTIF(%24C%241%3A%24C1%2C%20%24A%242%3A%24A%248)%2C%20%22%22)%2C%200))%2C%20%22%22)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFill%20down%20as%20far%20as%20you%20want.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E2)%20A%20formula%20that%20works%20in%20Excel%20365%20only.%3C%2FP%3E%0A%3CP%3EEnter%20the%20following%20formula%20in%20C2%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DSORT(UNIQUE(IF((LEFT(%24A%242%3A%24A%248)%3D%224%22)%2B(LEFT(%24A%242%3A%24A%248)%3D%225%22)%2C%24A%242%3A%24A%248%2C%22%22)))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2722793%22%20slang%3D%22en-US%22%3ERe%3A%20Extract%20unique%20items%20from%20a%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2722793%22%20slang%3D%22en-US%22%3EThanks%20for%20prompt%20reply%2C%20however%2C%20it%20returns%20first%20value%20correctly%20but%20it%20just%20repeated%20the%20same%20value%20in%20the%20second%20cell%20etc%20as%20I%20drag%20the%20formular%20down%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2722814%22%20slang%3D%22en-US%22%3ERe%3A%20Extract%20unique%20items%20from%20a%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2722814%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1141209%22%20target%3D%22_blank%22%3E%40FANIMOF%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2722818%22%20slang%3D%22en-US%22%3ERe%3A%20Extract%20unique%20items%20from%20a%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2722818%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1141209%22%20target%3D%22_blank%22%3E%40FANIMOF%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDid%20you%20confirm%20it%20with%20Ctrl%2BShift%2BEnter%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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

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

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

@FANIMOF 

Did you confirm it with Ctrl+Shift+Enter?

best response confirmed by allyreckerman (Microsoft)
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.

THANKS