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 13:23, akbob wrote:
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

Lets say that you have the email addresses in B from B2 on. If you have a label/column heading in B1, format it first as bold and italic. If you don't have one, enter e.g., Blist in B1 and format B1 as bold and italic.

Activate B2.
Activate Data|Filter|Advanced Filter.
Check 'Copy to another location'.
Enter as value of 'List range'.

$B$2:$B$8 [ adjust to suit ]

Enter as value of 'Copy to', e.g.,

$D$2

Check 'Unique records only'.

Click OK.

Copy everything from D2 on and paste it after your last email-address in A.

Delete column B.

Column D is now your second list of unique email adresses.

Aladin

 

[akbob]

Thanks for the quick reply Aladin! I'll give this a shot, it looks simple enough.

Bob

 

[Al Chara]

If you want to use a macro (but the situation doesn't need one) try the code below (I borrowed some from Chip Pearson's page):

Dim r As Long
Dim V As Variant
Dim Rng As Range
Application.ScreenUpdating = False
Set Rng = Intersect(ActiveSheet.UsedRange.Rows, Columns("b"))

For r = Rng.Rows.Count To 1 Step -1
V = Rng.Cells(r, 1).Value
If Application.WorksheetFunction.CountIf(Rng.Columns(1), V) > 1 Then
Rng.Cells(r, 1).Delete
End If
Next r
Range(Range("B1"), Range("B1").End(xlDown)).Copy
Range("A1").End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste
Set Rng = Intersect(ActiveSheet.UsedRange.Rows, Columns("a"))

For r = Rng.Rows.Count To 1 Step -1
V = Rng.Cells(r, 1).Value
If Application.WorksheetFunction.CountIf(Rng.Columns(1), V) > 1 Then
Rng.Cells(r, 1).Delete
End If
Next r
Application.ScreenUpdating = True

The code goes through column B and deletes duplicate entries then it appends the address at the bottom of column A and then deletes the duplicates of column A.

 

[akbob]

Well, ok, not as simple as I thought Geeze, I feel like an idiot. I know this should be simple

I have two columns set up. Current (A) and New (B). I select B2, Advanced Filter. It defaults to the list range including both columns. I select C2 for the copy to range, and click unique only. When I click on OK, it just copies both columns as is in C and D, without filtering out duplicates. What am I doing wrong?

Thanks for helping this dummy out here.

Bob

 

[Aladin Akyurek]

On 2002-03-19 14:35, akbob wrote:
Well, ok, not as simple as I thought /board/images/smiles/icon_frown.gif Geeze, I feel like an idiot. I know this should be simple

I have two columns set up. Current (A) and New (B). I select B2, Advanced Filter. It defaults to the list range including both columns. I select C2 for the copy to range, and click unique only. When I click on OK, it just copies both columns as is in C and D, without filtering out duplicates. What am I doing wrong?

Thanks for helping this dummy out here.

Bob

Just change manually the default range to the desired B-range:

if it says e.g.,

$A$1:$B$8

change A to B, you'll be OK.

 

[Yogi Anand]

On 2002-03-19 14:35, akbob wrote:
Well, ok, not as simple as I thought Geeze, I feel like an idiot. I know this should be simple

I have two columns set up. Current (A) and New (B). I select B2, Advanced Filter. It defaults to the list range including both columns. I select C2 for the copy to range, and click unique only. When I click on OK, it just copies both columns as is in C and D, without filtering out duplicates. What am I doing wrong?

Thanks for helping this dummy out here.

Bob

Hi akbob:
Here are the steps you need to take:
1. Copy data from column A to the end of the data in column B ... this will give you the entire data set from column A and B together -- duplicates and all
2. Now insert a blank column to the left of column B, so now your entire dataset is in column C
3. Click somewhere within the dataset in column C (make sure that your dataset is selected to be filtered) and click on Data|Filter|Advanced Filter ... and check Unique records only
4. Now you have your data filtered in place as you requested.
5. You may now want to copy the filtered data to another column, say column E
6. Now you have a hard copy of your filtered data ... unique records from originally column A and B combined.
now you may copy the data else where as you need
HTH

 

[akbob]

Hi All,

Thanks for all your help.

I really appreciate the Macro Solution Al Chara posted, because this will be something that will need to be done frequently. It would also allow me to have the most current list, and the most curent additions to the list.

The problem I have with the Macro as is however, is that it's deleting the wrong records from column A. No deletions should be made from Column A. For each entry in column B, they should be added to column A if unique, or Deleted from Column B if already present in Column A.

I've tried messing with the macro code to see if I could fix it, but I'm operating in the dark here. Can anyone see/fix the problem with the Macro?

Thanks again everyone.

Bob

 

[Aladin Akyurek]

On 2002-03-19 16:18, akbob wrote:
Hi All,

Thanks for all your help.

I really appreciate the Macro Solution Al Chara posted, because this will be something that will need to be done frequently. It would also allow me to have the most current list, and the most curent additions to the list.

The problem I have with the Macro as is however, is that it's deleting the wrong records from column A. No deletions should be made from Column A. For each entry in column B, they should be added to column A if unique, or Deleted from Column B if already present in Column A.

I've tried messing with the macro code to see if I could fix it, but I'm operating in the dark here. Can anyone see/fix the problem with the Macro?

Thanks again everyone.

Bob

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

 

[akbob]

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