• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sumproduct and formatting for 3 digit return value [SOLVED]

bvanscoy678

Member
Hello,


I am using this formula to count the number of unique values in a masterlist. Since I am also counting the heading, it returns the next number I can use as a document unique ID# when I create a new document. (There are two visible worksheet in attached workbook. The below formula is in the createtransfertemplate!B6)


This is the formula I need to modify, so the numbers show up when I copy them (via code) to the masterlist! A column


=IF(C6="","","AT"&RIGHT(YEAR(TODAY()),2)&"-"&SUMPRODUCT((NewTransferID<>"")/COUNTIF(NewTransferID,NewTransferID&"")))


The only problem is that it returns a value with no leading zeros. I manually entered the -001,-002 but when I use the VBA Code within the workbook, my formula above gives me the count, but no leading zero's. I would like to always have 3 numbers, so it looks uniform and lines up (I'll never have more than 999). The next one in line will show AT13-6, but I would like to see AT13-006. I hope that makes sense.


This is the A column from MasterList! (attached workbook)


TransferID

AT13-001

AT13-001

AT13-002

AT13-002

AT13-002

AT13-003

AT13-4

AT13-5


I have thought about splitting the columns, formatting as text, then use leading zeros. I would then need to bring it all back together again. I thought there might be another way with the same worksheet layout.


Thank you in advance for any help.


I did try this, but it was wrong:


=IF(C6="","","AT"&RIGHT(YEAR(TODAY()),2)&"-"&text(SUMPRODUCT((NewTransferID<>"")/COUNTIF(NewTransferID,NewTransferID&"")),000)


https://www.dropbox.com/s/0alr5t1pf86qc74/chandoo%20sumproduct.xlsm
 
Hi Brent ,


I am not sure I have understood you ; can you try this :


=IF(C6="","","AT"&RIGHT(YEAR(TODAY()),2)&"-"&TEXT(SUMPRODUCT(--(NewTransferID<>"")/COUNTIF(NewTransferID,NewTransferID&"")),"000"))


Narayan
 
Back
Top