Excel Formula: How to increment-alphanumeric-column-by-1 Part 2

mrrmpc
mrrmpc used Ask the Experts™
on
This is a follow up to previous question posted:
https://www.experts-exchange.com/questions/28960037/Excel-Formula-How-to-increment-alphanumeric-column-by-1.html?anchor=a41732303#a41732303

Received a valid, correct answer to that post (thank you Ryan).  I would like to go ahead an inquire and expand on the previous question posted.

My SKU pattern is MPSK-00001-001.  The prefix of the pattern is constant "MPSK". The dashes are part of the pattern and must be retained. And finally must retain leading zeros, padding zeros.

Would like to auto increment the middle part of the pattern the "00001" portion of the pattern +1 when column D(vendor) has a value.  From a previous post I applied the formula =IF(D2="","", "MPSK-" & TEXT(COUNTA($D$2:D2),"00000")&"-001") and this produced desired results to generate the main SKU base.
 
-ELSE-

When column D (vendor)  is blank increment the SKU suffix portion of the pattern +1.  How to nest IF, or how to accomplish the else portion of this question?

The datasheet that I have in Excel I can not add more columns.  So I need to apply the formula starting from column N, and carry it forward to however far I need to drag it down in the future as the list grows.  

Screen shot items in column N(variant SKU) black color were populated with the formula of =IF(D2="","", "MPSK-" & TEXT(COUNTA($D$2:D2),"00000")&"-001") , items in green are the desired additional output.  How to extend the previous code formula is the follow up question.
desired output
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
Please try this....

In N2
=IF(D2&I2="","",IF(D2<>"","MPSK-"&TEXT(COUNTA($D$2:D2),"00000")&"-001","MPSK-"&TEXT(COUNTA($D$2:D2),"00000")&TEXT(RIGHT(N1,3)+1,"000")))

Open in new window

and copy it down.
Top Expert 2016
Commented:
Hi,

pls try

= "MPSK-" & TEXT(COUNTA($D$2:D2),"00000")&"-"&TEXT(IF(D2="",RIGHT(N1,3)+1,1),"000")

Open in new window

Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
@Rgonzo

In your previous formula, you were referring to col. G. Why? Though you tweaked it later.

Also your tweaked formula will keep populating col. N even if there is not data in col. D. ;)
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Top Expert 2016

Commented:
@Neeraj
That's right but looking at the example in the first question it shouldn't be a problem
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
I think user always copy such formulas down the rows in advance and like the formula cells to populate automatically once they fill the column of interest. That's the usual practice. :)
Top Expert 2016

Commented:
then use
=IF(D2&I2="","","MPSK-" & TEXT(COUNTA($D$2:D2),"00000")&"-"&TEXT(IF(D2="",RIGHT(G1,3)+1,1),"000"))

Open in new window

Author

Commented:
In testing the code solutions out, thank you both for responding so quickly, both appeared to work.  The file right now has over 2K row entries that needed to be SKU'd.  The only difference I caught was the loss of the dash in suffix portion of the sku pattern when tested @Neeraj though I modified and appeared to work.

Again thank you both very much.

@Rgonzo
= "MPSK-" & TEXT(COUNTA($D$2:D2),"00000")&"-"&TEXT(IF(D2="",RIGHT(N1,3)+1,1),"000")
rgonzo.PNG
@ Neeraj
=IF(D2&I2="","",IF(D2<>"","MPSK-"&TEXT(COUNTA($D$2:D2),"00000")&"-001","MPSK-"&TEXT(COUNTA($D$2:D2),"00000")&TEXT(RIGHT(N1,3)+1,"000")))
Neeraj.PNG
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
Ah that means I posted the wrong formula. I corrected it but forgot to post and posted the wrong one instead.
My bad.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start Today