Excel Template becomes slow after adding 8000+ Supplier names

what formulas and conditional formatting are you applying

mole999 said:

what formulas and conditional formatting are you applying

Click to expand...

First and Foremost

1. I changed the processing speed above Normal via Task Manager

2. Tried Installing Fast Excel and truely it was like working in a Dream Excel Sheet, but cannot afford it as of now, therefore started to look for permanent solutions.

3. There were sheets which were spread out for sorting and finding duplicates. Remove those Sheets and got all Supplier info (Updating ones and the Existing list) under one sheet.

4. There are small Vlookup ranges ex : 25x4 cell Range sets going up to 500 (Once 500 Customer info is entered, we backup the information and start fresh) which means per Customer I use 100 Cells as a 25 Cell as Rows and 4 Colomns to sort the input Data as I wish, to 500 Customers..

5. Then there are 4 UDF's entered to get some specific work done in Sheet 1.

6. Helper colomns upto like 4-5.

7. I did many settings in Advanced options which were recommended by Microsoft official site, except for manual mode as I cannot let the calculation go manual because we cannot let the customers get duplicated. And it takes less than a minute to post one customer information as its a copy pasting Job.

8. The Criminal is Me, my lack of knowledge in making formulas efficient and the processing of Data. Because before the Supplier Data base was brought in everything was fine.

9. Removed all conditional formatting which makes the page nicer. and I try tackling the issue through Countif and Sumifs...

so seeing some of your formulas to see if they are optimal, a small analysis of what they do might be useful

helper columns maybe coded into a formula so that you reduce the number of formulas

not sure changing processing priority is as good as it seems

the copy paste routine of the customer might be simplified ?

mole999 said:

so seeing some of your formulas to see if they are optimal, a small analysis of what they do might be useful

Click to expand...

1. What exactly we do - We mine Data from Supplier Websites, as per the requirement of a respective Customer, to help them get the best prices for Bulk Orders.

2. We copy a set of Data and post it in a specific area of the Excel. From that we sort the Data via formulas as per our requirement.

I have created a sample, but attach option is disabled at my end to attach a sample. Is there any way I could send the file? it is only 1.7mb and its the Template with an example.

3. The Moment you delete the supplier list which is over 8000+ names, you'd notice the excel works just as Fine.

Thanks.

Is there any way I could send the file? it is only 1.7mb and its the Template with an example.

Click to expand...

Upload it to a free file hosting site like www.box.com or www.dropbox.com, mark it for sharing and post the link it provides in the thread.

Just be aware that a fair few members either can't or won't download files and so the number of people looking at it will be limited.

MARK858 said:

Upload it to a free file hosting site

Click to expand...

There will be no need to upload file Bro, Thank God, I have figured what causes the issue...unfortunately not the solution yet. The issue is this,

In my Sheet 3 as I mentioned there is 2 Lists of our Suppliers.

List 1. The Existing Suppliers
List 2. The Active Day Supplier List which gets updated.

There are few employees who constantly run into an issue of Conditional Formatting either slowing down the File or Formatting Doesn't work

Therefore I did something like this,

A1:A500 = Supplier Name's which gets Updated as Active Day processing
A501:A25000 = A list of 8000-15000 (depending Supplier Category) we have our complete Supplier List.

Now to track the Duplicates, I created C1:C25000 Mirror List by adding C1=A1

Then, B1 has the below Formula,

As some parts of the List comes with 0's and Blank Cells..

=IF((OR(A1=0,A1="")),0,COUNTIF($A$1:$A$25500,C1))

Therefore I get value 1 if the Supplier Name is not duplicated and I get 1+ figure if the Supplier name gets Duplicated.

Drawing this No. 1 or 1+ I have added a IF message to appear in the Sheet 1 stating "Duplicate Records Found"

Now just to give it a shot I deleted the whole B and C colomn and the Sheet WORKS JUST AS FINE AND SMOOTH IT WAS...

Now my issue, what are the alternative ways to code a COUNTIF where I would get 1 or 1+ if supplier name stands alone or have duplicated respectively?

Thank you,

What do you do with the 1 or +1 (or the "Duplicate Records Found") once you have it?

MARK858 said:

What do you do with the 1 or +1 (or the "Duplicate Records Found") once you have it?

Click to expand...

It's used like this Bro,

At the End of the Table, I sum the 1s of Colomn B : This gives the Exact Numbers of the Customers we have as for the Active Day. Because Active Day Customer Total will be the Number of Customers for the Day plus the Existing Customers...

That I minus from the Total Number of Customers in A Colomn. Which becomes 0 as a Result. Only when a Supplier/Customer gets duplicated the value goes Higher than 0,

So in Sheet 1 I have IF=(Sheet2!A25501>0,"Duplicates Available","")

Everyday the List we get is already sorted and mess free regards to customer Names, except for few 0s or Blank cells...

The issue we come across of duplicating the Suppliers as and when we enter Data in Sheet 1, Therefore as soon as the Agent enters the company name only, they see the message which pops up right on the Top stating Duplicates Available. Then they Skip that customer and go to the Next.

MARK858 said:

What do you do with the 1 or +1 (or the "Duplicate Records Found") once you have it?

Click to expand...

Is there a possibility of something like this? My suggestion is a UDF at this point. Which will really be helpful than a Macro for me.

1. UDF to do the very same of what a COUNTIF does...

2. UDF to Count all the 'Character cells' (1-9 & A-Z & or any other special character) of a respective colomn. So that Companies like ex: Ranna-1, Miami-5, Game Zone-2 customers will not get left out. (except for blank cells and cells which contains 0 Only or just some random numbers (0-9 Only)

By that I can do the very same thing I am doing now...