Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automaticalle generate sequential reference number
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automaticalle generate sequential reference number
I was able to do it but needed to use 2 helper columns that you can
hide or put off to the side somewhe 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 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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automaticalle generate sequential reference number
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 somewhe 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 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to calculate (generate) a cell reference | New Users to Excel | |||
generate a random number and use if function to generate new data | Excel Worksheet Functions | |||
Generate random numberes using reference to the other cell. | Excel Worksheet Functions | |||
sequential number | Excel Discussion (Misc queries) | |||
sequential number | New Users to Excel |