+ Reply to Thread
Results 1 to 12 of 12

Return Sequential Numbers Based on Unique Values

  1. #1
    Forum Contributor
    Join Date
    06-21-2010
    Location
    -
    MS-Off Ver
    Excel 2010
    Posts
    1,211

    Return Sequential Numbers Based on Unique Values

    I am looking for a formula that will return sequential numbers based on the first itteration of a combination of two ranges and then zero for the other itterations of the same combination. I've attached a sample to demonstrate.

    Many thanks
    Thanks,
    HangMan

    You can say "Thank you!" by clicking Add Reputation below the post.
    Please, mark your thread [SOLVED] if you are happy with the solution.

  2. #2
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,359

    Re: Return Sequential Numbers Based on Unique Values

    =sumproduct(--($A$2:A2=A2),--($D$2:D2=D2))

  3. #3
    Forum Contributor
    Join Date
    06-21-2010
    Location
    -
    MS-Off Ver
    Excel 2010
    Posts
    1,211

    Re: Return Sequential Numbers Based on Unique Values

    Hi tim,

    I'm afraid this doesn't give the expected result, it gives the next sequential number to each instance of the same column A and D combination, what I need is the same number for each combination (with the first instance showing the sequential number and other instances showing zero.

    If you add this in column J and then compare it to the expected result in column I you'll see this doesn't give the correct result.

    I could possibly get away witout having the zero's and just having the same number for each occurance of the same combination for columns A and D.
    Last edited by HangMan; 10-31-2015 at 12:08 PM.

  4. #4
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,658

    Re: Return Sequential Numbers Based on Unique Values

    I think you have some errors in col I.

    Row\Col
    A
    D
    I
    J
    K
    1
    Code
    Supplier
    Desired
    Helper
    Formula
    2
    AB123
    12378
    1
    AB123-12378
    1
    3
    AB124
    26546
    1
    AB124-26546
    1
    4
    AB123
    54687
    2
    AB123-54687
    2
    5
    AB124
    46390
    1
    AB124-46390
    2
    6
    AB127
    62983
    1
    AB127-62983
    1
    7
    AB123
    54378
    3
    AB123-54378
    3
    8
    AB123
    12378
    0
    AB123-12378
    0
    9
    AB123
    12378
    0
    AB123-12378
    0
    10
    AB131
    87345
    1
    AB131-87345
    1
    11
    AB132
    87345
    1
    AB132-87345
    1
    12
    AB124
    26546
    0
    AB124-26546
    0
    13
    AB124
    26546
    0
    AB124-26546
    0
    14
    AB133
    87345
    1
    AB133-87345
    1
    15
    AB133
    87345
    0
    AB133-87345
    0
    16
    AB124
    26546
    0
    AB124-26546
    0
    17
    AB123
    46390
    4
    AB123-46390
    4
    18
    AB136
    46390
    1
    AB136-46390
    1
    19
    AB124
    46390
    0
    AB124-46390
    0
    20
    AB141
    87345
    1
    AB141-87345
    1
    21
    AB127
    87345
    1
    AB127-87345
    2
    22
    AB124
    87345
    1
    AB124-87345
    3
    23
    AB144
    87345
    1
    AB144-87345
    1
    24
    AB130
    54687
    1
    AB130-54687
    1
    25
    AB146
    54378
    1
    AB146-54378
    1
    26
    AB147
    87345
    1
    AB147-87345
    1
    27
    AB123
    54687
    0
    AB123-54687
    0
    28
    AB149
    54687
    1
    AB149-54687
    1
    29
    AB127
    62983
    0
    AB127-62983
    0
    30
    AB124
    54378
    1
    AB124-54378
    4


    J2 and down: =A2 & "-" & D2

    In K2, confirmed with Ctrl+Shift+Enter and copied down,

    =IF(COUNTIF($J$1:K1, J2), 0, MAX(IF($A$1:$A1=A2, $K$1:K1)) + 1)
    Entia non sunt multiplicanda sine necessitate

  5. #5
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    8,003

    Re: Return Sequential Numbers Based on Unique Values

    i2=IF(ROWS(K$2:K2)=1,1,IF(COUNTIFS(A$2:A2,A2,D$2:D2,D2)>1,0,MAX(INDEX((A1:A$2=A2)*I1:I$2,0))+1))
    Please Login or Register  to view this content.
    try this and copy towards down
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  6. #6
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    7,863

    Re: Return Sequential Numbers Based on Unique Values

    I came up with:
    H2:
    Please Login or Register  to view this content.
    I2:
    Please Login or Register  to view this content.
    with results similar to shg's and discrepancy in I5, 21,22 and 30.
    What is correct?

  7. #7
    Forum Contributor
    Join Date
    06-21-2010
    Location
    -
    MS-Off Ver
    Excel 2010
    Posts
    1,211

    Re: Return Sequential Numbers Based on Unique Values

    @shg

    Yes, you are right, there was an error, thanks for picking that up, I've corrected it in the attachment.

    Thanks also to Bebo and nflsales... all formula's work, many thanks... I'm curious to understand if any one formula is more efficient than any other as I need to apply this to some 250,000 rows!

    The second challenge is to make the 1st instance, i.e. the instance that returns 1 for each combination of A and D be the one where the SUM of the values for that combination in column G is the highest...

    So taking AB123 as an example, the SUM of each combination is

    AB123 - 12378 = 25,352.00
    AB123 - 46390 = 2,639.00
    AB123 - 54378 = 15,527.00
    AB123 - 54687 = 28,438.00

    The key is that using the same logic AB123 - 54687 now should = 1. In an ideal world and probably formulaically it would be great if it worked in order of value so that

    AB123 - 12378 = 2
    AB123 - 46390 = 4
    AB123 - 54378 = 3
    AB123 - 54687 = 1

    Is that possible or asking the impossible?

    Many thanks to everyone, it's really interesting to see the differing approcaches...

  8. #8
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Return Sequential Numbers Based on Unique Values

    with a pivot table.

    see the attached file.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  9. #9
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,590

    Re: Return Sequential Numbers Based on Unique Values

    Based on the file from your another thread, try this Array Formula which requires confirmation with Ctrl+Shift+Enter instead of Enter alone.

    In K2
    Please Login or Register  to view this content.
    and copy down.
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  10. #10
    Forum Contributor
    Join Date
    06-21-2010
    Location
    -
    MS-Off Ver
    Excel 2010
    Posts
    1,211

    Re: Return Sequential Numbers Based on Unique Values

    @ oeldere

    Sadly, whilst a pivot table would give me the answer, I can't practically use one in this instance owing to the nature of the structure of the spreadsheet, but many thanks for your reply.


    @sktneer

    That gives the perfect answer in a formula which is exactly what I need, I now just need to break it down to understand it, many thanks...

  11. #11
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,590

    Re: Return Sequential Numbers Based on Unique Values

    You're welcome. Glad I could help.
    Thanks for the feedback also.

  12. #12
    Forum Contributor
    Join Date
    06-21-2010
    Location
    -
    MS-Off Ver
    Excel 2010
    Posts
    1,211

    Re: Return Sequential Numbers Based on Unique Values

    No Problem, thanks for your help...
    Last edited by HangMan; 11-08-2015 at 07:37 PM.

+ 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. Assign sequential numbers to unique values in a list
    By penfold in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 09-30-2017, 02:11 PM
  2. Replies: 6
    Last Post: 07-11-2015, 08:02 PM
  3. Adding Sequential Numbers in Column B Based on Column A Values
    By habsfan1433 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 02-14-2014, 04:17 PM
  4. Return number of unique values based on values in other column
    By Medir in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-26-2013, 11:17 AM
  5. [SOLVED] Return unique values based on another column's value
    By bd528 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 07-26-2013, 04:02 AM
  6. find and return sequential numbers
    By twisty in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-27-2013, 10:43 AM
  7. [SOLVED] Generating unique sequential numbers using VBA
    By onwell in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-01-2013, 11:15 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