Hi all,

I have a project to make in my office. I need to automatically create a
sequential reference number for a set of data. Please see below table.

Cust No. TA No SI date Amount Rate
008-018970 01 0500247 22-Sep-08 12,000,000.00 0.25%
008-018970 01 0500247 22-Sep-08 11,000,000.00 0.25%
050-101393 01 0500324 20-Oct-08 5,000,000.00 0.25%

I have 4 columns (column a-e). What I need to do is to generate a unique
reference code for each item in the row in this format (TA No._MMDDDYY_####)
#-representing the sequential number. Is this doable? thanks!

I was able to do it but needed to use 2 helper columns that you can
hide or put off to the side somewhere:

Assuming your data below is in cols A->E and the first row of data is
row 2

Add 2 helper columns in F and G as follows:
in F2 put: =+B2&"_"&REPT("0",2-LEN(MONTH(C2)))&MONTH(C2)&REPT("0",3-LEN
(DAY(C2)))&DAY(C2)&YEAR(C2)&"_"
In G2 put: =COUNTIF($F$2:F2,F2)

The sequence # will be in col. H as follows:
in H2 put: =+F2&REPT("0",4-LEN(G2))&G2

just copy the formulas down.

The spreadsheet will start to slow down if it gets large so you may
want to copy / paste values over these formulas every so often.



On Nov 19, 11:16*am, Rachel <Rac...@discussions.microsoft.com> wrote:
> Hi all,
>
> I have a project to make in my office. I need to automatically create a
> sequential reference number for a set of data. Please see below table.
>
> Cust No. * * * *TA No * * * * * *SI date * * * * * * * * * * *Amount * * * * * * Rate
> 008-018970 * * *01 0500247 * * *22-Sep-08 * * * *12,000,000.00 *0.25%
> 008-018970 * * *01 0500247 * * *22-Sep-08 * * * *11,000,000.00 *0.25%
> 050-101393 * * *01 0500324 * * *20-Oct-08 * * * *5,000,000.00 * 0.25%
>
> I have 4 columns (column a-e). What I need to do is to generate a unique
> reference code for each item in the row in this format (TA No._MMDDDYY_####)
> #-representing the sequential number. Is this doable? thanks!

HI tim,

Thanks for that reply. Then again what I needed is maybe a VBS code so that
I wont need to copy and past as values the whole data everytime. Nontheless I
tried the formula you had given but it seems there was an error in column f2
which I cant figure out. Thanks anyways!

"Tim879" wrote:

> I was able to do it but needed to use 2 helper columns that you can
> hide or put off to the side somewhere:
>
> Assuming your data below is in cols A->E and the first row of data is
> row 2
>
> Add 2 helper columns in F and G as follows:
> in F2 put: =+B2&"_"&REPT("0",2-LEN(MONTH(C2)))&MONTH(C2)&REPT("0",3-LEN
> (DAY(C2)))&DAY(C2)&YEAR(C2)&"_"
> In G2 put: =COUNTIF($F$2:F2,F2)
>
> The sequence # will be in col. H as follows:
> in H2 put: =+F2&REPT("0",4-LEN(G2))&G2
>
> just copy the formulas down.
>
> The spreadsheet will start to slow down if it gets large so you may
> want to copy / paste values over these formulas every so often.
>
>
>
> On Nov 19, 11:16 am, Rachel <Rac...@discussions.microsoft.com> wrote:
> > Hi all,
> >
> > I have a project to make in my office. I need to automatically create a
> > sequential reference number for a set of data. Please see below table.
> >
> > Cust No. TA No SI date Amount Rate
> > 008-018970 01 0500247 22-Sep-08 12,000,000.00 0.25%
> > 008-018970 01 0500247 22-Sep-08 11,000,000.00 0.25%
> > 050-101393 01 0500324 20-Oct-08 5,000,000.00 0.25%
> >
> > I have 4 columns (column a-e). What I need to do is to generate a unique
> > reference code for each item in the row in this format (TA No._MMDDDYY_####)
> > #-representing the sequential number. Is this doable? thanks!
>
>