Hi,
Thanks for that.
But what I needed was a formula that I can drag to automatically generate a
number. Currently I am using this formula:
=IF(B2="","",IF(B2=VLOOKUP(B2,B:C,1,FALSE),+CONCATENATE(B2,"_",)&REPT("0",4-LEN(COUNTIF($B$2:B2,B2)))&COUNTIF($B$2:B2,B2)))
But this formula does not tag the same reference number to a data that is
entered exactly the same (i.e. item#2 below would have a resulting reference
number = to 01 0500247_002) Any help? THANKS!
"JE McGimpsey" wrote:
> Will your results be sorted like that? If so then one way (assuming TA#
> in B, SI Date in C, Amount in D, Rate in E, and ref in F):
>
> F2:
> =B2 & "_0001"
>
> or, if B2 is a number rather than text:
>
> =TEXT(B2,"0000000\_\0\0\01")
>
> and F3:
> =IF(B3&C3&D3&E3=B2&C2&D2&E2,F2,TEXT(B3,"0000000\_") &
> TEXT(IF(B3=B2,RIGHT(F2,4)+1,1),"0000"))
>
>
> In article <0B0B8D6E-2284-4858-9D9F-(E-Mail Removed)>,
> Rachel <(E-Mail Removed)> wrote:
>
> > Hi,
> >
> > I am working on the following table to automatically generate a reference
> > number everytime a data is inputted. Please see below table:
> >
> > Cust No. TA No SI date Amount Rate
> > RESULTING REF
> > 008-018970 01 0500247 22-Sep-08 12,000,000.00 0.25% 01 0500247_0001
> > 008-018970 01 0500247 22-Sep-08 12,000,000.00 0.25% 01 0500247_0001
> > 050-101393 01 0500324 20-Oct-08 5,000,000.00 0.25% 01 0500324_0001
> > 050-101393 01 0500324 20-Oct-08 6,000,000.00 0.25% 01 0500324_0002
> >
> > Is there a formula or a VBS code that I can use to automatically generate
> > above reference number is such a way that below conditions are met:
> > a) format of ref no. ia TA No._#### - #-representing the sequential number
> > b) that when a set of information is repeated in a row it will generate the
> > same reference number as the previous number (see item#1 and #2 - it has the
> > same information for all the column therefore it will generate the same ref #)
> >
> > Is this doable? Any help you <textarea name="txtBody" id="txtBody" CLASS="WNInput" TABINDEX="3" TITLE="Type your message here." STYLE="font:xx-small Verdana,Arial,Helvetica,sans-serif;" ONFOCUS="fnBodyMoveToStart()" rows="10" ACCESSKEY="b" cols="80">
"JE McGimpsey" wrote:
> Will your results be sorted like that? If so then one way (assuming TA#
> in B, SI Date in C, Amount in D, Rate in E, and ref in F):
>
> F2:
> =B2 & "_0001"
>
> or, if B2 is a number rather than text:
>
> =TEXT(B2,"0000000\_\0\0\01")
>
> and F3:
> =IF(B3&C3&D3&E3=B2&C2&D2&E2,F2,TEXT(B3,"0000000\_") &
> TEXT(IF(B3=B2,RIGHT(F2,4)+1,1),"0000"))
>
>
> In article <0B0B8D6E-2284-4858-9D9F-(E-Mail Removed)>,
> Rachel <(E-Mail Removed)> wrote:
>
> > Hi,
> >
> > I am working on the following table to automatically generate a reference
> > number everytime a data is inputted. Please see below table:
> >
> > Cust No. TA No SI date Amount Rate
> > RESULTING REF
> > 008-018970 01 0500247 22-Sep-08 12,000,000.00 0.25% 01 0500247_0001
> > 008-018970 01 0500247 22-Sep-08 12,000,000.00 0.25% 01 0500247_0001
> > 050-101393 01 0500324 20-Oct-08 5,000,000.00 0.25% 01 0500324_0001
> > 050-101393 01 0500324 20-Oct-08 6,000,000.00 0.25% 01 0500324_0002
> >
> > Is there a formula or a VBS code that I can use to automatically generate
> > above reference number is such a way that below conditions are met:
> > a) format of ref no. ia TA No._#### - #-representing the sequential number
> > b) that when a set of information is repeated in a row it will generate the
> > same reference number as the previous number (see item#1 and #2 - it has the
> > same information for all the column therefore it will generate the same ref #)
> >
> > Is this doable? Any help you may provide will be highly appreciated. THANK
> > you so much in advance.
>
Similar Threads
