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.

 How do I create a list of unique values from 2 columns of data
 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

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

 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 User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Excel Newsgroups     Excel Discussion (Misc queries)     Setting up and Configuration of Excel     New Users to Excel     Excel Worksheet Functions     Links and Linking in Excel     Charts and Charting in Excel     Excel Programming About ExcelBanter     About this forum

 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 06:21 AM.