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.

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.

--
Rick (MVP - Excel)


"Seeker" wrote in message
...
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.

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.

"Rick Rothstein" wrote:

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.

--
Rick (MVP - Excel)


"Seeker" wrote in message
...
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.