How to merge two lists together, identify doubles, and delete them?

To remove duplicates you may find it efficient to use this Copy Distinct Automator Service (Dropbox download).

To install just double-click the downloaded .workflow package and, if necessary, click "Install Anyway" in System Preferences > Security and Privacy.

To use:

1. Select the cells with values containing duplicates.
2. Choose Copy Distinct from the Numbers > Services menu.
3. Click once in the top destination cell.
4. Command-v to paste the "de-duplicated" values.

This is a lot quicker and easier than setting up complicated formulas and extra columns and tables each time you need to remove duplicates.

SG

Hello underdog13

Is this what you're after?

Regards,

Naikosen

Here's a solution that does require the 'dreaded' extra column and a 'complicated' formula. 😉

Note that the initial data set in Numbers 1 is not sorted. It was created with a random number generator, and not re-ordered as the solution does not require that the initial set be in order.

Starting point:

Numbers 1 is the target table, containing the partial set of numbers.

Numbers 2 is the 'source' table, containing the full set, from which the numbers necessary to fill the set in Numbers 1 will be copied.

The added column B is used to determine which numbers need to be copied, then pasted into Numbers 1.

Enter the 'complicated' formula in cell B2, and fill down to the end of the column.

B2: =COUNTIF(Numbers 1 :: A,A)

With all of column B (except the top cell, which is in a Header row) selected, apply the following Conditional format rule to these cells:

The tables will look like this:

Step 1: Sort Numbers 2 on column B to gather all the 'needed' numbers into a group.

Select the cells in column A that are on rows with zero in column B. Copy.

Click once on the first empty cell in Numbers 1. Paste.

Note that column B of Numbers 2 gives you an immediate confirmation that there is one (and only one) copy of each number in column A of Numbers 1.

Finally, Sort column A of Numbers 1 in ascending order.

You may also want to re-sort Numbers 2 on the values in column A (which I haven't done in the 'final result' below).

Regards,

Barry

Compare and contrast all that to this:

Select the cells and choose 'Copy Distinct' from the Numbers > Services menu:

Click once in a destination cell (I picked A2 in 'Table 1-1 here, but it could be anywhere, including the original table) and command-v to paste:

'Sort Ascending' and you're done:

That's it! All done in less than the time it takes to enter a formula and get it working.

No cleanup after you're finished, and and it's all ready to go for use the next time in another document.

SG

P.S. Meant to address this to Underdog13, not Barry. Barry's solution is the way to go with Numbers 2 as the 'Copy Distinct' service only works with Numbers 3.

Hello

The following simple perl script will get lines of text from the clipboard, extract the distinct lines, sort them in (lexiclally) ascending order and put the result in the clipboard, which you can paste as desired.

#!/bin/bash export LC_ALL=en_GB.UTF-8 # UTF-8 locale is required to let pbpaste and pbcopy handle UTF-8 text correctly perl -CSDA -w <<'EOF' - <(pbpaste) | pbcopy use strict; my %h = (); local $\ = qq(\n); while (<>) { chomp; $h{$_} = 1; } for (sort keys %h) { print $_; } EOF

For the current example, copy the cells in Table 3, run the script and paste (and match style) the result to Table 4.

If you wish, you may create an Automator service to run the script. Service workflow consists of single Run Shell Script action as follows.

Service properties - Service receives [no input] in [any application] - [not checked] Replace selected text Run Shell Script action - Shell = /bin/bash - Pass input = as arguments - Code = as listed above

Resulting service workflow will look like this:

If you wish, you may define keyboard shortcut for this service.

To use it, copy the source lines of text to the clipboard, invoke the service and paste the result to desired destination.

Regards,

H