Cascading table columns from multiple range sources

I'm trying to populate a table with data from 2 separate ranges that contain the same relative data, but for processing reasons must be in separate columns in a cascading fashion. Basically, one range populates the first column of the table, and the other populates the second. If there is a value in the first column, then the second column must be blank for that row, and if the second column has a value, the first column must also be blank. It is worth mentioning that the table has a number of blank rows at the end that are filtered out to give the table some dynamic functionality. Also, the values may contain duplicates within the first range, but will never have duplicates in the second, although there may be some values that match the first range. Finally, the table must retain the ability to be sorted and filtered.

See the attached example for desired output.

Thanks!

I kind of feel like an INDIRECT is needed to "reset" the cell reference, but can't figure out how to increment the reference properly.

This is what I've tried:

Please Login or Register  to view this content.

Glenn, thanks for the response. Your solution meets most of the conditions, however the table will no longer sort. Column 1 and 2 are static, and will not sort at all, while if I sort by column 3, the rows become misaligned. I added another column to reflect the edge case where some of the rows will be of the same type, so when sorted by that column, column 1 & 2 will look mixed up, so to speak.

Thanks again for your help!

Glenn, that doesn't seem to work either.

Out of curiosity, why is the empty string add to the end of those formulas?

Perfect! Thanks so much for your help Glenn!