AutoNumber Problem

M

meetarun007

New Member
Joined
Mar 20, 2010
Messages
14
Hi to all,

I got problem.Initially my excel sheet looks like this.


Excel Workbook
AE
9181PALLETIZING STOPPER-2 CYLINDER DOWN
9282PALLETIZING STOPPER-3 CYLINDER UP
9383PALLETIZING STOPPER-3 CYLINDER DOWN
94DESTACKER
9584STACK HOLDING CYLINDER-1 - EXTENDED
9685STACK HOLDING CYLINDER-1 - RETRACTED
9786STACK HOLDING CYLINDER-2 - EXTENDED
9887STACK HOLDING CYLINDER-2 - RETRACTED
9988PALLET DESTACKER MOTOR - FORWARD
10089PALLET DESTACKER MOTOR - REVERSE
10190PALLET DESTACKER MOTOR - SPEED
10291PALLET DESTACKER MOTOR - BRAKE
10392PALLET DESTACKER MOTOR MOVE - UP
10493PALLET DESTACKER MOTOR MOVE - DOWN
105ROLLER CONVEYOR -1
10694ROLLER CONVEYOR -1 MOTOR
107ROLLER CONVEYOR -2
10896ROLLER CONVEYOR -2 MOTOR - FORWARD
10997ROLLER CONVEYOR -2 MOTOR - REVERSE
11098ROLLER CONVEYOR -2 MOTOR - SPEED
11199ROLLER CONVEYOR -2 MOTOR - BRAKE
112101PALLET STOPPER-1 CYLINDER UP
113102PALLET STOPPER-1 CYLINDER DOWN
114103PALLET STOPPER-2 CYLINDER UP
115104PALLET STOPPER-2 CYLINDER DOWN
116105PALLET CENTERING CYLINDER -1 EXTENDED POSITION
117106PALLET CENTERING CYLINDER -1 RETRACTED POSITION
118107PALLET CENTERING CYLINDER -2 EXTENDED POSITION
119108PALLET CENTERING CYLINDER -2 RETRACTED POSITION
120ROLLER CONVEYOR -3
121109ROLLER CONVEYOR -3 MOTOR - FORWARD
122110ROLLER CONVEYOR -3 MOTOR - REVERSE
123111ROLLER CONVEYOR -3 MOTOR - SPEED
124PLYWOOD SHUTTLES
125112PLYWOOD SHEET TROLLEY MOTOR -1 - FORWARD
126113PLYWOOD SHEET TROLLEY MOTOR -1 - REVERSE
127114PLYWOOD SHEET TROLLEY MOTOR -1 - SPEED
128115PLYWOOD SHEET TROLLEY MOTOR -1 - BRAKE
129116PLYWOOD SHEET TROLLEY MOTOR -2 - FORWARD
130117PLYWOOD SHEET TROLLEY MOTOR -2 - REVERSE
Outputs


If i want to add item in between, i need to change the numbers after that.

Excel Workbook
AE
9181PALLETIZING STOPPER-2 CYLINDER DOWN
9282PALLETIZING STOPPER-3 CYLINDER UP
9383PALLETIZING STOPPER-3 CYLINDER DOWN
94DESTACKER
9584STACK HOLDING CYLINDER-1 - EXTENDED
9685STACK HOLDING CYLINDER-1 - RETRACTED
9786STACK HOLDING CYLINDER-2 - EXTENDED
9887STACK HOLDING CYLINDER-2 - RETRACTED
9988PALLET DESTACKER MOTOR - FORWARD
100NEW ITEM
10189PALLET DESTACKER MOTOR - REVERSE
10290PALLET DESTACKER MOTOR - SPEED
10391PALLET DESTACKER MOTOR - BRAKE
10492PALLET DESTACKER MOTOR MOVE - UP
10593PALLET DESTACKER MOTOR MOVE - DOWN
106ROLLER CONVEYOR -1
10794ROLLER CONVEYOR -1 MOTOR
108ROLLER CONVEYOR -2
10996ROLLER CONVEYOR -2 MOTOR - FORWARD
11097ROLLER CONVEYOR -2 MOTOR - REVERSE
11198ROLLER CONVEYOR -2 MOTOR - SPEED
11299ROLLER CONVEYOR -2 MOTOR - BRAKE
113101PALLET STOPPER-1 CYLINDER UP
114102PALLET STOPPER-1 CYLINDER DOWN
115103PALLET STOPPER-2 CYLINDER UP
116104PALLET STOPPER-2 CYLINDER DOWN
117105PALLET CENTERING CYLINDER -1 EXTENDED POSITION
118106PALLET CENTERING CYLINDER -1 RETRACTED POSITION
119107PALLET CENTERING CYLINDER -2 EXTENDED POSITION
120108PALLET CENTERING CYLINDER -2 RETRACTED POSITION
121ROLLER CONVEYOR -3
122109ROLLER CONVEYOR -3 MOTOR - FORWARD
123110ROLLER CONVEYOR -3 MOTOR - REVERSE
124111ROLLER CONVEYOR -3 MOTOR - SPEED
125PLYWOOD SHUTTLES
126112PLYWOOD SHEET TROLLEY MOTOR -1 - FORWARD
127113PLYWOOD SHEET TROLLEY MOTOR -1 - REVERSE
128114PLYWOOD SHEET TROLLEY MOTOR -1 - SPEED
129115PLYWOOD SHEET TROLLEY MOTOR -1 - BRAKE
130116PLYWOOD SHEET TROLLEY MOTOR -2 - FORWARD
131117PLYWOOD SHEET TROLLEY MOTOR -2 - REVERSE
Outputs


finally it look like this..

Excel Workbook
AE
9181PALLETIZING STOPPER-2 CYLINDER DOWN
9282PALLETIZING STOPPER-3 CYLINDER UP
9383PALLETIZING STOPPER-3 CYLINDER DOWN
94DESTACKER
9584STACK HOLDING CYLINDER-1 - EXTENDED
9685STACK HOLDING CYLINDER-1 - RETRACTED
9786STACK HOLDING CYLINDER-2 - EXTENDED
9887STACK HOLDING CYLINDER-2 - RETRACTED
9988PALLET DESTACKER MOTOR - FORWARD
10089NEW ITEM
10190PALLET DESTACKER MOTOR - REVERSE
10291PALLET DESTACKER MOTOR - SPEED
10392PALLET DESTACKER MOTOR - BRAKE
10493PALLET DESTACKER MOTOR MOVE - UP
10594PALLET DESTACKER MOTOR MOVE - DOWN
106ROLLER CONVEYOR -1
10795ROLLER CONVEYOR -1 MOTOR
108ROLLER CONVEYOR -2
10997ROLLER CONVEYOR -2 MOTOR - FORWARD
11098ROLLER CONVEYOR -2 MOTOR - REVERSE
11199ROLLER CONVEYOR -2 MOTOR - SPEED
112100ROLLER CONVEYOR -2 MOTOR - BRAKE
113101PALLET STOPPER-1 CYLINDER UP
114102PALLET STOPPER-1 CYLINDER DOWN
115103PALLET STOPPER-2 CYLINDER UP
116104PALLET STOPPER-2 CYLINDER DOWN
117105PALLET CENTERING CYLINDER -1 EXTENDED POSITION
118106PALLET CENTERING CYLINDER -1 RETRACTED POSITION
119107PALLET CENTERING CYLINDER -2 EXTENDED POSITION
120108PALLET CENTERING CYLINDER -2 RETRACTED POSITION
121ROLLER CONVEYOR -3
122109ROLLER CONVEYOR -3 MOTOR - FORWARD
123110ROLLER CONVEYOR -3 MOTOR - REVERSE
124111ROLLER CONVEYOR -3 MOTOR - SPEED
125PLYWOOD SHUTTLES
126112PLYWOOD SHEET TROLLEY MOTOR -1 - FORWARD
127113PLYWOOD SHEET TROLLEY MOTOR -1 - REVERSE
Outputs






Is there anyway to automate the numbering...

thanks in advance....
 
Last edited:

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Sort by date Sort by votes
I woulda sworn there was an interactive means of inserting rows or cells and get a popup menu that would ask about copying down formulas; but I'll be danged if I can remember it or re-discover it.

You could certainly just use the macro recorder to create a little macro and hotkey it so that you can just hit a key and voilá.
 
Upvote 0
Greg Truby said:
I woulda sworn there was an interactive means of inserting rows or cells and get a popup menu that would ask about copying down formulas; but I'll be danged if I can remember it or re-discover it.

You could certainly just use the macro recorder to create a little macro and hotkey it so that you can just hit a key and voilá.
Click to expand...

Thnx for the reply..
Since i'm new to macros, can you give me a sample macro for this so that i can modify that........
 
Upvote 0
You're better off creating your own sample macro (we'll get to that). First - which version of Excel are you using?
 
Upvote 0
Welcome to the Board!

Unfortunately, you don't have a very friendly format there. By having header rows like that you lose the ability to do quite a bit. Can you add a column for each master category?

Maybe something like this:
Excel Workbook
ABC
1Item #Master CategorySub-Category
284DESTACKERSTACK HOLDING CYLINDER-1 - EXTENDED
385DESTACKERSTACK HOLDING CYLINDER-1 - RETRACTED
486DESTACKERSTACK HOLDING CYLINDER-2 - EXTENDED
587DESTACKERSTACK HOLDING CYLINDER-2 - RETRACTED
688DESTACKERPALLET DESTACKER MOTOR - FORWARD
789PALLET MOTORNEW ITEM
890PALLET MOTORPALLET DESTACKER MOTOR - REVERSE
991PALLET MOTORPALLET DESTACKER MOTOR - SPEED
1092PALLET MOTORPALLET DESTACKER MOTOR - BRAKE
1193PALLET MOTORPALLET DESTACKER MOTOR MOVE - UP
1294PALLET MOTORPALLET DESTACKER MOTOR MOVE - DOWN
Sheet1
Excel 2010
 
Upvote 0
Smitty said:
Welcome to the Board!

Unfortunately, you don't have a very friendly format there. By having header rows like that you lose the ability to do quite a bit. Can you add a column for each master category?

Maybe something like this:

Excel Workbook
ABC
1Item #Master CategorySub-Category
284DESTACKERSTACK HOLDING CYLINDER-1 - EXTENDED
385DESTACKERSTACK HOLDING CYLINDER-1 - RETRACTED
486DESTACKERSTACK HOLDING CYLINDER-2 - EXTENDED
587DESTACKERSTACK HOLDING CYLINDER-2 - RETRACTED
688DESTACKERPALLET DESTACKER MOTOR - FORWARD
789PALLET MOTORNEW ITEM
890PALLET MOTORPALLET DESTACKER MOTOR - REVERSE
991PALLET MOTORPALLET DESTACKER MOTOR - SPEED
1092PALLET MOTORPALLET DESTACKER MOTOR - BRAKE
1193PALLET MOTORPALLET DESTACKER MOTOR MOVE - UP
1294PALLET MOTORPALLET DESTACKER MOTOR MOVE - DOWN
Sheet1
Excel 2010
Click to expand...
Yeah i ad add...
If im adding can i able to do that... Yes means how?
thanks in advance..
 
Upvote 0
I'm not sure I understand the question. If you add a column and eliminate the gaps in your data, sequential numbering shouldn't be a problem.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Creating a Text Sentence using a number of Variables in Excel VBA
Replies
3
Views
143
rlv01
R
V
  • Question
Create parent-child set
Replies
0
Views
246
vipett
V
G
  • Question
Fuzzy Match code
Replies
1
Views
2K
gelu
G
B
  • Question
Keeping formulas on second sheet accurate after making changes in the first sheet
Replies
8
Views
206
Fluff
Fluff
J
  • Question
Formula to find date and return column number
Replies
2
Views
173
jc352
J

Forum statistics

Threads
1,215,459
Messages
6,124,948
Members
449,198
Latest member
MhammadishaqKhan

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