Sequencial

[cainmedussa]

Hi,

I have a little problem with CONCATENATE formula, “RU-14-10-001″, where RU=Revenue Unit, “14″=Year, “10″=Month and “001″=series number. Note that series number will reset every month.

A1=RU-14-10-001, if “B2″ is NOT blank then “A2″=RU-14-10-002. I want it to automatically assign a sequence number in Column “A” every time I Update Column “B”.

Please see attached excel file.

Thanks

[Punnam]

=CONCATENATE("RU-",TEXT(B5,"yy-mm"),"-",TEXT(ROW()-4,"000"))
try this in A5 & drag it down

Punnam

[martindwilson]

try in c5
="RU"&"-"&TEXT(B5,"yy-mm")&"-"&TEXT(SUMPRODUCT(--(TEXT($B$5:B5,"mmyy")=TEXT(B5,"mmyy"))),"000")

[Punnam]

Hi cainmedussa ,

Take some time to give us the feedback , let us know whether your question answered or need any changes .

Punnam

[cainmedussa]

Hi,

Thanks to your reply, your formula also works fine, but I also want the result to be blank if Column "B" is blank.
Sorry, I did not include it in my previous Post.

Thanks again.

[samba_ravi]

if(B5="","","RU"&"-"&TEXT(B5,"yy-mm")&"-"&TEXT(SUMPRODUCT(--(TEXT($B$5:B5,"mmyy")=TEXT(B5,"mmyy"))),"000"))
Try this

[martindwilson]

=IF(B5="","","RU"&"-"&TEXT(B5,"yy-mm")&"-"&TEXT(SUMPRODUCT(--(TEXT($B$5:B5,"mmyy")=TEXT(B5,"mmyy"))),"000")) will give what you asked for
RU-14-10-001
RU-14-10-002
RU-14-11-001
RU-14-11-002
RU-14-12-001
RU-14-12-002
RU-14-12-003
RU-14-12-004
RU-14-12-005

=CONCATENATE("RU-",TEXT(B5,"yy-mm"),"-",TEXT(ROW()-4,"000"))
will give
RU-14-10-001
RU-14-10-002
RU-14-11-003
RU-14-11-004
RU-14-12-005
RU-14-12-006
RU-14-12-007
RU-14-12-008
RU-14-12-009
which is not the same thing

[cainmedussa]

Thank you very much. problem solved.

[Punnam]

@ martindwilson,

Thank for correcting me , i didn't observed the second part of OP requirement

@ cainmedussa ,

Please refer post 7 , my solution will not take second part of your requirement.
Punnam

[boopathiraja]

Hi Try this

Please Login or Register  to view this content.