0

I am trying to make a list in Excel that has as its output a list of unique items that appear multiple times in different sources of the excel sheet. Ideally, the list should be automated automatically as more data is inputted in the sources, but no additional sources will be added. I used a formula I found here, but it only works for a single source of data (and this data then needs to be adjacent).

I attached a picture of my document with circles enclosing the sources and pointing to where the list should be created. I highlighted in yellow a cell in the top row that does not get outputted (because I don't know how to do this). Picture for reference

I can provide the excel document if need be.

I am thinking of consolidating the sources to a single source, but I would like to solve this in a more sophisticated way that does not involve creating more tables.

Dementio8
  • 1
  • What is your excel version? – Harun24hr Jul 01 '21 at 06:03
  • There doesn't seem to be any link/lookup reference to oil refinery. Can you describe what you're trying to achieve in words + screenshot e.g. lookup from table a -> b based on lookup 'x', then, based upon this result, lookup from table c - > b based upon xyz etc. – JB-007 Jul 04 '21 at 18:14

1 Answers1

0

As per your screenshot it seems you are using tables. Then try below formula-

=IFERROR(INDEX(FILTERXML("<t><s>"&TEXTJOIN("</s><s>",TRUE,Table1[Machine],Table2[Machine])&"</s></t>","//s[not(preceding::*=.)]"),ROW(1:1)),"")

Please note: TEXTJOIN() is available to Excel-2019 & Excel-365 and it has limitation to 50,000 data only.

To learn more about FILTERXML() read this article from JvdV.

enter image description here

Harun24hr
  • 30,391
  • 4
  • 21
  • 36