If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. 


Thread Tools  Display Modes 
#1




How do I create a list of unique values from 2 columns of data
I have 2 columns of data. Column A has 50,000 records and Column B has 4,000
records (many of which are in Column A). I need to generate Column C which should be all of the values from Column A that are not also in Column B. The net yield for Column C should be roughly 46,000 records. How can I go about solving this problem? Many thanks!!!! 
Ads 
#2




How do I create a list of unique values from 2 columns of data
I have to do this sort of thing often, determine which of a column of values
do (or do not) appear in another column. There are multiple ways to do it, but my favorite goes like this: Let's see, you want to a list of values in A that do not appear in B. Ok, in column C opposite one of the values in A, say row 2, put the formula "=MATCH(A2,B,0)". I'm used to working with R1C1 notation so I may have done this wrong, but the MATCH function should look in column B for any exact match on the cell in column A, and return either the row number where a match occurred or some error, #N/A I think. Copy this to each cell in column C opposite the values in A and you can tell at a glance which ones match and which ones don't. But you don't want to "tell at a glance", you want a list of the values that aren't there. So wrap an IF around the MATCH: =IF(ISERROR(MATCH(A2,B,0)),A2,"") (Or is it "ISERR"? I think you want the one that's spelled out, "ISERROR".) If the match returns #N/A then it means the value in A is not in B, so it lists the value in A; if it found a match in B it puts a null string in C. Now you can sort column C so that all the null strings are at one end of the column, and the rest of the values are the ones you want.  "FrozenRope" wrote: > I have 2 columns of data. Column A has 50,000 records and Column B has 4,000 > records (many of which are in Column A). I need to generate Column C which > should be all of the values from Column A that are not also in Column B. 
#3




How do I create a list of unique values from 2 columns of data
Another play which should deliver the goods for you
Assuming data in row2 down in cols A and B Put in C2: =IF(A2="","",IF(ISNUMBER(MATCH(A2,B:B,0)),"",ROW() )) Leave C1 blank Put in D2: =IF(ROWS($1:1)>COUNT(C:C),"",INDEX(A:A,SMALL(C:C,R OWS($1:1)))) Select C22, copy down to the last row of data in col A, ie down to D50001. Col D will return the required results, ie items in col A not found in col B, all neatly bunched at the top.  Max Singapore http://savefile.com/projects/236895 xdemechanik  "FrozenRope" wrote: > I have 2 columns of data. Column A has 50,000 records and Column B has 4,000 > records (many of which are in Column A). > > I need to generate Column C which should be all of the values from Column A > that are not also in Column B. The net yield for Column C should be roughly > 46,000 records. > > How can I go about solving this problem? > > Many thanks!!!! 
Thread Tools  
Display Modes  


Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
create a list of unique values  Bill Brehm  Excel Worksheet Functions  4  February 29th 08 02:50 AM 
List unique Values from different columns: How to...  dakke  Excel Discussion (Misc queries)  6  February 15th 08 12:34 AM 
Compare data in 2 columns for unique values  Steve C[_2_]  Excel Discussion (Misc queries)  2  November 29th 07 05:26 PM 
Count unique values and create list based on these values  vipa2000  Excel Worksheet Functions  7  August 5th 05 01:17 AM 
create list of unique values from a column with repeated values?  Chad Schaben  Excel Worksheet Functions  1  July 8th 05 10:25 PM 