INDEX-MATCH Unique List with Criteria

danthemantaylor

New Member
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!

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

FDibbins

Well-known Member
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

New Member
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

Well-known Member
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

New Member
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

Banned
This should do it:

Excel 2016 (Windows) 32 bit
ABC
1BC
22TuesdayTuesday
33WednesdayThursday
44Thursday
55Friday
66Saturday
77Sunday
88Monday
99Tuesday
1010Wednesday
1111Thursday
Sheet3
Cell Formulas
RangeFormula
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

New Member
This should do it:

Excel 2016 (Windows) 32 bit
ABC
1BC
22TuesdayTuesday
33WednesdayThursday
44Thursday
55Friday
66Saturday
77Sunday
88Monday
99Tuesday
1010Wednesday
1111Thursday

</tbody>
Sheet3

Worksheet Formulas
CellFormula
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)))),"")

</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

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

FDibbins

Well-known Member
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

Replies
1
Views
194
Replies
6
Views
294
Replies
22
Views
442
Replies
3
Views
301
Replies
8
Views
155

1,191,054
Messages
5,984,384
Members
439,883
Latest member
onions44

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back