New Incremental ID Column Required

[Brutality]

Hi there,

I know I can do the following manually using multiple sort fields and drag and drop, but wondered if there was any way to automate the following request:

I have a spreadsheet with thousands of entries for a Car rally, and wish to allot parking IDs using a new incremental column. The new ParkingID criteria is based on the type of rally the entrant is in, and whether or not they are a driver; there is a driver column with all drivers being given a Y (as some entries are navigators) and there are 3 rally types - short, medium, and long. I want to start a new column with the first short rally entrant that is a driver being given the ID 1000, the next short rally entrant driver would then be 1001. The first medium entrant driver ID is 2000, the next medium entrant driver is 2001. The first long entrant driver is 3000 and so on.

As I said initially, I could use 2 or 3 sorts to do this and simply drag using a visual cue but that's too easy - yet I have no clue how to automate this process. Any ideas?

TIA
Mark

 

[mole999]

not sure i understand the question

what sort of ID are you looking for

Y2000
Y2001
Y1000
Y1001
Y3000
Y3001

all seem easy

do you have other fields that says driver etc, and others that give their types

 

[Brutality]

Yes, sorry I wasn't clear. Here's a sample of the sheet and what I want. I've used commas to separate the fields so hope that is ok.

Headings are:

Name, Driver, Navigator, Run Type, ParkID
-----------------------------------------
Joe, Y, N, Medium, 2000
James, Y, N, Short, 1000
Mark, N, Y, Medium,
Flo, Y, N, Medium, 2001
Henry, Y, N, Short, 1001
Harry, Y, N, Long, 3000
Lenore, Y, N, Long, 3001
Barry, N, Y, Long,
Frank, Y, N, Medium, 2002

There are other fields but only the driver and run type are necessary to generate the required Park ID, and I have included an example of what I am after. Note I don't wish to assign a Park ID to navigators.

HTH
Mark

 

[shg]

       --A--- ---B--- ----C---- ----D---- ---E---
   1    Name   Driver Navigator  Run Type  ParkID
   2   Joe       Y        N     Medium       2000
   3   James     Y        N     Short        1000
   4   Mark      N        Y     Medium           
   5   Flo       Y        N     Medium       2002
   6   Henry     Y        N     Short        1001
   7   Harry     Y        N     Long         3000
   8   Lenore    Y        N     Long         3001
   9   Barry     N        Y     Long             
  10   Frank     Y        N     Medium       2003

The formula in E2 and down is

=IF(B2<>"Y", "", COUNTIF(D$1:D1, D2) + LOOKUP(D2, {"Long","Medium","Short"}, {3000,2000,1000}))

 

[Brutality]

Tremendous! Thanks a lot SHG.

 

[shg]

Just don't change anything in the table; if you do, everyone below will have run out and move their cars ...