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

Posted on Jul 5, 2023 9:17 AM

Reply
2 replies

Jul 6, 2023 5:52 PM in response to Khoi Vinh

If you goal is just to have a list of unique station names then you can do something like this:



Then with the table still selected in the menu go to Organize > Create Pivot Table and in the Pivot Options Pane simply drag field in the Rows box as follows:





You can copy-paste the resulting values wherever you want.



It took me just a few seconds to do this.


SG

Jul 6, 2023 10:48 AM in response to Khoi Vinh

Howdy Khoi Vinh,


It sounds like you have questions about calculating values in Numbers. We know how important this is for you, and we will help get you to the right place for answers.


The first thing to try is the steps listed in the Numbers User Guide. This info can often answer your questions:

Calculate values using data in table cells in Numbers on Mac - Apple Support


If you need continued assistance, please get in touch with the Apple support team via this link: Get Support


Take Care!

This thread has been closed by the system or the community team. You may vote for any posts you find helpful, or search the Community for additional answers.

Combine unique values from two columns into one

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.