Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Columns A, B and C contain values which describe a product. I want column D to generate a sequential number based on the contents of a given cell in column A. Together the four values make up the product code.

  • Column A: Location
  • Column B: Species
  • Column C: Date
  • Column D: Reference Number

There are multiple locations which will fill the cells in column A. I want the reference number to begin with 1 for each location. My approach is to use a nested IF formula to isolate the locations, but I can't figure out how to generate a unique sequential reference number. Any thoughts?

share|improve this question
Could you elaborate on how should the reference number (column D) be generated? just sequenced number, or should it depend columns A,B,C? – Michael Oct 15 '12 at 23:25
An example would be useful, too. – Scott Oct 15 '12 at 23:31

Slightly guessing what is required, if you sort your three columns A/B/C with columns labels in Row1 then this formula in D2 (or something similar) and copied down may suit: =IF(AND(A2=A1,OR(B2<>B1,C2<>C1)),D1+1,1). The OR ensures that Species and Date are not both duplicates, the AND requires that the Location is a duplicate of the record immediately above. IF increments the counter if so and resets to 1 if not.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.