Auto Add a Number After Auto Numbering

killis_

I have a master sheet which captures data and assigns an auto-number (Incident Number). When a row falls into a certain category (Warranty), it gets copied to a new sheet.

I want to be able to take that unique Incident Number and add to it. This is what I have so far:

It puts a prefix onto the Incident Number, but I also want it to add a letter or number at the end. So it would read W-CIR-00001-A.

Any ideas would be much appreciated.

Thanks!

Paul Newcome

What would be the logic? Would it always be "A", or would you need different letters based on certain variables?

killis_

It would need to be a consecutive letter (or number). It just needs to be something that can uniquely identify the row.

What happens is we get a warranty claim, but there could be multiple items on it. We need to break that claim up per item, i.e. create a row for each item within the claim so that we can identify them individually.

Paul Newcome

A number would definitely be much easier. Would you need it prefilled with leading zeros such as 001, 002 or just 1, 2 is fine?

killis_

Preferably with leading zeros, this will give us a bit of scope if there are lots of items.

Paul Newcome

Ok. Add this to the end of your existing formula:

+ IF(COUNTIFS([Incident Number]:[Incident Number], @cell = [Incident Number]@row, [Date Logged]:[Date Logged], @cell<= [Date Logged]@row)< 10, "00", IF(COUNTIFS([Incident Number]:[Incident Number], @cell = [Incident Number]@row, [Date Logged]:[Date Logged], @cell<= [Date Logged]@row)< 100, "0", "")) + COUNTIFS([Incident Number]:[Incident Number], @cell = [Incident Number]@row, [Date Logged]:[Date Logged], @cell<= [Date Logged]@row)

This will prefill with up to 2 zeros so that you have capacity for up to 999 entries per incident number.