INDEX-MATCH Unique List with Criteria

[danthemantaylor]

Hi friends!

I'm trying to build a workbook where my collegues only need to enter raw data in backend sheets and the rest is summarised in the first few pages.

Sheet 1 will be a summary of everything in the workbook with unique item codes contained in column B. In Sheet 2 Column B I am trying to pull out a list of codes from Sheet 1 beginning with "EX". Item codes are along the lines of: EX001,EX002, IN001, RD002 etc.

I'm basically running from: How to extract a unique distinct list from a column in excel | Get Digital Help - Microsoft Excel resource as I've never really done this before... and it doesn't seem to be working.

I've tried using the INDEX-MATCH formula below to pull the list out but the below just gives back a "0" answer:

'Sheet1'!B7:B43 = list

{=INDEX(list,MATCH(0,(ISERROR(SEARCH("EX*",list)))*(COUNTIF($B$7:B7,list)),0))}

I tried going back a step to just getting some kind of list but still got a "0":

{=INDEX('Sheet1'!B7:B43,MATCH(0,COUNTIF($B$7:B7,'Sheet1'!B7:B43),0))}

Not sure if I'm on the right track or not so any help would be greatly appreciated!

 

[FDibbins]

See if this will get you started, to extract unique values...

	B​	C​
2​	Tuesday	Tuesday
3​	Wednesday	Wednesday
4​	Thursday	Thursday
5​	Friday	Friday
6​	Saturday	Saturday
7​	Sunday	Sunday
8​	Monday	Monday
9​	Tuesday
10​	Wednesday
11​	Thursday

C2=IFERROR(INDEX($B$2:$B$26,MATCH(0,INDEX(COUNTIF($C$1:C1,$B$2:$B$26),),0)),"")
regular formula, using just enter
then copy down as needed

 

[danthemantaylor]

See if this will get you started, to extract unique values...

	B​	C​
2​	Tuesday	Tuesday
3​	Wednesday	Wednesday
4​	Thursday	Thursday
5​	Friday	Friday
6​	Saturday	Saturday
7​	Sunday	Sunday
8​	Monday	Monday
9​	Tuesday
10​	Wednesday
11​	Thursday

<tbody>
</tbody>

C2=IFERROR(INDEX($B$2:$B$26,MATCH(0,INDEX(COUNTIF($C$1:C1,$B$2:$B$26),),0)),"")
regular formula, using just enter
then copy down as needed

Thanks Ford! That provided the basic list, now I just need to figure out how to only pull out specific items (e.g. days beginning with T)

 

[FDibbins]

Probably the simplest way would be to use a helper column (that you can hide), then base the unique on that...

	B​	C​	D​
2​	Tuesday		Saturday
3​	Wednesday		Sunday
4​	Thursday		0
5​	Friday
6​	Saturday	Saturday
7​	Sunday	Sunday
8​	Monday
9​	Tuesday
10​	Wednesday
11​	Thursday

C2=IF(LEFT(B2,1)="s",B2,"")
D2=IFERROR(INDEX($C$2:$C$26,MATCH(0,INDEX(COUNTIF($D$1:D1,$C$2:$C$26),),0)),"")
both copied down as needed

 

[danthemantaylor]

Probably the simplest way would be to use a helper column (that you can hide), then base the unique on that...

	B​	C​	D​
2​	Tuesday		Saturday
3​	Wednesday		Sunday
4​	Thursday		0
5​	Friday
6​	Saturday	Saturday
7​	Sunday	Sunday
8​	Monday
9​	Tuesday
10​	Wednesday
11​	Thursday

<tbody>
</tbody>

C2=IF(LEFT(B2,1)="s",B2,"")
D2=IFERROR(INDEX($C$2:$C$26,MATCH(0,INDEX(COUNTIF($D$1:D1,$C$2:$C$26),),0)),"")
both copied down as needed

Thanks for the suggestion - i'm trying to avoid using additional columns and would like to have it all operate from one formula if thats at all possible...

 

[AliGW]

This should do it:

Excel 2016 (Windows) 32 bit
	A	B	C
1		B	C
2	2	Tuesday	Tuesday
3	3	Wednesday	Thursday
4	4	Thursday
5	5	Friday
6	6	Saturday
7	7	Sunday
8	8	Monday
9	9	Tuesday
10	10	Wednesday
11	11	Thursday
Sheet3

Cell Formulas
Range	Formula
C2	=IFERROR(IF(COUNTIF(C$1:C1,INDEX($B$1:$B$11,SMALL(IF(LEFT($B$2:$B$11,1)="T",ROW($B$2:$B$11)),ROWS($C$1:C1))))>0,"",INDEX($B$1:$B$11,SMALL(IF(LEFT($B$2:$B$11,1)="T",ROW($B$2:$B$11)),ROWS($C$1:C1)))),"")

 

[danthemantaylor]

This should do it:

Excel 2016 (Windows) 32 bit

	A	B	C
1		B	C
2	2	Tuesday	Tuesday
3	3	Wednesday	Thursday
4	4	Thursday
5	5	Friday
6	6	Saturday
7	7	Sunday
8	8	Monday
9	9	Tuesday
10	10	Wednesday
11	11	Thursday

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet3​

Worksheet Formulas Cell Formula C2 =IFERROR(IF(COUNTIF(C$1:C1,INDEX($B$1:$B$11,SMALL(IF(LEFT($B$2:$B$11,1)="T",ROW($B$2:$B$11)),ROWS($C$1:C1))))>0,"",INDEX($B$1:$B$11,SMALL(IF(LEFT($B$2:$B$11,1)="T",ROW($B$2:$B$11)),ROWS($C$1:C1)))),"") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Thanks for the help AliGW!

I think i solved my problem by using an ISNUMBER(SEARCH) function as it returns a TRUE value in the IF statement

{=IFERROR(INDEX(list,MATCH(0,IF((ISNUMBER(SEARCH("EX",list))),COUNTIF($B$6:B6,list),""),0)),"-")}

 

[AliGW]

Glad to have helped! No idea how your solution worked based on the data provided, but glad you sorted it out.

 

[FDibbins]

danthemantaylor there is no need to quote whole post when you reply, only do that is there is a specific part you need to refer to - otherwise, it just clutters up the thread