Let's assume I have some data in Excel (and not in a real database). In one sheet, I have data, where one column functions as the ID, and I have made sure that the values in this column are unique. In another sheet, I also have some data, again with one column which can be taken as an ID, and it is also unique. If row N in Sheet 1 has some value, and row M in Sheet 2 has the same value, I am sure that row N and row M describe the same real-world object.
What I am asking: how can I get the equivalent of a full outer join without writing any macros? Formulas and all functions accessible through the ribbon are OK.
A small "play data" example:
Dostoyevski Russia Pushkin Russia Shelley England Flaubert France Hugo France Eichendorff Germany Byron England Zola France
Shelley Percy Bysshe Eichendorff Josef Freiherr Von Flaubert Gustave Byron Lord Keller Gottfried Dostoyevski Fyodor Zola Emile Balzac Honoré de
Desired output (sorting is not important):
Dostoyevski Russia Fyodor Pushkin Russia Shelley England Percy Bysshe Flaubert France Gustave Hugo France Eichendorff Germany Josef Freiherr von Byron England Lord Zola France Emile Keller Gottfried Balzac Honoré de
To everybody who is horrified by this scenario: I know that this is The Wrong Way To Do It. If I have any choice, I would not use Excel for this. However, there are enough situations out there where a pragmatic solution is needed, stat, and a better (from IT point of view) solution cannot be applied.