Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
Rob Rob is offline
external usenet poster
 
Posts: 718
Default Merging to Lists into one Unique List

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


  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,688
Default Merging to Lists into one Unique List

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$4C1,A$1:B$4))<C1,"",MIN(IF(A$1:B $4C1,A$1:B$4)))

Copy down until you get blanks.

Biff

"Rob" wrote in message
...
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




  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,688
Default Merging to Lists into one Unique List

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

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


To:

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

Biff

"Biff" wrote in message
...
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$4C1,A$1:B$4))<C1,"",MIN(IF(A$1:B $4C1,A$1:B$4)))

Copy down until you get blanks.

Biff

"Rob" wrote in message
...
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






  #4   Report Post  
Posted to microsoft.public.excel.misc
Rob Rob is offline
external usenet poster
 
Posts: 718
Default Merging to Lists into one Unique List

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" wrote:

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$4C1,A$1:B$4))<C1,"",MIN(IF(A$1:B $4C1,A$1:B$4)))

Copy down until you get blanks.

Biff

"Rob" wrote in message
...
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





  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,688
Default Merging to Lists into one Unique List

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

"Rob" wrote in message
...
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" wrote:

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$4C1,A$1:B$4))<C1,"",MIN(IF(A$1:B $4C1,A$1:B$4)))

Copy down until you get blanks.

Biff

"Rob" wrote in message
...
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







Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How can I average data from a repeating list into a unique list? Duke Carey Excel Worksheet Functions 0 March 3rd 06 07:38 PM
Data Validation lists - entering value not in list rgarber50 Excel Discussion (Misc queries) 3 July 19th 05 08:35 PM
AutoFilter lists unique values R.J.H. Excel Discussion (Misc queries) 3 April 19th 05 08:53 PM
Pull unique names for drop down list [email protected] Excel Discussion (Misc queries) 3 February 1st 05 11:23 PM
Formula to list unique values JaneC Excel Worksheet Functions 4 December 10th 04 01:25 AM


All times are GMT +1. The time now is 11:10 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"