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.

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 ,
Rachel 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.

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),+CONCAT ENATE(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 ,
Rachel 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 ,
Rachel 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.

Using Excel 2007 and Table:
http://www.savefile.com/files/1896613