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 …