Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

Any ideas?

Thanks

share|improve this question

2 Answers 2

if you're fine with having the formula in another column, you could simply use

=$A2&"-"&countif($A$2:$A2,$A2)

Assuming that the invoice number is in column A and the data starts from row number 2. Copy the formula down to fill.

share|improve this answer

It may work to keep using the count if, but instead of keeping the range the same make the range be from the first row of data until the row you are in. You can swap the account number with this example of using an account name.

A1--john S -- =countif($A$1:A1,A1) -- =1

A2--Betty G -- =countif($A$1:A2,A2) -- =1

A3--john S -- =countif($A$1:A3,A3) -- =2

A4--Mike Z -- =countif($A$1:A4,A4) -- =1

A5--John S -- =countif($A$1:A5,A5) -- =3

A6--Terry T -- =countif($A$1:A6,A6) -- =1

share|improve this answer

Your Answer

 
discard

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.