I can't figure out how to transpose unique values based on specific critera in Google Sheets-CodePudding

I'm trying to create an inventory system for my business. For example, our SKU codes start with "LMS" "RSP" and "CON" for specific locations where we're working.

I'm trying to take all unique "LMS" codes, and copy them to a single sheet from our raw scan data. I've tried variations on REGEXMATCH, and UNIQUE, and TRANSPOSE, IF statements, and , but I'm totally lost. This is not my normal preview.

For example, I've been trying things like:

=FILTER(RAW!B2:B,RAW!B2:B=UNIQUE(RAW!B2:B),REGEXMATCH(RAW!B2:B,"LMS"))

and

=IF(REGEXMATCH(RAW!B2:B,"LMS"),(TRANSPOSE(RAW!B2:B)),"error")

The second example returns all values in RAW!B, while the first one does nothing. Any help would be appreciated.

CodePudding user response：

Try below formula-

=UNIQUE(FILTER(Raw!B2:B,REGEXMATCH(Raw!B2:B,"LMS")))

Or QUERY() function with wild card match-

=UNIQUE(QUERY(Raw!B2:B,"select B where B like 'LMS%'"))