Solved

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

Posted on 2016-07-27
8
88 Views
Last Modified: 2016-07-28
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
0
Comment
Question by:mrrmpc
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 31

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41732395
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.
0
 
LVL 52

Accepted Solution

by:
Rgonzo1971 earned 260 total points
ID: 41732400
Hi,

pls try

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

Open in new window

0
 
LVL 31

Assisted Solution

by:Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj) earned 240 total points
ID: 41732407
@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. ;)
0
Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 
LVL 52

Expert Comment

by:Rgonzo1971
ID: 41732409
@Neeraj
That's right but looking at the example in the first question it shouldn't be a problem
0
 
LVL 31

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41732411
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. :)
0
 
LVL 52

Expert Comment

by:Rgonzo1971
ID: 41732412
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

0
 

Author Comment

by:mrrmpc
ID: 41732914
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
0
 
LVL 31

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41732939
Ah that means I posted the wrong formula. I corrected it but forgot to post and posted the wrong one instead.
My bad.
0

Featured Post

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

622 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question