Forum Discussion
Ralph Kroll
May 28, 2019Copper Contributor
Can you invoke fill series sequencing logic for mixed alphanumeric data within a function?
I have customer transaction item data in a file and am trying to add an alphanumeric invoice number field to each line item (like: 2019-FI-001) before importing them into an accounting application. On each subsequent transaction line I wish to perform logic to determine if the customer number is the same as the previous line and if so keep the invoice number the same, otherwise increment by one. I could do this by splitting the counter (001) and concatenating it to the prefix (2019-FI-) but wondering is there is a function that can do this like the fill series sequencing logic does automatically in a drag fill?
1 Reply
- TwifooSilver ContributorAssuming this example:
Customer Invoice
1 2019-FI-001
2 2019-FI-002
2 2019-FI-002
The formula in B3 is:
=IF(A3=A2,B2,
LEFT(B2,8)&
TEXT(RIGHT(B2,3)+1,"000"))