Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 17 of 17

Thread: Comparing values in two columns to find uniques

  1. #11
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,657
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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

  2. #12
    New Member
    Join Date
    Mar 2002
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #13
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,657
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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

  4. #14
    Board Regular
    Join Date
    Feb 2002
    Location
    Ahmedabad Gujarat
    Posts
    303
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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



  5. #15
    New Member
    Join Date
    Mar 2002
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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


  6. #16
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  7. #17
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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


Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •