Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    East Coast(USA)
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    compare (excel 2003)

    Greetings,

    From the attache file, I like to compare the LIST in cell A against cell C and if there is a match, I want to take the number from the COUNT column (cell D), copy them into cell B, and add them to get one total.
    What is the best way to accomplish this?

    Thank you in advance!
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: compare (excel 2003)

    The data are problematic, since there are trailing spaces after the text values. And I assume that A & T should match A&T, but they don't.

    A possible solution using formulas only is demonstrated in the attached .
    Attached Files Attached Files

  3. #3
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    East Coast(USA)
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: compare (excel 2003)

    Thanks Hans for the reply post.
    The excel file I get from the client monthly is about 400 records from which I have to filter out only needed group & count (about 300 or so records). I attached another file to better explain the process. In the attached file, the tab
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: compare (excel 2003)

    The attached version contains some VBA code to clean up the names and to create the formulas for you. The names of the worksheets are specified near the top of the module. Run the macro Compare to regenerate the formulas (it has already been donw on the attached version).
    Attached Files Attached Files

  5. #5
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    East Coast(USA)
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    compare (excel 2003)

    Hans,
    Sorry for the delay in getting back..
    I've applied your method & more or less it works fine except where there is inconsistent data entry.
    In the attached (little modified) workbook some of the data entry are not consistent:
    spacing of words, abbreviations etc. For example, A&T vs. A & T, A+ MODELING vs. A+ MODELING INC. etc.

    1.How does this affect my data integrity and result as i have about 500 records? Can excel's LEFT function be used or is there other way around this?

    2. From the previous post what exactly does the following line of code?
    mt = Worksheets(MyList).Range("A65536").End(xlUp).Row


    Thank you in advance!
    OCM
    Attached Files Attached Files

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: compare (excel 2003)

    1. You should either get the master list to use the names from "your" list or the other way round, otherwise it'll be virtually impossible to get all matches.

    2. Range("A65536") is the last (bottommost) cell in column A in Excel 2003 (in Excel 2007 there are many more rows).
    End(xlUp) moves up from this cell until a non-blank cell is encountered.
    Row returns the row number of this cell.
    So mt is the row number of the last non-blank cell in column A.

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: compare (excel 2003)

    You can try some NearMatch functions that I created. This will work with limited numbers, but you will have to at least replace all the spaces if the spaces are not important

    Steve

  8. #8
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    East Coast(USA)
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    compare (excel 2003)

    Hans/Steve,
    Thank you both for your reply post.

    Regards,
    OCM

Posting Permissions

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