Home

Can you invoke fill series sequencing logic for mixed alphanumeric data within a function?

%3CLINGO-SUB%20id%3D%22lingo-sub-653999%22%20slang%3D%22en-US%22%3ECan%20you%20invoke%20fill%20series%20sequencing%20logic%20for%20mixed%20alphanumeric%20data%20within%20a%20function%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-653999%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20customer%20transaction%20item%20data%20in%20a%20file%20and%20am%20trying%20to%20add%20an%20alphanumeric%20invoice%20number%20field%20to%20each%20line%20item%20(like%3A%202019-FI-001)%20before%20importing%20them%20into%20an%20accounting%20application.%26nbsp%3B%20On%20each%20subsequent%20transaction%20line%20I%20wish%20to%20perform%20logic%20to%20determine%20if%20the%20customer%20number%20is%20the%20same%20as%20the%20previous%20line%20and%20if%20so%20keep%20the%20invoice%20number%20the%20same%2C%20otherwise%20increment%20by%20one.%26nbsp%3B%20I%20could%20do%20this%20by%20splitting%20the%20counter%20(001)%20and%20concatenating%20it%20to%20the%20prefix%20(2019-FI-)%20but%20wondering%20is%20there%20is%20a%20function%20that%20can%20do%20this%20like%20the%20fill%20series%20sequencing%20logic%20does%20automatically%20in%20a%20drag%20fill%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-653999%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-654492%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20you%20invoke%20fill%20series%20sequencing%20logic%20for%20mixed%20alphanumeric%20data%20within%20a%20function%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-654492%22%20slang%3D%22en-US%22%3EAssuming%20this%20example%3A%3CBR%20%2F%3ECustomer%20Invoice%3CBR%20%2F%3E1%202019-FI-001%3CBR%20%2F%3E2%202019-FI-002%3CBR%20%2F%3E2%202019-FI-002%3CBR%20%2F%3EThe%20formula%20in%20B3%20is%3A%3CBR%20%2F%3E%3DIF(A3%3DA2%2CB2%2C%3CBR%20%2F%3ELEFT(B2%2C8)%26amp%3B%3CBR%20%2F%3ETEXT(RIGHT(B2%2C3)%2B1%2C%22000%22))%3C%2FLINGO-BODY%3E
Ralph Kroll
Occasional Contributor

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
Assuming 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"))
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies