S
Seeker
I have a spreadsheet which I intended to use whole length of 65536 rows for
data keeping.
Column A & column B to be used as record number of the data. IF existing
data be retreived and changed, data will be stored as new record while A
remains same and B will be +1. When entire new set of data input, the A +1
and B=1. So A has duplicate numbers. Example:
Column A Column B
1 1
1 2
2 1
2 2
2 3
3 1
I used Array - frequency which can do well in determine Column A sequential
number. but since my list has 65536 rows, it stall fora while to get the
answer.
Is there a speedy way in macro to do the same please?
My formula used as follow :
Range("F14").FormulaArray = _
"=IF(F2=""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"")"
data keeping.
Column A & column B to be used as record number of the data. IF existing
data be retreived and changed, data will be stored as new record while A
remains same and B will be +1. When entire new set of data input, the A +1
and B=1. So A has duplicate numbers. Example:
Column A Column B
1 1
1 2
2 1
2 2
2 3
3 1
I used Array - frequency which can do well in determine Column A sequential
number. but since my list has 65536 rows, it stall fora while to get the
answer.
Is there a speedy way in macro to do the same please?
My formula used as follow :
Range("F14").FormulaArray = _
"=IF(F2=""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"")"