Multiple Match with No Repeats

[seguin85]

I tried googling this but didn't know exactly how to ask it.

I want it to return:
123-08932
123-08940
123-08947
123-09013

from this list of items:

123-08932-05
123-08932-06
123-08932-07
123-08932-99
123-08940-01
123-08940-01
123-08940-99
123-08947-99
123-09013-01
123-09013-01
123-09013-02
123-09013-02
123-09013-03
123-09013-03
123-09013-04
123-09013-04
123-09013-05
123-09013-06
123-09013-07

 

[Fluff]

How about

+Fluff 1.xlsm
	E	F
1
2	123-08932-05	123-08932
3	123-08932-06	123-08940
4	123-08932-07	123-08947
5	123-08932-99	123-09013
6	123-08940-01
7	123-08940-01
8	123-08940-99
9	123-08947-99
10	123-09013-01
11	123-09013-01
12	123-09013-02
13	123-09013-02
14	123-09013-03
15	123-09013-03
16	123-09013-04
17	123-09013-04
18	123-09013-05
19	123-09013-06
20	123-09013-07
21
Master

Cell Formulas
Range	Formula
F2:F5	F2	=UNIQUE(LEFT(E2:E20,9))
Dynamic array formulas.

 

[seguin85]

How about

+Fluff 1.xlsm
	E	F
1
2	123-08932-05	123-08932
3	123-08932-06	123-08940
4	123-08932-07	123-08947
5	123-08932-99	123-09013
6	123-08940-01
7	123-08940-01
8	123-08940-99
9	123-08947-99
10	123-09013-01
11	123-09013-01
12	123-09013-02
13	123-09013-02
14	123-09013-03
15	123-09013-03
16	123-09013-04
17	123-09013-04
18	123-09013-05
19	123-09013-06
20	123-09013-07
21
Master

Cell Formulas
Range	Formula
F2:F5	F2	=UNIQUE(LEFT(E2:E20,9))
Dynamic array formulas.

My excel version doesn't include "unique", also I forgot to mention the first 3 digits might be different then 123, but I only want to return items that start with 123.

Thanks!

 

[Saba Sabaratnam]

Hi

Another way to extract may be using the following array formula.

=IFERROR(INDEX(IF(LEFT($A$1:$A$100,3)="123",MID($A$1:$A$100,1,FIND("-",$A$1:$A$100,5)-1),),MATCH(0,COUNTIFS($C$1:C1,IF(LEFT($A$1:$A$100,3)="123",MID($A$1:$A$100,1,FIND("-",$A$1:$A$100,5)-1),)),0)),0)

This is the layout of my spreadsheet

Enter the above formula in in C2 by pressing Control + Shift + Enter and drag it down.

Kind regards

Saba

 

[seguin85]

Hi

Another way to extract may be using the following array formula.

=IFERROR(INDEX(IF(LEFT($A$1:$A$100,3)="123",MID($A$1:$A$100,1,FIND("-",$A$1:$A$100,5)-1),),MATCH(0,COUNTIFS($C$1:C1,IF(LEFT($A$1:$A$100,3)="123",MID($A$1:$A$100,1,FIND("-",$A$1:$A$100,5)-1),)),0)),0)

This is the layout of my spreadsheet

Enter the above formula in in C2 by pressing Control + Shift + Enter and drag it down.


View attachment 31592

Kind regards

Saba

Thank you Saba! That seems to be working perfectly!