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
