Solved

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

Posted on 2016-07-27
8
84 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
  • 4
  • 3
8 Comments
 
LVL 30

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 50

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 30

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 50

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 30

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 50

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 30

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

: Microsoft Office Collaborate for free and online versions of Microsoft  Word, Excel, Powerpoint, OneNote, Onedrive , Email, Calendar etc. In short we can say that Microsoft office is a suite of servers, applications and services developed by  Micr…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

680 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