Comparing values in two columns to find uniques

[akbob]

Newbie to the board, hope someone can help.

Here is what I want to do. I have a list of email addresses in column A. I want to update the list in column A with unique addresses from column B, and have the duplicates deleted from column B. I should end up with a complete uniques only list in column A, and a list of the uniques that were just added in Column B.

Anybody have a quick an easy solution for me?

Bob

 

[Aladin Akyurek]

On 2002-03-19 16:37, akbob wrote:

Did you try recording a macro, while applying the procedure I described?

Hi Aladin -

I've haven't been able to get your solution to copy only the unique records from Column B. It just copies everything in column B, even if it is present in Column A. I was planning on recording it as a macro if I could make it work right though.

Bob

I just made a file (WB) for you, even recorded a (partial) macro that does part of the job. Just drop me a line if you want a copy.

aladin_akyurek@yahoo.com

 

[akbob]

On 2002-03-19 16:49, Aladin Akyurek wrote:

I just made a file (WB) for you, even recorded a (partial) macro that does part of the job. Just drop me a line if you want a copy.

aladin_akyurek@yahoo.com

Email on the way. Thanks Aladin.

Bob

 

[Aladin Akyurek]

On 2002-03-19 17:03, akbob wrote:

On 2002-03-19 16:49, Aladin Akyurek wrote:

I just made a file (WB) for you, even recorded a (partial) macro that does part of the job. Just drop me a line if you want a copy.

aladin_akyurek@yahoo.com

Email on the way. Thanks Aladin.

Bob

Bob,

Yahoo seems to be down, so use the address in my profile.

Aladin

 

[nisht]

You can do this by using my free utility on my website. File nos is 5

http://www.pexcel.com/download.htm

this utility will compare your range and mark duplicates. you are given two kind of options.

Secondly if you want to extract unique values from A and B .. you can use my second utlitlty "Extract Uniques Second Way"

file nos is 22.

Still ahead if suppose you can copy your data of Column B in sheet2 and you have data in Column A of sheet1 the you can take example i have just uploaded for a friend on

http://www.pexcel.com/guestdownload.htm


Name of the file 'Play1' against the name of 'frank'

this will solve your problem

ni****h desai

 

[akbob]

I must be losing my mind. I'm really NOT this stupid folks. Honest.

Every time I plug in any email address lists into the suggested solutions, none of them produce the desired results. I'm beginning to wonder if it is something with the way Excel reads email addresses, or if I have a bug in my application? (Excel 2000 9.0.3821 SR-1)

For such a simple operation, this has become a major PITA.

Bob

 

[Legacy 98055]

It's not pretty, I'm a novice, but it works.


Sub EmailListingThingy()
Dim AcolCntr As Long
Dim BcolCntr As Long
Dim A_LastRow As Long
Dim B_LastRow As Long
Dim FoundDuplicate As Boolean
Dim Temp_C_RowCntr As Long
Dim Append_A_Cntr As Long

'YourSheetNameHere.Activate

Columns("A:A").Sort Key1:=ActiveSheet.Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Columns("B:B").Sort Key1:=ActiveSheet.Range("B1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

A_LastRow = 1
Do Until Range("A" & A_LastRow).Value = ""
A_LastRow = A_LastRow + 1
Loop
A_LastRow = A_LastRow - 1
B_LastRow = 1
Do Until Range("B" & B_LastRow).Value = ""
B_LastRow = B_LastRow + 1
Loop
B_LastRow = B_LastRow - 1

Temp_C_RowCntr = 1
Append_A_Cntr = A_LastRow + 1

For BcolCntr = 1 To B_LastRow
FoundDuplicate = False
For AcolCntr = 1 To A_LastRow
If Trim(Range("A" & AcolCntr).Value) = _
Trim(Range("B" & BcolCntr).Value) Then
FoundDuplicate = True
Exit For
End If
Next
If FoundDuplicate = False Then
Range("A" & Append_A_Cntr).Value = _
Trim(Range("B" & BcolCntr).Value)
Range("C" & Temp_C_RowCntr).Value = _
Trim(Range("B" & BcolCntr).Value)
Temp_C_RowCntr = Temp_C_RowCntr + 1
Append_A_Cntr = Append_A_Cntr + 1
End If
Next


Columns("B:B").Delete Shift:=xlToLeft

Columns("A:A").Sort Key1:=ActiveSheet.Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Columns("B:B").Sort Key1:=ActiveSheet.Range("B1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom


End Sub
This message was edited by TsTom on 2002-03-20 02:53

 

[Dave Hawley]

Hi Akbob

Try this

Sub DoIT()
Columns(2).EntireColumn.Insert

Range("C1", Range("C65536").End(xlUp)) _
.AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Range("B1"), Unique:=True

Range("B2", Range("B65536").End(xlUp)).Copy _
Destination:=Range("A65536").End(xlUp).Offset(1, 0)

Columns(3).Clear
End Sub