danthemantaylor
New Member
- Joined
- Feb 25, 2015
- Messages
- 6
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!
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!