I have two columns and need to keep the unique values from BOTH columns.
For example:
Column 1
a
b
c
Column 2
b
c
d
Desired results:
a
d
('a' and 'd' are unique to both column 1 and column 2)
How on earth? 🙂
Thanks,
Kurt
you can use the vlookup function to search for each value in one column in the other, then flag all those that are not found:
D2=IFERROR(VLOOKUP(B2, Column 2, 1, 0), "__NOT_FOUND__")
E2=IFERROR(VLOOKUP(C2, Column 1, 1, 0), "__NOT_FOUND__")
F2=IF(D2="__NOT_FOUND__", B2, IF(E2="__NOT_FOUND__", C2, ""))
select D2 thru F2, then fill down
You should name the columns that same as I did if you want to copy and paste the formulas
you can use the vlookup function to search for each value in one column in the other, then flag all those that are not found:
D2=IFERROR(VLOOKUP(B2, Column 2, 1, 0), "__NOT_FOUND__")
E2=IFERROR(VLOOKUP(C2, Column 1, 1, 0), "__NOT_FOUND__")
F2=IF(D2="__NOT_FOUND__", B2, IF(E2="__NOT_FOUND__", C2, ""))
select D2 thru F2, then fill down
You should name the columns that same as I did if you want to copy and paste the formulas
Wayne,
This is awesome! WORKS GREAT!!! And such a quick reply. Wow. (Gurus can be intimidating...)
Thank you very much!
Best regards,
Kurt
P.S. I am not at all good with formulas..., so this really helps me - a lot, actually 🙂 .
Not being much of a spreadsheet user it took me a few tries, but I got it.
I had to put in the formula for D2, then pull it down that column, and then do the same with E2 and its column, and then F2. I tried putting in all the formulas at once... HA! I'm sure most users would not need that much detail in an explanation.
Wayne,
This solution seems to only be working for columns where either value in a row is unique, but not if BOTH are unique.
For example, using the formulas given, this is what I get if there is a row with two values that are unique. In this case row 4 has unique values in Column 1 and Column 2:
Column 1 | Column 2 | Test Col 1 | Test Col 2 | Uniques |
a | b | __NOT_FOUND__ | b | a |
b | c | b | c | |
c | d | c | __NOT_FOUND__ | d |
h | e | __NOT_FOUND__ | __NOT_FOUND__ | h |
Notice in row 4 both 'h' and 'e' are unique, but only 'h' (Column 1, row 4) is listed in the Uniques. 'e' in Column 2 row 4 was not included in the Uniques.
How could I get both the 'h' and the 'e' into the Uniques column?
I'm not sure I see a way.
Thanks again!
Best regards,
Kurt
It also appears the solution will not work if the columns do not have the same number of rows:
Column 1 | Column 2 | Test Col 1 | Test Col 2 | Uniques |
a | b | __NOT_FOUND__ | b | a |
b | c | b | c | |
c | d | c | __NOT_FOUND__ | d |
h | e | __NOT_FOUND__ | __NOT_FOUND__ | h |
f | __NOT_FOUND__ | __NOT_FOUND__ | 0 | |
g | __NOT_FOUND__ | __NOT_FOUND__ | 0 |
'f' and 'g' in Column 2 are unique, but are listed as zeros in the Uniques?
I'm not sure what would work here either 😟 .
Thanks,
Kurt
Hi Kurt,
Here's another approach, using a breakout table to extract a single column containing the values in the two lists that are unique within the full set.
The original lists are in columns A and B of the larger table ( "Data" ).
Column B of Data use two COUNTIF statements to count the number of occurrences in columns A and B of each value in column A. The same formula is used in Column D to count occurrences of each value in column B.
B2 (and filled down) =COUNTIF(A,A2)+COUNTIF(B,A2)
C2 (and filled down) =COUNTIF(B,B2)+COUNTIF(A,B2)
Formulas in columns D and E create an index of the rows containing values whose count is 1. The index in D is for column A values, the one in E for column B values.
D2 (and filled down) =IF(C2=1,MAX(E$1:E1)+1,"")
E2 (and filled down) =IF(C2=1,MAX(E$1:E1)+1,"")
The second table, "Summary," uses the index values as the 'search-for' values in two LOOKUP statements to retrieve first the unique within the list values from column A of Data, then, when that list is exhausted, to retrieve the ones from column B.
B2 (and filled down) =IF(ROW()-1<=MAX(Data :: E),LOOKUP(ROW()-1,Data :: E,Data :: A),IF(ROW()-1-MAX(Data :: E)<=MAX(Data :: F),LOOKUP(ROW()-MAX(Data :: E)-1,Data :: F,Data :: B),""))
Details, and further examples for each function can be found in the iWork Formulas and Functions User Guide, available through the Help menu in Numbers.
Regards,
Barry
Thanks Barry!
This is a great solution. And it works with two columns that have a different number of rows, even when a row has the same value.
This is a correct solution to my problem.
compare two columns and find unique values
