Excel sequential numbering

Latest Articles

Excel sequential numbering

Downsizer Forum Index -> IT Matters

Author

Message

tahir

Joined: 28 Oct 2004
Posts: 45440
Location: Essex

Posted: Wed Oct 16, 13 11:59 am Post subject: Excel sequential numbering
I need to generate numbers for some data in Excel; based on the column "SupplierCode" I want create a field "SupplierCode.xxxxxx" where the sequential number is generated on a per supplier basis. Any ideas? (Hope the above makes sense)

DorsetScott

Joined: 23 Oct 2011
Posts: 500
Location: Bournemouth

Posted: Wed Oct 16, 13 12:06 pm Post subject:
Do you mean you have 1 column with various supplier codes in it and you wish to create a new column to sequentially track that supplier EG where Letter is supplier code and number is the sequential number? A 1 B 1 C 1 A 2 D 1 B 2 A 3

tahir

Joined: 28 Oct 2004
Posts: 45440
Location: Essex

Posted: Wed Oct 16, 13 12:11 pm Post subject:
e.g. SuppCode MaterialRef A001 A001.000001 B001 B001.000001 C001 C001.000001 A001 A001.000002 A001 A001.000003 D001 D001.000001 So that the sequential part is tracked for each change in SupplierCode

DorsetScott

Joined: 23 Oct 2011
Posts: 500
Location: Bournemouth

Posted: Wed Oct 16, 13 12:29 pm Post subject:

I'll try to explain it, apologies if it's confusing or I over simplify (don't know your excel knowledge)

Assuming your supplier code is in column A, put this formula in a new column for material ref. (If your supplier code is in column M, just change the As to Ms.) Then copy it all the way down your new column, the references should change automatically

=A2&".0000"&(COUNTIF($A$1:A2,A2))

Breaking it down,
=A2 - will put the value in cell A2 (which is the supplier code)
&".0000" - will then add the text .0000 after the supplier code
&(COUNTIF($A$1:A2,A2)) - Will count from the first cell in the supplier code column to where you are how many times that supplier code has been use and put it at the end.

Give it a go and see if it does what you want from the explanation. I've tried it on my computer and it works, hopefully explaining it makes a little sense

tahir

Joined: 28 Oct 2004
Posts: 45440
Location: Essex

Posted: Wed Oct 16, 13 1:00 pm Post subject:
Scott that's brill, only issue appears to be when the number of digits increases: A019.00001 A019.00002 A019.00003 A019.00004 A019.00005 A019.00006 A019.00007 A019.00008 A019.00009 A019.000010

tahir

Joined: 28 Oct 2004
Posts: 45440
Location: Essex

Posted: Wed Oct 16, 13 1:22 pm Post subject:
I can get the sequential numbers now: =(COUNTIF($D$12,D2)) I can then get the number to display correctly with the leading zeros via a custom format but if I then use: =D2 & "." & BD2 I get A019.1 instead of A019.000001

DorsetScott

Joined: 23 Oct 2011
Posts: 500
Location: Bournemouth

Posted: Wed Oct 16, 13 2:15 pm Post subject:
A slight bodge would be to just change the number of zero's that are displaying as plain text at 10, 100, 1000, 10000 etc So change &".0000"& to &".000"& at 10 and &".00"& at 1000 I'll see if I can work out the decimal formatting quickly for you though when the boss isn't looking

tahir

Joined: 28 Oct 2004
Posts: 45440
Location: Essex

Posted: Wed Oct 16, 13 2:29 pm Post subject:
Thanks

tahir

Joined: 28 Oct 2004
Posts: 45440
Location: Essex

Posted: Wed Oct 16, 13 2:34 pm Post subject:
Got it: =D2 & "." & TEXT(BD2,"000000")

DorsetScott

Joined: 23 Oct 2011
Posts: 500
Location: Bournemouth

Posted: Wed Oct 16, 13 3:33 pm Post subject:
Beat me to it. I hate how work gets in the way!!!

tahir

Joined: 28 Oct 2004
Posts: 45440
Location: Essex

Posted: Wed Oct 16, 13 3:41 pm Post subject:
Couldn't have done it without your help though

DorsetScott

Joined: 23 Oct 2011
Posts: 500
Location: Bournemouth

Posted: Wed Oct 16, 13 4:00 pm Post subject:
147 posts in and I helped someone Pleasure

alison
Downsizer Moderator

Joined: 29 Oct 2004
Posts: 12918
Location: North Devon

Posted: Wed Oct 16, 13 5:28 pm Post subject:

DorsetScott wrote:

147 posts in and I helped someone

Pleasure

	Downsizer Forum Index -> IT Matters	All times are GMT
Page 1 of 1

View Latest Posts