• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

CREATING AUTONUMBERING IN COMBINED DATA

V

VDS

Member
I Have an excel file containing following data : -


SCS/0185/11-12
SCS/0186/11-12
SCS/0187/11-12
SCS/0278/12-13

SCS is the company name, 0185 = Unique no and 11-12 is the Financial year. Daily basis entry is to be posted for making despatch instructions to stores dept. For a new entry, entire row is to be pasted fresh and correct the srl nos. Every year around 400 nos sales order is prepared. Pls suggest any command / function to enter the complete data without changing the sr no manually. When I tried to insert the row before the column, for autonumbering/Fill Series, it shows cannot shift objects offsheet.


VDS
 
Hi VDS,
You haven't told us how you get the serial numbers. But here is an example:
Code: 
="SCS/"&TEXT(ROW(),"0000")&"/12-13"
 
Xiq said:
Hi VDS,
You haven't told us how you get the serial numbers. But here is an example:
Code: 
="SCS/"&TEXT(ROW(),"0000")&"/12-13"
Click to expand...

Hai XIQ,
Serial numbers are typed from "001 onwards" till end of financial year when transactions come to close.
My query is that by doing so, there is chance of duplicacy. It should be avoided.

VDS
 
Hi VDS,

You can try the following formula, placed in A2 and array entered (ctrl + shift + enter) then copy down:
Code: 
="SCS/"&TEXT(MAX(--IFERROR(MID(A$1:A1,FIND("/",A$1:A1)+1,4),0))+1,"0000")&"/12-13"
 
Another way of doing this, is by using a dedicated column for the serial number and a second column to combine the number with some text-strings. See attached file for an example.
 

Attachments

  • ExampleB.xlsx
    10.9 KB · Views: 10
Xiq- That's the first time I've seen the =MAX($A$1:$A1)+1 used to start numbering a list. I really like that! Thanks for sharing!!
 
Hi Xiq, I know this is a silly question. :(

But will using "=row(a1)" give us same result as to "=MAX($A$1:$A1)+1"

If you get time, could you please guide what is advantage of using =MAX($A$1:$A1)+1 v/s row(a1).
My understanding of your formula is, for genareting serial numbers.

Have a nice day ahead.
 
sachinbizboy said:
But will using "=row(a1)" give us same result as to "=MAX($A$1:$A1)+1"
Click to expand...

No real advantage in this case, it maybe a little bit less foolproof when somebody decides to enter a manual serial number somewhere in between.
If you, on the other hand, don't want to start by 1 but e.g. 200 ... you then have to edit the function to =ROW(A1)+199 for each row.
 
You can also do following. Apply following custom formatting to the cell using
Format Cells | Custom and then
"SCS/"0000"/11-12"

and then the formula would simply be:
=MAX($A$1:A1)+1

Edit:= Small mistake in the formula. Thanks to Sachin!!
 
Xiq said:
Hi VDS,

You can try the following formula, placed in A2 and array entered (ctrl + shift + enter) then copy down:
Code: 
="SCS/"&TEXT(MAX(--IFERROR(MID(A$1:A1,FIND("/",A$1:A1)+1,4),0))+1,"0000")&"/12-13"
Click to expand...

Hi XIQ, this formula seems bit difficult. could u pls streamline?.

VDS
 
VDS said:
Hi XIQ, this formula seems bit difficult. could u pls streamline?.

VDS
Click to expand...

Hi VDS,

You have been given ample solutions in this thread. From very easy, to (the one you quoted) a little bit more exotic formula's. So... if you want easy solutions, what is wrong with the ones you have been given? Have you checked the uploaded files?
 
Xiq said:
Hi VDS,

You have been given ample solutions in this thread. From very easy, to (the one you quoted) a little bit more exotic formula's. So... if you want easy solutions, what is wrong with the ones you have been given? Have you checked the uploaded files?
Click to expand...

@XIQ: I am having Excel 2003. Other functions are working nicely. That's why little curious about it.
Thanks a lot.

VDS
 
Ah, IFERROR will not work in 2003. You have to modify it to use:
Code: 
="SCS/"&TEXT(MAX(--IF(ISERROR(MID(A$1:A1,FIND("/",A$1:A1)+1,4)),0,MID(A$1:A1,FIND("/",A$1:A1)+1,4)))+1,"0000")&"/12-13"
 
Hi VDS,
If by "streamline" you meant "explain"... then here we go:


First of all, the "and"-symbols combine two pieces of formula/text together. In my formula it looks like this:
"SCS/" & piece of formulaA & "/12-13"

The piece of formulaA generates a number and formats it as text. I want this number to always have 4 digits, I do this with the TEXT function and give the format "0000". Like this:
"SCS/"& ... TEXT( piece of formulaB , "0000" ) ... &"/12-13"

The piece of formulaB is basically looking for the largest number in all the cells above via piece of formulaC, then it adds 1 to it.
"SCS/"& TEXT(...MAX( piece of formulaC ) +1 ... , "0000" ) &"/12-13"


The piece of formulaC makes sure the extracted numbers by piece of formulaD always returns a number. I do this by using an IFERROR function: if it finds and error in piece of formulaD, then return zero. Also, piece of formulaD returns as text... so I need to convert it to numbers for the previous part of the formula. I do this by adding the double minus sign "--".
="SCS/"&TEXT(MAX( ... --IFERROR( piece of formulaD ,0) ... )+1,"0000")&"/12-13"

Now piece of formulaD is one of the reasons why you have to enter as array. It extracts 4 digit from every cell above. The piece of formulaE tells the MID function from what position it should start extracting.
="SCS/"&TEXT(MAX( --IFERROR( ... MID( A$1:A1 , piece of formulaE , 4 ) ... ,0) )+1,"0000")&"/12-13"

And last, piece of formulaE. Also a reason to enter as array. I use the FIND function to search for the first slash-symbol "/" in the text-string in all the cells above, then I add 1... now you have the position for the previous part of the formula.
="SCS/"&TEXT(MAX( --IFERROR(MID( ... FIND( "/" , A$1:A1) +1 ... , 4 ),0) )+1,"0000")&"/12-13"


And there you have it. It is a bit exotic, because I made the assumption of a more all round use.
 
shrivallabha said:
Ah, IFERROR will not work in 2003. You have to modify it to use:
Code: 
="SCS/"&TEXT(MAX(--IF(ISERROR(MID(A$1:A1,FIND("/",A$1:A1)+1,4)),0,MID(A$1:A1,FIND("/",A$1:A1)+1,4)))+1,"0000")&"/12-13"
Click to expand...

Oh.... that would make sense...
 
Xiq said:
Oh.... that would make sense...
Click to expand...

@ shrivallabha, Now this too is good. NICE REPLY
@ XIQ. Thanks for support.

I have a query, How many columns can be inserted in worksheet? is it differs in 2003, 2007 & 2010.

VDS
 
No Of Column or Rows are fixed, you cant insert & Delete No Of Column or Rows.
Logically Inserting New Column, means, Delete some Non Used Columns from end side, and pulling them in the front or Inserted area.

Try, by putting 1 in all column (1 to End of sheet vertically IV1, in case of 2003 + ), and try to insert a new Column.. Will fail,

Now Just delete content, from the last cell IV1, and insert a new Column anywhere.. you can see.. content of IU1 has shifted to IV1, and a new row has been inserted.

For 2003 & -:
Worksheet size 65,536 rows by 256 columns
In short 2^16 & 2^8

For 2007 & +:
Worksheet size 1,048,576 rows by 16,384 columns
In short 2^20 & 2^14

PS: ^ denote as ToThePower
PPS:
For Numeric view, you can..
  • Go To VBA (Alt + F11)
  • Go to Immediate Window (Ctrl + G)
  • Write Below code
    • Application.ReferenceStyle = xlR1C1
Now come back to Sheet (Alt + F11) again.. and view the Column Header, Its changed to 1,2,...256 instead of A,B,...IV.

CAUTION:
Dont forget to roll back to Application.ReferenceStyle = xlA1, Many Excel Experts also has hesitation to work R1C1 style..

Believe me ..
=MAX(R1C1:R[-7]C)+1 is same as =MAX($A$1:A1)+1

  • Go To VBA (Alt + F11)
  • Go to Immediate Window (Ctrl + G)
  • Write Below code
    • Application.ReferenceStyle = xlA1
 
Deb,

Do you mean the forum admin 'R1C1' is also '$A$1' ;)

In 2003, I think the native option was in Tools | Options and then in there, one of the many tabs had this reference style checkbox.

In 2007, it is Office Button | Formulas | R1C1 reference Style checkbox.
 
Believe me ..
=MAX(R1C1:R[-7]C)+1 is same as =MAX($A$1:A1)+1
Click to expand...
Hi Deb ,

Not necessarily !

That is the problem with the R1C1 style of referencing ; a formula can mean anything depending on where the cursor is when the formula is typed in.

Put the above formula ( =MAX($A$1:A1)+1 ) in several cells , say C4 , C5 ,.C6 , J17 , L1 and so on ; toggle the display to R1C1 style of referencing , and see what is displayed.

Narayan
 
NARAYANK991 said:
Hi Deb ,

Not necessarily !

That is the problem with the R1C1 style of referencing ; a formula can mean anything depending on where the cursor is when the formula is typed in.

Put the above formula ( =MAX($A$1:A1)+1 ) in several cells , say C4 , C5 ,.C6 , J17 , L1 and so on ; toggle the display to R1C1 style of referencing , and see what is displayed.

Narayan
Click to expand...
Do not agree completely.

R1C1 is as absolute as $A$1. No matter where you write it.
R[0]C[1] Or RC[1] can be anything depending on the current cell.

The square brackets are good enough pointers in my opinion.
 
Hi Deb ,

Instead of the word many , I think it should be all !

The only place where R1C1 comes in useful seems to be when INDIRECT is used ( Sajan has developed this into a fine art ! ) or in VBA.

Narayan
 
shrivallabha said:
Do not agree completely.

R1C1 is as absolute as $A$1. No matter where you write it.
R[0]C[1] Or RC[1] can be anything depending on the current cell.

The square brackets are good enough pointers in my opinion.
Click to expand...
Hi shrivallabha ,

I think I was talking about the entire formula ; if you put in the entire formula in various cells and toggle to R1C1 style of referencing , R1C1 will be the same since it is absolute addressing , but since A1 has been used in a relative style of addressing , it will depend on where you are entering the formula. This is what I wanted to point out.

Narayan
 
You must log in or register to reply here.
Back
Top