+ Reply to Thread
Results 1 to 13 of 13

Countif formula using id strings (column1) and dates (column2): what's the best approach

  1. #1
    Registered User
    Join Date
    10-14-2013
    Location
    Ohio, US
    MS-Off Ver
    Excel 2010 - 2016
    Posts
    46

    Countif formula using id strings (column1) and dates (column2): what's the best approach

    Here's what I need to figure out:
    Column1 has a list of 6-digit id strings sorted ascending with a varying amount of duplicates for each id string
    Column2 has accompanying dates entries of the ids

    I need to figure out a formula that counts ids with the most recent date, so that I can filter my list down to having 1 distinct entry per id based on the most recent date.

    I've attached an example. Thanks in advance.
    Attached Files Attached Files

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,684

    Re: Countif formula using id strings (column1) and dates (column2): what's the best approa

    you can use a pivot table and get the unique ID and max date
    Attached Files Attached Files
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Registered User
    Join Date
    10-14-2013
    Location
    Ohio, US
    MS-Off Ver
    Excel 2010 - 2016
    Posts
    46

    Re: Countif formula using id strings (column1) and dates (column2): what's the best approa

    Thanks that makes sense. Cheers!

  4. #4
    Registered User
    Join Date
    10-14-2013
    Location
    Ohio, US
    MS-Off Ver
    Excel 2010 - 2016
    Posts
    46

    Re: Countif formula using id strings (column1) and dates (column2): what's the best approa

    I just realized that a pivot table will work for the example I provided but not the actual dataset I am using, so my apologies for not being able to describe this with the best details. Since this comes from a much larger dataset (A1:AF821) I don't think a pivot table would work. I think this is why I considered a countif situation but am stumped on the logic. There may even be need for a vlookup but, again, I cannot get the logic right. Any help would be appreciated.

  5. #5
    Registered User
    Join Date
    10-14-2013
    Location
    Ohio, US
    MS-Off Ver
    Excel 2010 - 2016
    Posts
    46

    Re: Countif formula using id strings (column1) and dates (column2): what's the best approa

    Here's a more complete version. You can see that it is too large for a pivot table.

  6. #6
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,684

    Re: Countif formula using id strings (column1) and dates (column2): what's the best approa

    as you have multiple columns - can you post a sample of the data and the result you want from that dataset please

  7. #7
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,684

    Re: Countif formula using id strings (column1) and dates (column2): what's the best approa

    cross posts

    So what columns are we trying to find the max date and what ID are we using ?

  8. #8
    Registered User
    Join Date
    10-14-2013
    Location
    Ohio, US
    MS-Off Ver
    Excel 2010 - 2016
    Posts
    46

    Re: Countif formula using id strings (column1) and dates (column2): what's the best approa

    Here's a sample of the sheet using only the columns I spoke of earlier. The relevant columns are P & Q. Sheet 2 breaks down what I was expecting as a result but I'm open to whatever is possible to extract the data.
    Attached Files Attached Files

  9. #9
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,684

    Re: Countif formula using id strings (column1) and dates (column2): what's the best approa

    you have the same IDS with the same date - so how do you treat those ?

  10. #10
    Registered User
    Join Date
    10-14-2013
    Location
    Ohio, US
    MS-Off Ver
    Excel 2010 - 2016
    Posts
    46

    Re: Countif formula using id strings (column1) and dates (column2): what's the best approa

    The datasheet consists of raw data taken from a template scanning program so there may be duplicate submissions. I will remove duplicates later. My apologies for not doing to before creating the sample.

  11. #11
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,684

    Re: Countif formula using id strings (column1) and dates (column2): what's the best approa

    try using
    =IF($A2="","",IF(MAX(IF($A$2:$A$21=$A2,$B$2:$B$21))=B2,1,""))
    dont use enter - you need to use
    shift + control + enter
    so that it becomes an array formula with {} around the formula

    see attached
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    10-14-2013
    Location
    Ohio, US
    MS-Off Ver
    Excel 2010 - 2016
    Posts
    46

    Re: Countif formula using id strings (column1) and dates (column2): what's the best approa

    Thanks! That worked splendidly.

  13. #13
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,684

    Re: Countif formula using id strings (column1) and dates (column2): what's the best approa

    your welcome
    thanks for the rep

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 6
    Last Post: 07-01-2014, 10:56 AM
  2. [SOLVED] Counting cells based on date in column1 and an alpha-numeric value in column2
    By JStreck in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-24-2013, 12:47 PM
  3. Delete row containing column1 column2 etc
    By lemonstar in forum Excel General
    Replies: 2
    Last Post: 09-26-2011, 11:26 AM
  4. Replies: 4
    Last Post: 08-03-2011, 08:22 PM
  5. compare column1 with column2
    By myguess21 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 12-12-2005, 03:55 PM

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