Google-sheets – Google Sheets: How to compare multiple rows/data ranges AND display the MATCHING/duplicate values

google sheets

As the title reads, I am using google sheets and have MULTIPLE data (columns) ranges of varying sizes (different row counts). The goal is to combine the data into a single dataset AND only show items that are "duplicated"

This is my data (sample), from EVE Echoes:

------Dataset 1-------
-----A-----|-----B----
Asteroid       Yield
-----------|----------
Jaspet-----|-1,033.00
Gneiss-----|---455.00
Arkonor----|---300.00
Kernite----|---216.00
Spodumain--|---178.00
Pyroxeres--|---113.00
Plagiocase-|----43.65


------Dataset 2-------
-----X-----|-----Y----
Asteroid       Yield
-----------|----------
Hedbergite-|---193.00
Gneiss-----|---91.08
Hemorphite-|---79.20
Spodumain--|---29.70
Dark Ochre-|---25.20
Omber------|---24.75
Kernite----|---21.60


------Dataset 3-------
-----O-----|-----P----
Asteroid       Yield
-----------|----------
Crokite----|---39.48
Jaspet-----|---20.16
Dark Ochre-|---19.35
Pyroxeres--|---13.50
Hemorphite-|----6.43
Hedbergite-|----3.70

In the data (sample) above, I am only providing 3 different datasets, but keep in mind that there is no "same row-count" EVER. Nor there's a "same dataset count"; could be 3 data sets (like in this sample), only one, or hundred's of data sets.

Comparing datasets, the desired outcome should be these values with duplicates:

------------------------
|-Data with duplicates-|
------------------------
Dark Ochre
Gneiss
Hedbergite
Hemorphite
Jaspet
Kernite
Pyroxeres
Spodumain

Already tried a number of approaches, all failed. Tried using FILTER():

=FILTER({A:A; X:X; O:O}, COUNTIFS({A:A; X:X; O:O}, UNIQUE({A:A; X:X; O:O})>1))

Query didn't help either:

=QUERY({A:A; X:X; O:O}, "SELECT Col1 WHERE Col1 IS NOT NULL AND COUNT(Col1)>1 GROUP BY Col1")

Anything I have tried results in errors. There's also the problem of headers and empty cells

https://docs.google.com/spreadsheets/d/1rEIifKu_d6i6rikgFcEm15EhFNjLVFNQxWeKJjsZ0-o/edit#gid=547852505

Best Answer

See sheet "Duplicates" in File. Cell A1 contains following formula:

=QUERY(
 QUERY({Data!$A$2:$A;Data!$D$2:$D;Data!$G$2:$G},
 "Select Col1, COUNT(Col1)
 Group by Col1
 Label COUNT(Col1) ''"),
"Select Col1
Where Col2 > 1")

where {Data!$A$2:$A;Data!$D$2:$D;Data!$G$2:$G} are ranges with your initial data.