+ Reply to Thread
Results 1 to 7 of 7

Count Unique text against another unique text prior to todays date

  1. #1
    Registered User
    Join Date
    11-21-2011
    Location
    Virginia Beach, VA
    MS-Off Ver
    Excel 2007
    Posts
    11

    Count Unique text against another unique text prior to todays date

    Hello again,

    Tried to use the last solution to figure this out, but this is becoming one of my new favorite pages!

    I have three columns (listed below), I need to count the unique Locations by the designation (IE: the 11-01 designation with 2 Hawaii locations, would be counted once). I need to group this for 3 locations (count the location for Hawaii, San Diego, and Yokosuka to include blanks in the designation) and then checked against today's date (want to list all prior to today's date)

    Based on this list the answer should be: 2

    Location Designation Start Date
    Hawaii 11-01 18-Oct-10
    Hawaii 11-01 18-Oct-10
    Norfolk 11-01 18-Oct-10
    Hawaii 11-01 18-Oct-10
    Norfolk 11-02 29-Nov-10
    Norfolk 11-02 29-Nov-10
    Mayport 11-02 29-Nov-10
    Yokosuka 14-Feb-11
    Norfolk 11-03 4-Apr-11


    Thanks!
    Last edited by VBSK8R; 11-22-2011 at 11:23 AM.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Count Unique text against another unique text prior to todays date

    Assuming the data is in A2:C10, do you mean?

    Please Login or Register  to view this content.
    confirmed with CTRL+SHIFT+ENTER not just ENTER.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    11-21-2011
    Location
    Virginia Beach, VA
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Count Unique text against another unique text prior to todays date

    Here's how I applied:

    =COUNT(1/FREQUENCY(IF($D$11:$D$20000<TODAY(),IF(B2:B10="11-01",MATCH(D11:D20000&H11:H20000&I11:I20000,D11:D20000&H11:H20000&I11:I20000,0))),ROW(D11:D2000)-ROW(D11)+1))

    Column D contains the Location, Column H the Designation, Column I is the Date,

    If column D contains "Hawaii", "San Diego", or "Yokosuka" and the Designation is the same it should count each of them once, if there are two of the same location but only one deisgnation, then it should only be counted once. All of that for prior to today's date

    Column D Column H Column I
    Location Designation Date

    Hawaii 11-01 10/10/10
    Norfolk 11-01 10/10/10
    Yokosuka 11-01 10/10/10
    Hawaii 11-01 10/10/10
    Hawaii 11-02 10/10/11
    San Diego 11-02 10/10/11
    Hawaii 12-01 12/12/11

    The formula should return 4 in the above example:

    Hawaii 11-01, Yokosuka 11-01, Hawaii 11-02, San Diego 11-02

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Count Unique text against another unique text prior to todays date

    The ranges in your formula don't seem to match the description of the columns you gave.

    Also, I had interpreted that you wanted to count unique for "11-01". You can remove this part: IF(B2:B10="11-01" from the formula.

    I am confused about your last comments... why isn't Norfolk counted?

  5. #5
    Registered User
    Join Date
    11-21-2011
    Location
    Virginia Beach, VA
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Count Unique text against another unique text prior to todays date

    It is a breakout of locations, by event (east and west coast) prior to today's date.

    Supported West Coast Events = Hawaii+Yokosuka+San Diego (By event, not by number of occurrences.).

    Does this explain it better?

  6. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Count Unique text against another unique text prior to todays date

    So is it?

    Please Login or Register  to view this content.
    CSE confirmed
    Last edited by NBVC; 11-22-2011 at 02:14 PM. Reason: cleaned out extraneous $

  7. #7
    Registered User
    Join Date
    11-21-2011
    Location
    Virginia Beach, VA
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Count Unique text against another unique text prior to todays date

    You are the man!! Thanks!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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