# Apply invoice line item numbers to rows in excel

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

-

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.

-

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

-