CODEWITHSUNDEEP
sortinggoogle-sheetsconcatenationgoogle-sheets-formulagoogle-sheets-query
Manuel
Manuel

Reputation: 2371

How can I filter a sheet to show only one instance of a particular substring?

I have a spreadsheet where the values in a column are texts with some common patterns.

M03 D12
M03 D11
M04 D11
M02 D20

I'd like to filter this sheet so that I see only one appearance of a particular subtext instead of the whole text. For instance, by "M" and the number that follows.

M03 D11
M04 D11
M02 D20

Or by "D" and the number that follows.

M03 D12
M03 D11
M02 D20

I tried to use a custom function along the lines of =COUNTIF(A2:A, "M03*")=1. What this does is it filters out everything except the last occurrence of M03, as the range in COUNTIF is not fixed so the last occurrence of M03 would be counted only once. The problem is that this works only for strings containing M03. I'd like to do this together with M04, M02, and any other subtext of my choosing.

Upvotes: 1

Views: 106

Answers (1)

player0
player0

Reputation: 1

=TRANSPOSE(QUERY(TRANSPOSE(SORTN(IFERROR(SPLIT(
 SORT(A:A, 1, 1), " ")), ROWS(A:A), 2, 1, 0)),,999^99))

0

=TRANSPOSE(QUERY(TRANSPOSE(SORTN(IFERROR(SPLIT(
 SORT(A:A, 1, 1), " ")), ROWS(A:A), 2, 2, 1)),,999^99))

0

Upvotes: 1

Related Questions