how do I generate a unique reference number???

[Rachel]

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.

 

[JE McGimpsey]

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"))

 

[Rachel]

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!

 

[Herbert Seidenberg]

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