Also Charles, the biggest perfomacne issue I had was
When I incerted the =if(countif( fromula to check for
duplicate data entry, only in a one column range e1:e25000.
but this made a huge perfmance ajustment.
any suggestions there

"Charles Williams" wrote:

Yes, you really should be using a proper database.

But if you want to use Excel you should sort your table array so that you
can use VLOOKUP(lookupvalue,TableArray,Column,True)
See http://www.decisionmodels.com/optspeede.htm for examples of how to do
this when you may have missing values.
This will probably be about 10000 times faster.

Splitting the table array into a separate file will be slower, if it works
at all.


Charles
___________________________________
London Excel Users Conference April 1-2
The Excel Calculation Site
http://www.decisionmodels.com

"Dylan @ UAFC" wrote in message
...
sure I can send.
Its a huge file I could email.
The basics is,
The data is driven off a unique phone #.
Wich is the table array. It had to done because
we really have to many sloppy data entry and
no speed.
So I wanted it to popluate the
Address, city state zip county, name ect.
1) do you think the speed would be higher
If i had the table array as a seperate file and
not just on sheet2, 500,000 row and need to built to
add additional data
2) I have the formul set to
=IF(ISNA(VLOOKUP(G4,Sheet1!$A$1:$J$500000,3,FALSE) ),"",VLOOKUP(G4,Sheet1!$A$1:$J$500000,3,FALSE))
because I hate those #n/A's
I did notice this was a tremdous accomplishmen for the computer to copy
down, wich took 25 min.


The next issue I was having was duplicate data entry
wich I then used the formula
=IF(COUNTIF($G$4:$G$25000,G6)1,"FALSE"," TRUE")
I needed it to render Treu is in fact the data was clean and
new and then render False is the appointment was in fact a dup.
If you could show me how this formula would leave the cell
empty until data is typed in the corrisoponding cell, that
would great. I have tried a # of way/

In sheet one there is another table array formula hidden in
sheet one, but the range is literally r4:s12. that could not
be putting to much strain.
Any suggestions.

"Don Guillett" wrote:

Can you break up your ranges into smaller groups. I had to correct one
for a
client recently where he was using a VLookup for 65000 rows. If desired,
I
will take a look if you send to my address below.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Dylan @ UAFC" wrote in message
...
I have a data entry sheet were
sheet 1 has the table array fromulas
built down 25000 and accross about 12 columns.
The table is about 500,000 rows of 12 colums.

It was running ok, very sluggish)
I have 2.888 G processor on the computer.
But I stuck in a
=if(countif(
formula to check for duplicates.
Now this sheets is running so slow
it is almost unuseabel.
any sujjestions