Super slow table array formulas

[Dylan @ UAFC]

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

 

[Don Guillett]

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.

 

[Dylan @ UAFC]

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]

I don't think the separate file would help. In fact, it could make it worse.
As I said, can you break it up. I can take a look.

 

[Charles Williams]

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]

I will check out the site and see what I can do.

Are you saying that excel just might not do thejob, an d
we should look into a more web data base driven system
Like a serrious of SQL codes or something???

 

[Dylan @ UAFC]

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]

If your data (assume in column A) is sorted it is very simple and fast to
check for duplicates like this

=if(a2=a1,"Dup","")

and copy down.

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

 

[Dylan @ UAFC]

not the case here.
we are dealing with super master spreedsheet that is
distibuted out many way.
The date the phoen entry occoured is important.
However, no two phone entry can exist in the history of
the data entry progect.
The order is by date
the intergerty is by phone #
so unfor. the phoen # si really only random
data that is not usefull in any calcution purpose