hello @kdev:

a try for a ‘KISS’ approach, simpler / smarter solutions welcome, it leaves some manual work - re-triggering after change in source data, i’d be curious whether somebody has a dynamic solution based on formulas? if not: enhancement request - ‘dynamic filters’? :

assuming you know your sheet with the most rows - let’s say there are 10 (if not look around how to circumvent various sizes):

try the following formula in sheet4 A1:A10 `=IF($'Sheet A'.A1<>"";$'Sheet A'.A1;"")`

, and adopted for ‘Sheet B’ in A11:A20, and for ‘Sheet C’ in A21:A30,

and the following formula in sheet4 B1:B10 `=IF($'Sheet A'.A1<>"";SUMPRODUCT($'Sheet A'.A1=$'Sheet A'.A$1:$'Sheet A'.A$10)+SUMPRODUCT($'Sheet A'.A1=$'Sheet B'.A$1:$'Sheet B'.A$10)+SUMPRODUCT($'Sheet A'.A1=$'Sheet C'.A$1:$'Sheet C'.A$10);0)`

, i let the adoption to ‘Sheet B’ and ‘Sheet C’ for cells B11:B20 and B21:B30 to you,

now you have a list with all values in col ‘A’ and their occurence in col ‘B’ … but with duplicates … to filter them out apply [data - filter - standard filter] with condition ‘col ‘B’ > 1’, options ‘no duplicates’, and ‘copy output to’ say Sheet5.A1,

mission accomplished, sheet5 has your wishlist,

shortcomings:

- variable size of ranges with data in the source, somebody will come up with a solution,
- not ‘dynamic’, after change in data you have to re-trigger the filtering, see above request for enhancement

i’d intentionally not provide a readymade sheet, i’d like to let you some fun, some learning success and gaining self confirmation,

P.S. ‘solved marks’ and ‘likes’ welcome,

click the grey circled hook - ✓ - top left to the answer to turn it green if the problem is solved,

click the “^” above it if you ‘like’ the answer,

“v” if you don’t,

do not! use ‘answer’ to add info to your question, either edit the question or add a comment,

‘answer’ only if you found a solution yourself …