+ Reply to Thread
Results 1 to 4 of 4

Increment value with criteria for every Nth duplicate

  1. #1
    Valued Forum Contributor
    Join Date
    04-13-2011
    Location
    Ottawa, Canada
    MS-Off Ver
    365
    Posts
    1,067

    Increment value with criteria for every Nth duplicate

    Excel gurus,

    given that I have data in which they're not in order (for this purpose, we can't use 'sort & filter' to have the data sorted - in other words, use the data as is but accomplished via formula), how do I increment a value with criteria for every Nth duplicate.

    And this increment also needs to take the MIN value of that criteria as the start, and then increase.

    For example, looking at the screenshot,

    The original data is column A and column B.

    Again, the data is not in order. Thankfully, for each data (A, B or C), the year associated with it is the same.

    Therefore, the "minimum" value for A is 2021, B is 2022 and C is 2023 as the "start point".

    And for every nth duplicate, in this case the desired is 2 which is found in cell G2, I'd like to increment by 1.

    Therefore, taking A for example, the 1st 2 duplicate will be 2021, and if it found the next 2 duplicate, it'll be 2022 and so on.

    Increment value with criteria for every Nth duplicate.png

  2. #2
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Increment value with criteria for every Nth duplicate

    In D2 copied down

    =INT(COUNTIFS($A$1:$A1,$A2,$B$1:$B1,$B2)/$F$2)+$B2
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  3. #3
    Valued Forum Contributor
    Join Date
    04-13-2011
    Location
    Ottawa, Canada
    MS-Off Ver
    365
    Posts
    1,067

    Re: Increment value with criteria for every Nth duplicate

    Perfect!

    Solved and reps up to you @kvsrinivasamurthy

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Increment value with criteria for every Nth duplicate

    Hahaha!

    Far better than my convoluted effort!!

    =IFERROR(INDEX(D$1:D1,AGGREGATE(14,6,ROW(B$1:B1)/(A$1:A1=A2),1)),B2)+IF(COUNTIF(A$2:A2,A2)=1,0,IF(MOD(COUNTIF(A$2:A2,A2),$F$2)=1,1,0))
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

+ 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: 4
    Last Post: 11-15-2019, 10:37 AM
  2. Macro To Copy Data, Paste, Then Increment In Order To Duplicate Functionality
    By oettinger1919 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-26-2019, 05:10 AM
  3. Increment counter based on cell criteria
    By Sunvinmon in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-29-2016, 10:58 PM
  4. How to sumifs auto increment date change for a criteria?
    By Ftsoi in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-19-2016, 02:42 PM
  5. [SOLVED] Can I increment the 'criteria' part of SUMIF function across columns
    By ex66 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-24-2015, 06:23 AM
  6. Increment count if not duplicate
    By imachristiansoldier in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 06-09-2014, 04:43 AM
  7. increment number if criteria is matched
    By faisalmalak in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-16-2013, 05:11 AM

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