Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I average data from a repeating list into a unique list? | Excel Worksheet Functions | |||
Data Validation lists - entering value not in list | Excel Discussion (Misc queries) | |||
AutoFilter lists unique values | Excel Discussion (Misc queries) | |||
Pull unique names for drop down list | Excel Discussion (Misc queries) | |||
Formula to list unique values | Excel Worksheet Functions |