• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 44
  • Last Modified:

Creating a custom sequence based on another cell

What formula or function can I use to populate a number based on how often a number appears in another column.  I want it to say 1, 2, or 3 and keep that order.  If it is the 5th time a number appears in column B, I want the number to show 2.
0
E. J.
Asked:
E. J.
  • 3
2 Solutions
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Why not upload a sample workbook with the desired output mocked up manually to visualize your requirement?
0
 
Rob HensonFinance AnalystCommented:
With your range of numbers starting in B2, use formula in C2 and copy down as required:

=IF(MOD(COUNTIF(B$2:B2,B2),3)=0,3,MOD(COUNTIF(B$2:B2,B2),3))
0
 
Rob HensonFinance AnalystCommented:
In case you are not aware, the MOD function works as follows.

Syntax = MOD(Number, Divider)

The function will divide the number by the Divider and will return the remainder. So in your scenario, I have used it for dividing the count of the number by 3.

The results will be:

Count = 1 gives 1 divided by 3 equals zero remainder 1
Count = 2 gives 2 divided by 3 equals zero remainder 2
Count = 3 gives 3 divided by 3 equals 1 remainder 0, so forced to a 3 in the formula
Count = 4 gives 4 divided by 3 equals 1 remainder 1
etc etc counts that divide by 3 exactly will have result of zero so forced to a 3, counts that don't divide by 3 will be either 1 or 2.

Hope that explains it.

Thanks
Rob
0
 
Rob HensonFinance AnalystCommented:
Without confirmatio  from author but does as requested
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now