1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

EXCEL 2 files compare data in 3 columns

Discussion in 'Business Applications' started by shoesy, Mar 21, 2012.

Thread Status:
Not open for further replies.
Advertisement
  1. shoesy

    shoesy Thread Starter

    Joined:
    Mar 21, 2012
    Messages:
    5
    I’m hoping there is a formula within EXCEL that will allow me to complete this comparison. I’ve been working on it all day manually and realize it will take a long time to complete this way.
    My computer is running on Windows 7 Home Premium and my EXCEL is 2010
    I have two different files and within each are three columns of data that I need to compare. They’re labeled NC, NCI, and SECNCI
    To be considered a “match” I need all three fields within a row to match exactly to a row within the other file. The first file called REQUEST NET FILE has over 37000 rows of data so naturally there will be many repeats of these same three fields in that file, but each row where the three fields match any row of data on the second file called DEPLOYED TABLE FILE needs to be the same to be considered a match.
    If it doesn’t match, if possible, I’d also like to know why. This could fall into two different scenarios.
    NC CODE IS NOT ON DEPLOYED TABLE FILE or
    NCI/SECNCI combo not on deployed table for this NC
    Once complete, I’ll run the same process in reverse so both files will end up with a match or no match next to each row.
    If I need to run two separate compares, one to get the match, and then one on just the “no match” results to find the second bit of info, I can do that. Or if the second criteria can’t be found via excel formula, just the “match” “no match” result will be acceptable, as that will save me so much time.
    I was thinking a vlookup could do the compare for me, but I’m not good with vlookup formulas, and after struggling a few hours with it, I started searching and came across your site. I’ve found a few similar topics but nothing that I can translate into something for my specific need.
    I’ve attached portions of the two files, and you can see on the REQUEST NET FILE, where I’ve managed to get to with doing it manually. It’s quite a tedious process, so I’m really hoping you can help.
    Thank you.



    Tech Support Guy System Info Utility version 1.0.0.2
    OS Version: Microsoft Windows 7 Home Premium, Service Pack 1, 64 bit
    Processor: Intel(R) Celeron(R) CPU 900 @ 2.20GHz, Intel64 Family 6 Model 23 Stepping 10
    Processor Count: 1
    RAM: 2008 Mb
    Graphics Card: Mobile Intel(R) 4 Series Express Chipset Family, 780 Mb
    Hard Drives: C: Total - 223434 MB, Free - 177646 MB;
    Motherboard: Dell Inc., 0G848F
    Antivirus: McAfee Anti-Virus and Anti-Spyware, Updated and Enabled
     

    Attached Files:

  2. shoesy

    shoesy Thread Starter

    Joined:
    Mar 21, 2012
    Messages:
    5
    Please...can anyone help? Even just the match/no match portion. I can manually do the compare of the no matches if needed, but really need some help getting thru this a little quicker. Thank you.
     
  3. CDHarm

    CDHarm

    Joined:
    May 26, 2011
    Messages:
    255
    Hi,

    The data you have in the "Req" file has trailing space which can not be removed by a "Trim" function. At least when I tried to do so.
    How is the data loaded in the "Req" file?
    If you can get ride of the "Extra" spaces in the "Req" this would help with a code for you.
    From what I can see the other file is ok as far as extra spaces are concerned.
    I'm not sure you do what you want with formulas, but I know you can do it via code, as I already have some of it worked out.
    But, sad to say I will be gone for several days and may not get this done. If you can provid a clean copy of the "Req" file then someone else may be able to help you.
    For what it's worth here is part of my code.
    Code:
    Sub Match_Three_Columns()
    Application.ScreenUpdating = False
    Dim MyM As Variant
    Dim MyMd As String
    Dim cel As Range
    Dim c As Variant
    Dim firstaddress As Range
    Dim NBSRws As Worksheet
    Set NBSRws = Sheets("NcBySvcReport 1 ")
    Workbooks("DEPLOYED TABLE FILE.xls").Activate
    Dim Dws As Worksheet
    Set Dws = Sheets("Deployed")
    For Each cel In NBSRws.Range("E2:E" & NBSRws.Range("D65536").End(xlUp).Row)
        MyM = Trim(NBSRws.Cells(cel.Row, 5).Text)
        With Dws.Range("D2:D" & Dws.Range("D65536").End(xlUp).Row)
            Set c = .Find(Trim(MyM), lookat:=xlWhole)
            If Not c Is Nothing Then
            '''' This pare not done because of extra space in text ''
                firstaddress = c.Address
                If Dws.Cells(c.Row, 2).Text <> NBSRws.Cells(cel.Row, 2).Text _
                    And Dws.Cells(c.Row, 3).Text <> NBSRws.Cells(cel.Row, 3).Text Then
                    Do
               
                    Set c = .FindNext(c)
                    Loop While Not c Is Nothing And c.Address <> firstaddress
                End If
            Else
            '''' Not found'''
                NBSRws.Cells(cel.Row, 6).Value = "NO MATCH"
            End If
        End With
    Next cel
    End Sub
    
     
  4. shoesy

    shoesy Thread Starter

    Joined:
    Mar 21, 2012
    Messages:
    5
    oh gosh, I don't know VBA or any coding. I deleted rows and columns from my original file to make it fit the size limits for attachments, but not sure what you mean by extra spaces. I was hoping I could get the match/no match by an excel formula, like "match" or "if" or even "vlookup" that I could just copy and paste to a cell and paste down the entire files. I could email the full file if you think that would help.
     
  5. shoesy

    shoesy Thread Starter

    Joined:
    Mar 21, 2012
    Messages:
    5
    I can find no empty spaces in the columns I'm trying to compare, so I'm at a loss what to do next.

    Can anyone else help? Is there a way that an "INDEX/MATCH" or "VLOOKUP" formula can do the match comparison? I've been trying all morning and keep getting errors.

    Would appreciate any help.

    Thanks.
     
  6. shoesy

    shoesy Thread Starter

    Joined:
    Mar 21, 2012
    Messages:
    5
    I went back to the original file and saved as text and tried to redo the import. I found the original data file had breaks in it that could not be removed with either the trim or replace or clear functions. I also could not change to a fixed width to import because the data was not lined up in columns, so I had no choice but to manually remove the extra break space by filtering on each column, selected one at a time, deleting the space up in the formula bar, then pasting that cleared data into the rows below where the data was the same. With only 50 or so unique NC codes, it took less than an hour to complete that task.

    Once the space was removed, a simple vlookup function was able to do the compare and find match/no match on NC codes. Then on all the matched results, I just concatenated the nc/nci/secnci fields and ran the vlookup again to find the nci/secnci no matches.

    There may have been a simpler way to do this, but it worked.
     
  7. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1046092

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice