Extracting Unique data from two columns

[gbutler288]

I'm trying to find a way to extract unique data from two columns within an excel spreadsheet. There are nearly 70,000 records in each column. 2007 as you know has moved beyond the 2x16 rule. I'm trying to compare each record in both columns and if the data is in column a but not in column b I want to list it in column c. Any ideas on how to do this. I've tried a couple of MS solutions and they are able to identify the uniqe records but not place them into a separate column. I don't want to go record by record copying and pasting.

Thanks for you help and ideas!

Gary

[arthurbr]

Welcome to the board

I think this link will help you out :http://www.cpearson.com/excel/ListFunctions.aspx

[gbutler288]

Unfortunately I don't think I found the answer there. I have two lists that are different in number of rows. Someone told me I should do a vlookup between the two columns.

1 1 false
2 3 false
4 2 true
5 6 true
7 7 false

This would show me which items appear in list two and which ones don't that way I can sort the true's and have a unique record list. Any ideas?

[arthurbr]

Could you post a small sample of your data?

[royUK]

Try this, I'm not sure how long it will take with 70,000 rows to check.

Option Explicit

Sub getUniques()
    Dim rColA  As Range
    Dim rColB  As Range
    Dim cl     As Range
    Dim str    As String

    Set rColA = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
    Set rColB = Range(Cells(1, 2), Cells(Rows.Count, 2).End(xlUp))
    For Each cl In rColA
        str = cl.Value
        If Application.WorksheetFunction.CountIf(rColB, str) = 0 Then Cells(Rows.Count, 3).End(xlUp).Offset(1, 0) = str
    Next cl
End Sub

[gbutler288]

sorry I can't post the data due to confidentiality. But here is an example

1 3
2 4
5 5
6 9
19 10
20 20
24 68
40 69
70 70
71
72
76
89
91

There is over 70,000 rows of data. Both columns are sorted. So I need to know the following.

If a number in column B matches a number in column a give me a result if not give me another result. Then I can sort on the results and have all the unique id's from column B.

This is so simple in concept but I just can't get this sucker to work.

Thanks for your help!

[vane0326]

Hi gbutler288,

I tested this formula for 60,000 rows in Column A & column B and the calculation seems to be very efficient.


Input formula in C2 and just hit enter then copy down.

=LOOKUP(10^307,CHOOSE({1,2},IF(MAX($A$2:$A$60000)=MAX(C$1:C1),"",SMALL($A$2:$A$60000,COUNTIF($A$2:$A$60000,"<="&C1)+1)),IF(MAX($B$2:$B$60000)=MAX(C$1:C1),"",SMALL($B$2:$B$60000,COUNTIF($B$2:$B$60000,"<="&C1)+1))))

Hope it helps!