Need Help in numbering

  • Thread starter Thread starter Seeker
  • Start date Start date
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"")"
 
Forget using a macro... if I understand what you are doing correctly, then
your formula is much more complicated than needed. Assuming your data starts
in Row 2, put this formula in B2 and copy it down...

=COUNTIF(A$2:A2,A2)
 
Back
Top