Merging to Lists into one Unique List

[Guest]

Hello,

I have two lists of numbers in different columns, and require Excel to
generate one column of unique numbers from both lists.

Example:
Column A Column B Column C
1 1
2 3
3 4
5 5

I would require Column C to generate a listing of unique numbers from both
Column A and Column B. In this example, Column C would generate the
list of (1,2,3,4,5).

THANKS!
Rob

 

[Biff]

Hi!

Try this:

Enter this formula in C1:

=MIN(A1:B4)

Enter this formula in C2 using the key combination of CTRL,SHIFT,ENTER (not
just ENTER):

=IF(MIN(IF(A$1:B$4>C1,A$1:B$4))<C1,"",MIN(IF(A$1:B$4>C1,A$1:B$4)))

Copy down until you get blanks.

Biff

 

[Biff]

For added robustness change: (just in case *every* number is a 0)

=IF(MIN(IF(A$1:B$4>C1,A$1:B$4))<C1,"",MIN(IF(A$1:B$4>C1,A$1:B$4)))

To:

=IF(MIN(IF(A$1:B$4>C1,A$1:B$4))<=C1,"",MIN(IF(A$1:B$4>C1,A$1:B$4)))

Biff

 

[Guest]

Thanks Biff,

How would I get it to work if my real data was in Column B and Column F,
instead
of A & B as per my initial example. I would like the unique list to be
displayed in Column I.

Also, there is no set length to the amount of numbers that are in both
Columns B and Column F.

Thanks!
Rob

 

[Biff]

Ok, that changes things!!!!

Let's assume column B is longer than column F. The ranges are B1:B10 and
F1:F5.

Enter this formula in I1:

=SMALL((B1:B10,F1:F5),1)

Enter this formula in I2: (normally entered)

=SMALL((B$1:B$10,F$1:F$5),1+SUMPRODUCT(COUNTIF(B$1:B$10,I$1:I1)+COUNTIF(F$1:F$5,I$1:I1)))

Copy down until you get #NUM! errors meaning all the uniques have been
extracted.

Biff