Need Help in numbering

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"")"
 
R

Rick Rothstein

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)
 

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