+ Reply to Thread
Results 1 to 4 of 4

Compare and identify unique column values from different workbooks

  1. #1
    Registered User
    Join Date
    01-22-2015
    Location
    Los Angeles, CA
    MS-Off Ver
    2010
    Posts
    14

    Compare and identify unique column values from different workbooks

    So let's say I have IPHost Column that lists different IP addresses

    A1 IPHost
    A2 10.20.3.238
    A3 172.16.16.1
    A4 192.15.6.5
    A5 12.10.10.1

    and so on...

    Then I have another workbook that also lists different IP addresses but I wanna find out all the unique values in the second workbook and add it to the spreadsheet above. What's the best way to do it?

    So let's say the second workbook contains:

    A1 IPHost
    A2 10.20.3.238
    A3 172.16.16.1
    A4 142.6.3.22 (unique)
    A5 192.15.6.5
    A6 184.16.3.19 (unique)

    How will I put 142.6.3.22 and 184.16.3.19 into the first workbook using Macros? And the reason why I'm asking is we have over 2,000 IP addresses on each workbook and I'm trying to find the easiest way to do it. Thank you in advance for all your help.

  2. #2
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365 ProPlus
    Posts
    9,544

    Re: Compare and identify unique column values from different workbooks

    Please find the two workbooks IP1.xlsm and IP2.xlsx. Place these two workbooks in the same folder and open IP1.xlsm and run the code present on Module 1 to see if this works as per your requirement.
    Attached Files Attached Files
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  3. #3
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2003, 2010, 2016 (Windows)
    Posts
    4,809

    Re: Compare and identify unique column values from different workbooks

    I would copy the values from the second workbook to the bottom of the first, and use

    Data / Remove Duplicates

    using just that one column. Here's a macro based on column A, with the workbook for the unique list holding the code, and the other workbook active:

    Please Login or Register  to view this content.
    Bernie Deitrick
    Excel MVP 2000-2010

  4. #4
    Registered User
    Join Date
    01-22-2015
    Location
    Los Angeles, CA
    MS-Off Ver
    2010
    Posts
    14

    Re: Compare and identify unique column values from different workbooks

    Hi sktneer,

    Thank you for all your help. However, I forgot to mention that the source (IP1.xlsm) has multiple spreadsheets (like 5). How can the code be fixed so that it'll compare values with ALL IP addresses on the workbook IP1 with five different spreadsheets and paste all new ones just to the first spreadsheet on IP1.xlsm. I tried looking it up ways to do it but I always get error when running the code. Here is what you gave me.

    Sub UniqueIPAddresses()
    Dim Swb As Workbook, Dwb As Workbook
    Dim Sws As Worksheet, Dws As Worksheet
    Dim Slr As Long, Dlr As Long, cnt As Long
    Dim Srng As Range, Drng As Range, Dcell As Range
    Dim FPath As String
    Set Swb = ThisWorkbook
    Set Sws = Swb.Sheets("Sheet1")
    FPath = Swb.Path & "\"
    Set Dwb = Workbooks.Open(FPath & "IP2.xlsx")
    Set Dws = Dwb.Sheets("Sheet1")
    Slr = Sws.Cells(Rows.Count, 1).End(xlUp).Row
    Dlr = Dws.Cells(Rows.Count, 1).End(xlUp).Row
    Set Srng = Sws.Range("A2:A" & Slr)
    Set Drng = Dws.Range("A2:A" & Dlr)
    For Each Dcell In Drng
    If WorksheetFunction.CountIf(Srng, Dcell) = 0 Then
    Sws.Range("A" & Rows.Count).End(3)(2) = Dcell
    cnt = cnt + 1
    End If
    Next Dcell
    MsgBox cnt & " IP addresses from " & Dwb.Name & " has been copied to " & Swb.Name & " successfully.", vbInformation, "IP Addresses Copied!"
    Dwb.Close
    End Sub

    So for example:

    IP1 Worbook has 3 spreadsheets
    First spreadsheet Second spreadsheet Third spreadsheet
    172.1.1.1 172.3.3.3 172.5.5.5
    172.2.2.2 172.4.4.4 172.6.6.6

    And IP2 has only 1.
    172.1.1.1
    172.2.2.2
    172.6.6.6
    172.7.7.7

    As you can see 172.7.7.7 is not in any spreadsheet in the first workbook. How can I copy just that (172.7.7.7) to the first spreadsheet on first workbook? I don't want 172.6.6.6 because it already exists somewhere else in IP1.xlsm.

    Thank you.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Compare and identify unique column values from different workbooks
    By la_chua29 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-28-2015, 12:40 PM
  2. Compare and identify unique values from different workbooks
    By la_chua29 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-28-2015, 01:44 AM
  3. Replies: 8
    Last Post: 09-25-2014, 10:14 AM
  4. Replies: 3
    Last Post: 07-24-2013, 01:23 PM
  5. Compare 2 columns of data and get unique values in the 3rd column
    By Eduard in forum Excel Programming / VBA / Macros
    Replies: 30
    Last Post: 04-04-2013, 08:32 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1