auto numbering problem

S

Seeker

I intended to use whole 65536 rows in a spreadsheet for data keeping, Column
A & column B as record number.
When new record input, A+1 & B=1, when old record retrieves, A=original
number & B+1, which means numbers are duplicated. Example:
Column A Column B
1 (new record) 1
1 2 (record changed =1+1)
1 3 (record changed =2+1)
2 (new record =1+1) 1
2 2 (record changed =1+1)
3 (new record =2+1) 1
4 (new record =3+1)
I used array-frequency to auto-number new data entry in cell A1 to get the
number and copy-past special to put it in the next empty cell in column A, it
works but process time is too long, is there any way I can shorten the
process time?
Range("A1").FormulaArray = _

"=IF(B1=""ADD"",SUM(IF(FREQUENCY(IF(LEN(A2:A65536)>0,MATCH(A2:A65536,A2:A65536,0),""""),
IF(LEN(A2:A65536)>0,MATCH(A2:A65536,A2:A65536,0),""""))>0,1))+1&"".1"",""Input Order Number"")"

Tks in advance.
 
R

Rick Rothstein

Please don't multi-post your questions... it wastes the time of the
volunteers when they spend time developing an answer to a question that has
already been answered elsewhere. For example, this question... I've given
you an answer to it over in the excel.programming newsgroup.
 
S

Seeker

Hi Rick,
First of all, sorry about my duplicated on question as my IE shown "service
not avaliable" in my first attempt of posting question, so I really didnt
know the question was posted.
Secondly, my question may not be clear enough and becomes misleading. I am
not looking for the sub-number in column B right now.

What I need to do is :
find the next number in coulmn A (which is 5 in my example - count the
number of unique text and number values in cells A2:A65536).

Thanks again for your help.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top