+ Reply to Thread
Results 1 to 12 of 12

Comparing Columns to Match Duplicates ?

  1. #1
    Registered User
    Join Date
    07-22-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    12

    Comparing Columns to Match Duplicates ?

    Hello Everyone,

    I use MS Excel but would consider myself a novice. I'm working on something where I have two CSV exports from different databases. The output is a list of server hostnames and several other attribute columns. One list has about 10,000 rows and the other is roughly 5000 rows. What I need to do is compare the hostnames from the two CSVs and keep the duplicates and discard any hostname where there isn't a match. I also need to keep the associated attribute columns in place. In addition to this, the hostnames on one of the exports will most likely be fully qualified, whereas it would be a short on the other.

    I'm pretty sure this is possible, I'm just curious about how complex it is ... Does anyone have any ideas? Any advice would be appreciated. Thank you.

    If this should be in a different forum, let me know and I'll move it. Thx!

  2. #2
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,873

    Re: Comparing Columns to Match Duplicates ?

    Without seeing any of those files, It is very difficult to give a direct solution..

    However, based on what I can imagine, You will have to import both files > Use Some Text Function to Extract the "Short" Text so that both Data Sources match > Use the Match Function to find Match > Get Rid Of those That Do Not Match..
    Cheers!
    Deep Dave

  3. #3
    Registered User
    Join Date
    07-22-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Comparing Columns to Match Duplicates ?

    Thank you for the response. I'll need to obfuscate some of the names and fields, but I can probably post an example for you to look at. Thank you

  4. #4
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,873

    Re: Comparing Columns to Match Duplicates ?

    It would be great if you could..

  5. #5
    Registered User
    Join Date
    07-22-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Comparing Columns to Match Duplicates ?

    Hello, Thanks for looking at this for me. Here is an example of what I'm trying to do... I have four columns. The second column is associated with attribute1 and attribute2. The first column is coming from an external source. I need to compare column 1 and column 2 and keep only the hosts that match. The comparison should not be case sensitive since there is a chance this could differ between the two host columns. In the actual sheet, column A is about 5000 rows and columns B is like 10,0000. I need to make sure the attributes are applied properly. Is this possible? How complex?

    HOST1 Host2 Attribute1 Attribute2
    scvdLPXT02 12lpar04 443 Production
    scvdLPXT01 gpar0230 443 Production
    AV-VMCTX665 gpar1789 998 QA/DR
    AV-VMCTX666 ugpar0604 998 QA
    AV-VMCTX667 ugpar0303 443 Development
    AV-VMCTX668 axscgpar1789 443 Production
    PO-099AODJ SCVDLPXT02 998 Test
    PD-YY829872 SCVDLPXT01 998 Test
    OO-AP998283 av-VMctx666 998 Production
    BB-AO99383
    BB-AO99283
    UP-GAD0101

    If I were to process the above spreadsheet, I would get the following output:

    scvdLPXT02 SCVDLPXT02 998 Test
    scvdLPXT01 SCVDLPXT01 998 Test
    AV-VMCTX666 av-VMctx666 998 Production

    I'm not sure if I can attach an actual xlsx to this thread? Thanks in advance.
    Last edited by zooba72; 07-07-2015 at 09:48 PM. Reason: I made a mistake

  6. #6
    Registered User
    Join Date
    07-22-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Comparing Columns to Match Duplicates ?

    Example.csv

    Attached the .csv file with the example. It might be easier to look at.

  7. #7
    Registered User
    Join Date
    07-22-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Comparing Columns to Match Duplicates ?

    Anyone have a clue how this can be done ... It's actually a bit more complicated then stated above.

    list one has FQDN:

    host1.acme.com


    List two has short names:

    host1

    I need to associate the short name host with the FQDN hostname.


    host1 host1.acme.com 8873 PROD

    Anyone have any idea how to do this?
    Last edited by zooba72; 07-14-2015 at 10:14 PM.

  8. #8
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Comparing Columns to Match Duplicates ?

    Edited:

    I'm not sure I understand the new question now. Can you give a representative example as in post #6 of the more complicated version?

    Another edit:

    Are the target items in the second column always going to be in the same sort order as the first?
    Last edited by FlameRetired; 07-15-2015 at 12:08 AM.
    Dave

  9. #9
    Registered User
    Join Date
    07-22-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Comparing Columns to Match Duplicates ?

    Hello, thanks for the reply. Given the attached .csv, I would need the results to be provide this:

    Host Attribute1 Attribute2
    scvdLPXT02.acme.com 443 Production
    scvdLPXT01.acme.com 443 Production
    AV-X666 443 Test
    AV-x667 998 Test
    ugpar0303.acme.com 443 Development

    I think I need to use vlookup, but can't figure out the proper syntax. Any help would be appreciated. Thanks in advance
    Attached Files Attached Files

  10. #10
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Comparing Columns to Match Duplicates ?

    I can not discern the logic or a pattern between the data and expected output in the uploaded workbook. Even if I could I am not certain how I could make logical assignment of two sets of index numbers AND tell Excel which of the two Host columns and their corresponding records to apply which set of those index numbers to.

    For example: Two of the .acme.com's are assigned their corresponding record parts in Attributes 1 and 2, but the 3rd one is applied to the non-acme.com record in Host2. Any possible emergence of pattern/logic appears more vague and inconsistent from there.

    Can you clarify the selection criteria of records?

  11. #11
    Registered User
    Join Date
    07-22-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Comparing Columns to Match Duplicates ?

    Thanks for looking at this ... I figured it out this morning. I was able to use vlookup to match the hostnames and associate the corresponding attribute columns. I'm all good. Thank you

  12. #12
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Comparing Columns to Match Duplicates ?

    Good to hear.

+ 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. Find duplicates by comparing 3 columns
    By nethravathik in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-24-2013, 07:40 AM
  2. Comparing two columns for duplicates
    By michaelrusk in forum Excel General
    Replies: 1
    Last Post: 07-16-2012, 09:18 AM
  3. comparing multiple columns for duplicates
    By shuddle in forum Excel General
    Replies: 3
    Last Post: 12-20-2011, 12:08 PM
  4. Duplicates in comparing two columns
    By sammyd323 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-26-2007, 10:50 AM
  5. Replies: 0
    Last Post: 02-15-2006, 10:35 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