Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Complex List Problem (Excel 2003)

    Here's the problem: I have a download of hundreds of companies organized by Zip codes. I also have a target company, which by definition has at least one branch in each of the zip codes. The other listed companies may or may not have branches in each of the zip codes, and they may have multiple branches in each of the zip codes. This means that the other listed companies may (and mostly do) appear more than once in the download, even within the same zip. However, there may be only a couple of hundred unique companies within this list, which I can get by Data==>Filter==>Advanced Filter==>Unique Records Only. What I need to do is to count the number of each company within each zip and compare that number to the number of the target companies within each zip. I then want the LOWER of the two numbers in each case. Finally, I need to total (or aggregate) the numbers for each company for all zips. Whew!

    I already know that I can do this (or most of this) in a pivot table, but this task is part of a larger model and the pivot table solution doesn't fit what is needed for the final report. So I am looking for either a formula or VBA code that will let me get those numbers and enter them in a table.

    I have attached a sample file that shows the target company (B2), the entire list of companies by zip (A3:B1278) and the list of unique companies (D3252).

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

    Re: Complex List Problem (Excel 2003)

    Would it be possible for you to use Access? This could probably be done with one or (probably) more queries in an Access database.

  3. #3
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Complex List Problem (Excel 2003)

    Hi Hans and thanks for your response.

    The bad news is that it is not possible to use Access. I only sent a small portion of the actual downloaded data and the problem I described is actually only a part of a larger model. I need to code the entire thing so a user can click a button and get a result.

    The good news is that I had a breakthrough and (I think) am on the way to fully resolving the problem in VBA. Basically I created two arrays and several counters and I was able to generate accurate numbers for the first three companies. I checked the results manually in a pivot table, so I'm pretty sure I have it now, but I'll know for certain on Monday when I pick it up again.

    Thanks again for your reply and your suggestion. I'm sure that if I don't have the answer I'll be back for more advice.

    Regards,

Posting Permissions

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