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.