+ Reply to Thread
Results 1 to 12 of 12

Count consecutive same text starting from the latest date going backwards

  1. #1
    Registered User
    Join Date
    10-17-2012
    Location
    Manila, Philippines
    MS-Off Ver
    Office 365
    Posts
    11

    Count consecutive same text starting from the latest date going backwards

    Hi,

    I've been struggling in automating to count consecutive same text starting from the latest date going backwards. Attached here is a sample excel file. Does anyone have an idea on how to execute this? Thanks!
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,038

    Re: Count consecutive same text starting from the latest date going backwards

    There's probably a better way to do this, but here's one solution

    =12-MAX(IF(C2:K2=LOOKUP(2,1/(C2:K2<>L2),C2:K2),COLUMN(C2:K2)))
    Array formula, use Ctrl-Shift-Enter

    LOOKUP(2,1/(C2:K2<>L2) find the last occurrence in a row of the value which is NOT equal to L2 the last value in the row.
    The rest of the formula finds the maximum column number where that value exists.
    The result is subtracted from 12 (Column L is the 12th column in the sheet) to give the final value.
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Registered User
    Join Date
    10-17-2012
    Location
    Manila, Philippines
    MS-Off Ver
    Office 365
    Posts
    11

    Re: Count consecutive same text starting from the latest date going backwards

    Hi Special-K,

    Thank you for your answer. Will there be a way of doing this without using array?

  4. #4
    Forum Expert
    Join Date
    10-02-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    1,222

    Re: Count consecutive same text starting from the latest date going backwards

    Typically when you need to compare part of a data set against all other parts of a data set (1 compared to 2, 1 compared to 3, 1 compared to 4, etc) then you need to use an array formula as this is effectively what an array allows for, performing the same calculation on each element of your data set instead of on the data set as a whole. Specifically in your case, to know if a number is consecutive we need to compare it against other values to discover if it is.

    So the short answer is, there is unlikely to be a non array style formula capable of what you asked for in this case
    Ways to get help: Post clear questions, explain your overall goal, supply as much background as possible, respond to questions asked of you by those trying to help and post sample(s) files.

    "I am here to help, not do it for people" -Me

  5. #5
    Registered User
    Join Date
    10-17-2012
    Location
    Manila, Philippines
    MS-Off Ver
    Office 365
    Posts
    11

    Re: Count consecutive same text starting from the latest date going backwards

    Thank you Zer0Cool for the clarification. Appreciate it.

  6. #6
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,880

    Re: Count consecutive same text starting from the latest date going backwards

    Non-CSE alternative:

    =12-AGGREGATE(14,6,(C2:K2=LOOKUP(2,1/(C2:K2<>L2),C2:K2))*COLUMN(C2:K2),1)

  7. #7
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,032

    Re: Count consecutive same text starting from the latest date going backwards

    Another way:

    =12-LOOKUP(12,COLUMN(C2:L2)/(C2:L2<>L2))

  8. #8
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,038

    Re: Count consecutive same text starting from the latest date going backwards

    You could use AGGREGATE but you're using Excel 2007 so AGGREGATE won't work.
    I'll have another think...

  9. #9
    Registered User
    Join Date
    10-17-2012
    Location
    Manila, Philippines
    MS-Off Ver
    Office 365
    Posts
    11

    Re: Count consecutive same text starting from the latest date going backwards

    Thank you Phuocam and 63falcondude. The non-CSE alternatives solved it for me.

  10. #10
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,880

    Re: Count consecutive same text starting from the latest date going backwards

    Happy to help. Thanks for the rep! I just converted the formula from post #2.

    If AGGREGATE worked for you, please update your profile to show the current version of Excel that you have.

  11. #11
    Registered User
    Join Date
    10-17-2012
    Location
    Manila, Philippines
    MS-Off Ver
    Office 365
    Posts
    11

    Re: Count consecutive same text starting from the latest date going backwards

    Special-K, the aggregate solved it for me. The excel version I used is from Office 365, forgot update it. Anyways, thank you for your time.

  12. #12
    Registered User
    Join Date
    10-17-2012
    Location
    Manila, Philippines
    MS-Off Ver
    Office 365
    Posts
    11

    Re: Count consecutive same text starting from the latest date going backwards

    63falcondude I've updated it already. Thank you all for the inputs. Really appreciate it. This is solved.

+ 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. [SOLVED] Count X with the specified starting date
    By angelosison00 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-24-2018, 05:53 AM
  2. Replies: 8
    Last Post: 04-08-2015, 10:10 PM
  3. Replies: 20
    Last Post: 05-02-2014, 06:42 AM
  4. Replies: 4
    Last Post: 02-06-2014, 04:24 PM
  5. Replies: 2
    Last Post: 11-28-2013, 03:13 PM
  6. Count & Latest Date
    By goodboy in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-04-2013, 02:25 AM
  7. count unique records with latest date
    By xtrmhyper in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-21-2005, 07:05 AM

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