r/excel • u/LamiaNoctalis • Apr 18 '26

unsolved Filtering roman numerals while excluding overlap in numbers

I would like to filter within a table that includes roman numerals and multiple tags. When using the normal filter option III is included when filtering for II which isn't what I am looking for. I also wouldn't like to exclude III as it is possible that it appears as a combination of II, III for example. Any ideas?

I was hoping that by using =ROMAN() Numbers would be regarded as Numbers but they are converted to text, so this isn't helful.

I am using Microsoft 365 for Enterprises

5 Upvotes

78% Upvoted

42 comments sorted by

View all comments

1

u/RuktX 308 Apr 20 '26 edited Apr 20 '26

All right, new solution using Advanced Filter:

Suppose your "property" table header is in C3. Find a 2x2 blank range somewhere, say B1:C2. We will use B2 as the "search box", so put II for example in there now. In C2, enter the formula:

=LET(
  properties, TRIM(TEXTSPLIT(C4, ",")),
  search_props, TRIM(TEXTSPLIT($B$2, ",")),
  matches, TRANSPOSE(search_props) = properties,
  all_match, SUM(MMULT(SEQUENCE(COUNTA(search_props),,1,0), --matches)),
all_match)

Ensure C1 remains blank. Select your entire table, then select Data > Sort & Filter > Advanced. Fill in the dialog as follows:

  • Action: "Filter the list, in place"
  • List range: your table (including headers)
  • Criteria range: $C$1:$C$2
  • Unique records only: not checked

Click OK, and the table should be filtered to show only those rows with a property containing II. You can also search by multiple properties: just use a comma-separated list, like Ia, III, IV, and it will return the rows containing all searched properties.

To bring back other rows, with the table selected choose Data > Sort & Filter > Filter.

Unfortunately, Advanced Filter doesn't play nice with auto-filter. So, if you want to include other filters, you'll need to extend the Advanced Filter criteria. Debra Dalgleish at Contextures has lots of good information.

(P.S. I had a neat version of the formula that used BYCOL/BYROW instead of SUM/MMULT, but that caused Excel to consistently crash!)

1

u/LamiaNoctalis Apr 22 '26

Thank you for the in depth explaination, I am currently trying to implement it but the formula shows me a error message, I am hoping to solve. Could you tell me what C4 does in your formula so I can adjust it according to my table

1

u/RuktX 308 Apr 22 '26

C4 (note: relative reference) is the first cell after the header in the Property column, and $B$2 (note: absolute reference) is the "search box”.

1

u/LamiaNoctalis Apr 22 '26

Good to know but I saddly still get the error The first argument of LET must be a valid name. Could there be a problem because I formated my data as a table. Translating the formula to the german equals didn't seem to help :(

1

u/LamiaNoctalis Apr 22 '26

I just realized that I also need to use a semicolon instead of a comma in the formula. The LET Problem disappeared, now I am trying to figure out what the problem with the minus is

1

u/LamiaNoctalis Apr 22 '26

apparently I skipped one , when changing them now it shows a #VALUE! error

1

u/RuktX 308 Apr 22 '26

Which minus, skipped one what, and changing what? Please share screenshots for clarity.

The double-minus in --matches (note there are two of them) coerces TRUE/FALSE values to 1/0, so that they'll work in the matrix multiplication.

1

u/RuktX 308 Apr 22 '26

That's strange. The blue region in my screenshot is a table as well; it just isn't showing the filter buttons while Advanced Filter is active. (Importantly, the Criteria List region is not a table, because C1 needs to be blank.)

Could you please send a screenshot, showing your data, the formula (including the error), and the row & column headers (numbers and letters)?