Would I be better off to use Access in this cases.

I would say yes in this case.

--
Biff
Microsoft Excel MVP


"markmcd" wrote in message
...
Yes that's right 300k rows. It does take forever. During this time I do
get
Excel not responding but have found if I leave it go rather than killing
it,
it eventually comes good and you can carry on.

By August YTD it is 300k rows and my concern as the year goes on is just
how
big will this be by the end of December. I'm thinking I could be better
off
using MS Access but don't know the program that well. Would I be better
off
to use Access in this cases. (Microsoft Office 2007 in use)

"T. Valko" wrote:

ie my spreadsheet has over 300,000 lines
COUNTIF(A$2:A2,A2)

Using an ever progressing range formula like that on 300k rows might be a
killer!

By the time you get to the last row you've referenced 45,000,150,000
cells!

=SUMPRODUCT(ROW(1:300000))

1+2+3+4+5+6+7.....+300000

Plus, you're doing it twice:

IF(COUNTIF(A$2:A2,A2)1,"",COUNTIF(A$2:A2,A2)))

A generic formula to count unique numbers:

=COUNT(1/FREQUENCY(range,range))

This will also be a killer on 300k rows. For "just" 100,000 rows it
caused
my version of Excel 2007 to lock-up ("not respond").

--
Biff
Microsoft Excel MVP


"Max" wrote in message
...
On the face of it, the earlier formula for col B (Uniq) should remain
unaffected by any sorting done (on the table) in other cols - that's
the
key
issue you raised. The Trip col's uniqueness is a presumption, taken as
you
had stated in your original post. You might need to re-examine whether
your
concat idea/formula is sufficient to render this field unique.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,100 Files:360 Subscribers:56
xdemechanik
---
"markmcd" wrote:
Thanks Max.....if I do a sort say by product or by rate as an example
(other
fields in the table), won't this cause havoc with the trip count. The
column
I imagine then should have contigious data in the trip column whether
newly
created or otherwise.

Once again thanks for your assistance.

Cheers Mark.