New Incremental ID Column Required

Brutality

Brutality

New Member
Joined
Feb 5, 2003
Messages
44
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
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Sort by date Sort by votes
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
 
Upvote 0
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
 
Upvote 0
Code: 
       --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}))
 
Upvote 0
Just don't change anything in the table; if you do, everyone below will have run out and move their cars ...
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Show/Hide Column for Duplicate Order IDs
Replies
3
Views
314
marcocm4
M
J
  • Solved
VBA to have user select a cell in column A have a 1 placed as a result of selection and a 2 placed in the next selected, etc
2
Replies
15
Views
451
NoSparks
N
S
  • Question
VBA Inventory UserForm ID generation
Replies
1
Views
697
Herakles
H
A
  • Solved
Comparing two excel files for matching / mismatching rows (long text strings - match() wont work
Replies
4
Views
554
ahaye
A
Ashe
  • Solved
Help matching multiple permissions in columns
Replies
6
Views
276
Fluff
Fluff

Forum statistics

Threads
1,214,864
Messages
6,121,986
Members
449,058
Latest member
oculus

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top