+ Reply to Thread
Results 1 to 10 of 10

Counting unique names with three column condition

  1. #1
    Registered User
    Join Date
    07-08-2011
    Location
    Sweden
    MS-Off Ver
    Excel 2003
    Posts
    6

    Counting unique names with three column condition

    Hey!

    I'm new to this but I have a problem I would really appreciate getting help with.

    I have a worksheet with over 1000 rows, with a column containing a bunch of names of different projects. Each project can appear several times, since they have changed status over the years. There is a date column, and a status column varying from "Idea", "Project", "Company" and "Archive". So project "A" for example can appear four times if they have gone through all these stages. And to make it complicated there is an Archive ID column with numbers ranging from 0-3. 0 meaning the project is closed, 1 meaning the project has continued externally, 2 meaning the project is on hold, 3 meaning the project is active. So if a project has the status "Archive" a given date you need to chech the "Arhive ID" to get more information on the status. If the project has one of the other statuses the "Archive ID" number means nothing.

    I hope this is not to confusing. My problem here is that I want to know how many ideas have been ended, i.e. I want to count all the projects who only occur two times, first with the status "Idea" and then "Archive" with ID 0. I would actually like to count several combinations, for example idea to project, and project to archive 1,2,3,0. It would be easier to count how many have gone from idea to project since that eliminates the search through the "Archive ID" column, but I guess the solution would be similar.

    So does anybody think they can help? Is it possible to do this without writing a code?

    I really appreciate the help!

    /M
    Last edited by moyounis; 07-11-2011 at 08:05 AM.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Counting unique names with three column condition

    The way I always used to do this was to use an Advanced Filter to extract a list of unique entries and then use SUMIF or SUMPRODUCT to find whatever combinations I was looking for. This can be quite slow depending on the number of formulae, the complexity of the formulae and the volume of data.

    However, I suspect that the recommendation is actually to use a Pivot Table. This is quick and flexible ... and more important, built in and hence as efficient as it's going to be.

    Regards
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    07-08-2011
    Location
    Sweden
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Counting unique names with three column condition

    How would you go about using the advanced filter to do this? Could you give an example of what the formulae would look like? I have tried experementing with the Pivot table but I'm not familiar with it at all.. I can't see how this will get me the results I'm looking for.

    Maybe I could attach an example of the worksheet to better understand your solution?
    /M

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Counting unique names with three column condition

    Maybe I could attach an example of the worksheet to better understand your solution?

    That would be good

    Regards

  5. #5
    Registered User
    Join Date
    07-08-2011
    Location
    Sweden
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Counting unique names with three column condition

    I made a simple example of my problem, since I don't have access to the actual file until tomorrow. Another thing, how would you go about calculating how many days each entry is in respective status?

    I really appreciate the help!
    /M
    Attached Files Attached Files

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Counting unique names with three column condition

    Code to extract unique titles using Advanced Filter and then add formulae to create analysis.

    Please Login or Register  to view this content.


    If you want to keep the formulae, comment out the Copy/PasteSpecial lines.

    See attached example.


    Regards
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    07-08-2011
    Location
    Sweden
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Counting unique names with three column condition

    Thank you! I really appreciate you taking time to help.

    However, my actual worksheet doesn't have the exact same layout so I tried to change the code to fit my worksheet but I can't really get it to work since I don't understand the entire code.

    I've attached the layout of my actual worksheet if you wouldn't mind making the changes needed.

    /M
    Attached Files Attached Files

  8. #8
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Counting unique names with three column condition

    This should match your actual layout:

    Please Login or Register  to view this content.


    Regards
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    07-08-2011
    Location
    Sweden
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Counting unique names with three column condition

    Perfect!

    I'm very grateful!

    /M

  10. #10
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Counting unique names with three column condition

    You're welcome. Thanks for the rep.

    how would you go about calculating how many days each entry is in respective status?

    I think you'd need to sort the records (on the data sheet) into status within title. However, you'd probably need to add a helper column with a status sequence so that you can sort them correctly.

    Once they can be sorted into the correct sequence, you can then subtract one date from the next.

    Regards

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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