Need a Formula to give a group a unique number

[rbpd5015]

Hello everybody,

I have been tasked at my Police Department with tracking ticket books. Each ticket book is unique and assigned to an indivudual officer.

What I need to do is dig through 6 years of excel data over 25k tickets and assign each book a unique number. I have a file with no personal data just ticket numbers and am hoping someone could help me with a formula to give every ticket book its unique number. I will explain the numbering system below

Tickets are numbered the following way

1234-ABC

each book contains 25 tickets. so its starts as follows

xx01-ABC through xx25-ABC then the next book in that series is xx26-ABC through xx50-ABC and so on and so on.

below I will post a small sample of the rows I did myself by hand. I have also attached the file if anybody would like to take a stab at this. as long as you keep the ID I can match it back to the original data.

Thanks a million,

Matt

[NBVC]

Try at D5:

=D4+IF(C5<>C4,1,IF(INT((B5-1)/25)=INT((B4-1)/25),0,1))

copied down

[rbpd5015]

Try at D5:

=D4+IF(C5<>C4,1,IF(INT((B5-1)/25)=INT((B4-1)/25),0,1))

copied down

Can seem to make this work just shows 1's

[NBVC]

See attached

[rbpd5015]

See attached

WOW thanks

Almost perfect

it screwed up on row

B10075 because I had a ' in the number I removed that and it worked but the cells below didnt work? what do I need to do to make them recalculate?

Matt

[bebo021999]

Try this in any cell in row 2 for new ID:
=TEXT(ROW()-1,"0000")&"-ABC"
Copy down.
Does it work?

[rbpd5015]

Try this in any cell in row 2 for new ID:
=TEXT(ROW()-1,"0000")&"-ABC"
Copy down.
Does it work?

I probably confused you.

The ticket range must stay the same I just used ABC to show it was Letters.

The unique book number can be anything as simple as 1,2,3,4,....

I am just trying to make it easy to say let me see all the tickets from book 4
and who it was assigned to. I can do all that from my main spreadsheet

I just need to give a unique book number.

Matt

[rbpd5015]

nevermind got it can you mark this as SOLVED!

Thanks

Matt