I have two columns of stock symbols - A and B. I want to create 3 new
columns. Column C will tell me which symbols are in both A & B.
Column D will tell me which symbols are in A but not in B. Column E
will tell me which symbols are in B but not in A.
Thanks
Lets assume that Column A has 26 values and Column B has 13 values.
The following formulas should work for any ranges, simply substitute
the actual for the assumed wherever you see 26 or 13 below.
I am assuming that the Column A values begin in A1 and go to A26 and
that the Column B values being in B1 and go to B13.
In C1 enter: =IF(COUNT(MATCH(B1,$A$1:$A$26,0)),B1,"")
Copy this formula into the cells C1:C13 and Column C will now display
the values found in both Column A and Column B.
In D1 enter: =IF(COUNT(MATCH(A1,$C$1:$C$13,0)),"",A1)
Copy D1 and paste this formula into cells D1:D26 and into cells E1:E13.
Column D will now display symbols in A that are not in B, and Column E
will now display symbols in B that are not in A.
To remove any blank spaces between the values in Columns C, D, and E,
you will first want to remove the formulas. To do this, select the
entire spreadsheet (you can click the blank square left of the A
column), copy, then go to edit and 'paste special'. Select values and
hit OK. You can now use the Sort function in the data menu for any
column you want to remove blank spaces in.
I hope this answers your question. I found the trick of how to do
this tucked away at the following link:
http://www.emailoffice.com/excel/arrays-bobumlas.html
Search strategy: determine whether a value is in a list excel
Yours truly,
anhebetude
 How much does getting a small tattoo on your hip/stomach hurt?
 Do anyone else have an itchy anus? ?
|
Microsoft Excel - comparing columns