Finding something multiple times in a column

[pryor15]

So I've been googling this for a couple hours with no luck....

I have a crazy spreadsheet omnibus. So each item has a unique code like *WTVL* and that'll show up in a list of events between 14 and 18 times. All these events are in column A. The cell containing the item code can be wherever. Let's say B1.

That unique code will show up in a larger unique event code in column A. That code might look like '43441 *WTVL* *OCEA*'. There's roughly 2,000 of those.

I'd like to return every event code, in order, that contains the *WTVL* code, but on *WTVL*'s sheet, if that makes sense. *OCEA* also has their own sheet. There's roughly 130 different codes that each have their own sheet.

It's a really, really big spreadsheet.

I'm assuming I need an Index/Match combo?

 

[Aladin Akyurek]

Let column A of the data sheet house the relevant data.

Let B1 of the WTVL sheet contain WTVL.

In B2 just enter:

=COUNTIFS(data!$A$2:$A$20,"*"&B1&"*")

In B3 control+shift+enter, not just enter, and copy down:

=IF(ROWS($B$3:B3)>$B$2,"",INDEX(data!$A$2:$A$20,SMALL(IF(ISNUMBER(SEARCH($B$1,data!$A$2:$A$20)),ROW(data!$A$2:$A$20)-ROW(data!$A$2)+1),ROWS($B$3:B3))))

 

[pryor15]

Thanks!

Small problem: if found literally none of entries with WTVL. It did know how many times WTVL was in the column.

 

[pryor15]

Nevermind!

It's working now. I must have typed something wrong

 

[Aladin Akyurek]

Thanks!

Small problem: if found literally none of entries with WTVL. It did know how many times WTVL was in the column.

Nevermind!

It's working now. I must have typed something wrong

No problem. Thanks for the update.