How can I get the cells that repeat in lists from multiple sheets?

Example:

Sheet A

Col A
Val1
Val2
Val3

Sheet B

Col A
Val1
Val2
Val4

Sheet C

Col A
Val1
Val7

I want to get in other sheet, all the values from Col A, from each sheet, that have occurrence > 1 (repeated) and it’s count. In this example would be:

image description

How can I achieve this?

Thanks in advance.

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 …

Hi,

Thank you so much, this works.

Best regards