Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2016-07-27
8
Medium Priority
?
93 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 32

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 1040 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 32

Assisted Solution

by:Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj) earned 960 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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 32

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 32

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
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…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

705 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