Ask Experts Questions for FREE Help !
Ask
    Jovacle's Avatar
    Jovacle Posts: 5, Reputation: 1
    New Member
     
    #1

    Nov 3, 2008, 12:23 AM
    Spreadshet Sequential Number per Column Record
    Hi Guys,

    As per the example below, I have two columns (A & B) which I have that are to be joined along with a sequential number to make up part of a final number (column C). Columns A & B need to be joined and then an additional number must be appended to create a unique number. The final sequential number though must be unique per the value in column A. Columns C shows a manual example of what the result should be only having columns A & B to work with. Do you have any ideas how this could be done with Excel formulas or SQL/Access code?:confused::rolleyes:

    Column_A Column_B Column_C
    103 AAA 103-AAA-001
    103 AAA 103-AAA-002
    103 BBB 103-BBB-003
    103 BBB 103-BBB-004
    103 BBB 103-BBB-005
    107 AAA 107-AAA-001
    107 AAA 107-AAA-002
    107 KKK 107-KKK-003
    107 BBB 107-BBB-004
    107 BBB 107-BBB-005
    ScottGem's Avatar
    ScottGem Posts: 64,966, Reputation: 6056
    Computer Expert and Renaissance Man
     
    #2

    Nov 3, 2008, 06:59 AM

    Are you trying to do this in Access or Excel? In Access you would do it differently.

    First you would add an autonumber field to your table. This would be your primary key. Next you would add a Integer field (call it Increment).

    When you add a record to your table you would populate the Increment field with an expression like the following:

    Me.txtIncrement = Nz(DMax("[Increment]","tablename","[fieldA] = " & Me.txtfieldA & " AND [fieldB] = '" & Me.txtfieldB & "'"),0)+1

    Finally, when you want to display the full code you would use an expression like:

    =[fieldA] & "-" & [field]B & "-" & Format([Increment]."000")
    Jovacle's Avatar
    Jovacle Posts: 5, Reputation: 1
    New Member
     
    #3

    Nov 16, 2008, 07:13 PM
    Quote Originally Posted by ScottGem View Post
    Are you trying to do this in Access or Excel? In Access you would do it differently.

    First you would add an autonumber field to your table. This would be your primary key. Next you would add a Integer field (call it Increment).

    When you add a record to your table you would populate the Increment field with an expression like the following:

    Me.txtIncrement = Nz(DMax("[Increment]","tablename","[fieldA] = " & Me.txtfieldA & " AND [fieldB] = '" & Me.txtfieldB & "'"),0)+1

    Finally, when you want to display the full code you would use an expression like:

    =[fieldA] & "-" & [field]B & "-" & Format([Increment]."000")

    Thanks Man!:)

    Awesome!!

    :):):)

Not your question? Ask your question View similar questions

 

Question Tools Search this Question
Search this Question:

Advanced Search

Add your answer here.


Check out some similar questions!

Sequential numbering in Excel [ 3 Answers ]

Hi, I'm trying to number a column in sequential order by counting the number of times a certain word is typed in another range. For example: AAA 1 BBB 1 AAA 2 AAA 3 CCC 1 I need the numbers to be in sequence in column B:B every time a word is typed multiple times in column A:A.

Create column with descending numbers corresponding to data in another column. [ 1 Answers ]

I created an alphabetized column with names. In another column I put an x next to those names that were heads of households leaving the rest blank. I would like to create a new column that would recognize the x then insert a number in the cell next to the x in descending order (i.e. 1-50).

New sheet needs new sequential number [ 3 Answers ]

When I send a new submittal sheet, I would like to auto assign a new number to a submittal sheet each time I open it. As I have it today, the form is located on the worksheet as an additional tab. Any help is appreciated! Thanks

How to find the number in another column? [ 7 Answers ]

Dear all, I wanted to find the numbers of column B which are present in Column A? Could any one provode the formula. A B 201 35 211 53 21254 212


View more questions Search