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: How can I achieve this?

edit retag close merge delete

Sort by » oldest newest most voted

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,