CODEWITHSUNDEEP
google-sheets
FindTheWay
FindTheWay

Reputation: 13

Filtering data that will only filter based on if it matches with the first few letters instead of matching everything

From a list of items that have been sold, I am trying to filter out who sold what based on the unique ID number everyone is using on the item.

For example:

Worker 1 Unique ID- Naya

Worker 2 Unique ID- Rena

Worker 3 Unique ID- Ken

So everything they sell will have the unique ID + "0001".

For Example: Naya0001, Naya0002, Rena0007, Ken0103, Ken1101, Ken0001 and so on

The issue I'm running into is that I am trying to use the starting words and emit the number part on my drop down table

Drop-down menu: Naya, Rena, Ken

The Main data is in a sheet called "YApr-22". And I am using the formula

=FILTER('YApr-22'!A3:AF700,'YApr-22'!I3:I700=J1) #to pull the data onto another sheet.

The formula works if I input the numbers as well. Like if I look for "Rena0007" instead of just "Rena".

Is there any workaround or another solution to this where only the name of the person will be cross-checked if data exists and pulled so I can see all the different variations of sales made by the same person?

Upvotes: 0

Views: 87

Answers (1)

Erik Tyler
Erik Tyler

Reputation: 9345

If I'm understanding you correctly, try this:

=FILTER('YApr-22'!A3:AF700,REGEXMATCH('YApr-22'!I3:I700,"(?i)"&J1))

-or-

=FILTER('YApr-22'!A3:AF700,REGEXMATCH(LOWER('YApr-22'!I3:I700),LOWER(J1)))

The inclusion of (?i) or LOWER is to add a measure of error proofing, so that, "ken" matches "KEN" or "Ken." Some functions are case agnostic, but REGEX is inherently not.

Upvotes: 1

Related Questions