Increment a Text String

[Petronella]

Hi

I have rows of data representing drawing numbers within various projects. Each row includes a Project Number in the format n.nn.n and a Sequential Number, format nnnn, within each of those projects. So you could have:

1.03.1 0001
1.03.1 0002
1.11.1 0001 etc

I need to be able to calculate what the next sequential number would be for a given project. So if the last used number for project 1.04.1 was 0003 then when I enter 1.04.1 as the project number in a new row I want it to calculate that the next drawing number for that project is 0004.

Ideally without using macros!

Any help appreciated.

John

 

[BAlGaInTl]

Is 1.03.1 0001 one Cell? Or is it split in two columns?

If it's one cell...

Do you, or can you have a separate table that keeps track of the project numbers and the highest drawing number used?

 

[Petronella]

I've cracked it!

The ProjectNumber and SequentialNumber are in separate cells but there is also a cell that concatenates them.

My solution is, and I had to do it via a macro:

Sort the Table on the concatenated field.
Filter the table by the required ProjectNumber.
Find the last SequentialNumber used (bottom row of filter) and store it.
Create new row.
Populate new row with ProjectNumber and stored SequentialNumber + 1.

Voila.

 

[James006]

Hello,

To avoid the macro ... you can use following Array Formula ... in cell B4

=INDEX(B:B,MAX(IF($A$1:A3=A4,ROW($A$1:A3)-MIN(ROW($A$1:A3))+1)))+1

Hope this will help

 

[Rick Rothstein]

If your first project number is in cell A1, then put this formula in cell B1 and copy it down as far as you think you will ever need to...

=IF(A1="","",TEXT(COUNTIF(A1:A$1,A1),"0000"))

 

[Petronella]

If your first project number is in cell A1, then put this formula in cell B1 and copy it down as far as you think you will ever need to...

=IF(A1="","",TEXT(COUNTIF(A1:A$1,A1),"0000"))

Thanks. Not sure I understand. How does this increment the SequenceNumber WITHIN ProjectNumber (see original post)?

J

 

[Rick Rothstein]

Thanks. Not sure I understand. How does this increment the SequenceNumber WITHIN ProjectNumber (see original post)?

First off, it does not affect functionality as Excel will interpret the range correctly, but the standard way of writing a range where one cell is fixed and the other can change as the formula is dragged is to put the fixed cell first in the range and the non-fixed one second (not sure how it happened, but I reversed those to references when I posted my original formula). Here is how the formula should look...

=IF(A1="","",TEXT(COUNTIF(A$1:A1,A1),"0000"))

Now, to your question. I assumed your project numbers were all in Column A by themselves (that is, without the sequence number) and that sequence number you wanted to generate was going to be in Column B (which is where my formula should be placed). The formula I posted looks at Column A in the same row as the formula being evaluated is in to see what the project number is and then counts how many of that project number there are within the range starting at Row 1 (the $ sign in A$1 fixes that reference so that it will not change as the formula is dragged) up to the row in Column A where the formula being evaluated is located. This gives a count of that project only so far up to the row the formula is in (that is what the IF part of COUNTIF does). Then the formula uses the TEXT function to format that sequence number to the format you showed you wanted. Finally, I wrap everything in an IF function call that displays the empty text string ("") if the cell in Column A is empty (that way, you can copy the formula down past the current end of your existing data in anticipation of future data).

 

[Petronella]

Ah yes. I see what you are doing.

However, not unusually, I have not expressed myself as well as I might.

The table already consists of lots of these records sorted in order. Each record consists of two cells, ProjectNumber and SequenceNumber (within Project).

Now, when I create a new record at the bottom of the table and enter the ProjectNumber, I want it to fill in the next SequenceNumber for that ProjectNumber.

I will then re-sort to get all the records in the correct order with the new one slitting into its correct position.

Hope that explains a bit better. Thanks for your help.

John

 

[Rick Rothstein]

The table already consists of lots of these records sorted in order. Each record consists of two cells, ProjectNumber and SequenceNumber (within Project).

Now, when I create a new record at the bottom of the table and enter the ProjectNumber, I want it to fill in the next SequenceNumber for that ProjectNumber.

I will then re-sort to get all the records in the correct order with the new one slitting into its correct position.

Hope that explains a bit better. Thanks for your help.

Is your existing sequence numbers produced by a formula or are they hard-coded? In either case, I would think if you put my formula in cell B1 (assuming your first data is in cell A1) and copied it down over top of your existing sequence numbers and on down past that, everything should still work correctly after you re-sort your data. Try it out on a copy of your worksheet to make sure what I am assuming will actually happen correctly for you.

 

[Petronella]

Is your existing sequence numbers produced by a formula or are they hard-coded? In either case, I would think if you put my formula in cell B1 (assuming your first data is in cell A1) and copied it down over top of your existing sequence numbers and on down past that, everything should still work correctly after you re-sort your data. Try it out on a copy of your worksheet to make sure what I am assuming will actually happen correctly for you.

Dear Rick

THAT IS FANTASTIC!

Compare my solution....

- Sort the Table on the concatenated field.
- Filter the table by the required ProjectNumber.
- Find the last SequentialNumber used (bottom row of filter) and store it.
- Create new row.
- Populate new row with ProjectNumber and stored SequentialNumber + 1.

which works logically but is horribly complex, with yours.....

=IF(A1="","",TEXT(COUNTIF(A1:A$1,A1),"0000"))

So neat and simple.

Many, many thanks.

John