Combine unique values from two columns into one
I want to create a list of all the unique Métro stations that I use to start and end journeys.
I've managed to create a spreadsheet where I can record the start stations and the end stations. I then have a column that compiles just the unique starting stations, and another column that compiles just the unique ending stations. This gives me two separate columns, and within each column every value is unique, but between the two columns there can be duplicates (and there are often are). I want to combine the two columns into one and remove any duplicative values.
I've tried using different methods to do this, but can't figure this out.
This is what my spreadsheet looks like today.
The formula that calculates the column in the Unique Starts table looks like this:
IF(ROW(cell)−1>MAX(UID Starts),"",INDEX(Start,MATCH(ROW(cell)−1,UID Starts,0),column-index,area-index))
Similarly, the formula that calculates the column in the Unique Ends table looks like this:
IF(ROW(cell)−1>MAX(UID Stops),"",INDEX(Stop,MATCH(ROW(cell)−1,UID Stops,0),column-index,area-index))
I tried for a while to make it work with this solution generously provided by forum expert @badunit last year. That approach works fine when values are manually entered in columns A and B. But it gets convoluted when values are automatically populated from other columns. I wrestled with it for some weeks and then gave up, but now a year later find I need to take another crack at a solution.
Here's a link to my Numbers file if anyone cares to take a look:
Thank you to anyone who can help!
[Link Edited by Moderator]
iMac 27″ 5K, macOS 13.4