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

# 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.
• 3
2 Solutions

Excel & VBA ExpertCommented:
Why not upload a sample workbook with the desired output mocked up manually to visualize your requirement?
0

Finance 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

Finance 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

Finance 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.