A Microsoft Excel forum. ExcelBanter

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.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

How do I create a list of unique values from 2 columns of data



 
 
Thread Tools Display Modes
  #1  
Old June 7th 08, 07:10 AM posted to microsoft.public.excel.worksheet.functions
FrozenRope
external usenet poster
 
Posts: 6
Default 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  
Old June 7th 08, 08:11 AM posted to microsoft.public.excel.worksheet.functions
Bob Bridges
external usenet poster
 
Posts: 108
Default 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  
Old June 7th 08, 11:57 AM posted to microsoft.public.excel.worksheet.functions
Max
external usenet poster
 
Posts: 9,221
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
create a list of unique values Bill Brehm Excel Worksheet Functions 4 February 29th 08 01:50 AM
List unique Values from different columns: How to... dakke Excel Discussion (Misc queries) 6 February 14th 08 11:34 PM
Compare data in 2 columns for unique values Steve C[_2_] Excel Discussion (Misc queries) 2 November 29th 07 04: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


All times are GMT +1. The time now is 05:20 AM.


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