Compare and remove duplicates in Arrays (Google sheets)

Question

I have two arrays of many rows , in my Google sheets, which have numbers such as

Active	Recovered
904;1505;1706	406;504;1502;1503
202;401;602;1104;1501	202;1502;1602;1704

I have shown only two rows to explain my problem. In the second row, you see that 202 is common. These rows are populated by complex calculations over which I have no control ¹. Logically, if the element is common (202 here), it should appear only under Active and be removed from Recovered.

How do I compare and remove the common element? Or at least alert me that a common element exists so that I can manually copy-paste that row and delete it?

¹ Active column is populated a formula like =Join(";",Transpose(ARRAY_CONSTRAIN(SORT(UNIQUE(FILTER('2022'!D3:D319,TO_TEXT('2022'!F3:F319)="Active",'2022'!D3:D319<>"")),1,true),15,1))); similarly Recovered and I have no way of accessing the source. My job is to report the output and only today noticed the duplication

Answer 1

Put this formula in row 2 of a free column:

=arrayformula( iferror( substitute( trim( regexreplace( substitute(B2:B, ";", " "), "\b" & substitute(A2:A, ";", "\b|\b") & "\b", " " ) ), " ", ";" ) ) )

The formula copies the contents of B2:B with the values in corresponding rows of A2:A removed separately in each row.

To learn the exact regular expression syntax used by Google Sheets, see RE2.

If you just want to flag rows with duplicates separately in each row of A2:B, you can get a row-by-row count like this:

=arrayformula( iferror( 1 / sum( iferror( match( split(A2, ";"), split(B2, ";"), 0 ) ) ) ^ -1 ) )

The formula will give a blank on rows where there are no duplicates, and the number of duplicates on rows where there are duplicates.