Excel - sequential numbering with countif

[Phil Brining]

Hope someoene can help with this.

I have an excel sheet that I routinely import into Salesforce. The sheet is an extract from Sage containing data relating to Orders placed by Customers. Each row in the sheet contains an Order Number and order details. If an order comprised five items then the order number would occur five times in the sheet.

What I want to do is apply a unique text string to each row comprising the Order Number and then a sequential number based on the number of occurrences in the list. E.g. if my list contained the following order numbers: 45678, 45699, 45678, 45234, 45672, 45699, 48230, 45678

I want a formula to create the following:
45678-1, 45699-1, 45678-2, 45234-1, 45672-1, 45699-2, 48230-1, 45678-3

I.e. 45678-3 is the third occurrence of the invoice number 45678 and so on.

I'm using Excel 2007 on Win7.

Any ideas?

Thanks

Phil

 

[StephenCrump]

Welcome to the forum!

If your order numbers start in A1, say, then use this formula for your first cell, and copy/paste it down that column:

=A1&"-"&COUNTIF(A$1:A1,A1)

 

[Phil Brining]

Wow Stephen you're a genius! Thanks - the formula works great. Frustrated that i couldn't crack it.
Thanks
Phil