Formula's not up to the job?

[amtrad]

Hi All

Could anyone help?

I have within a single spreadsheet tab 1 which is effectively many to one data - stock codes which are all unique in column A matched to a description of that stock in column B (column B has many stock descriptions which are exactly the same). This list goes to circa 140,000 lines.

In tab 2, 3 and 4 I have 3 respective supplier lists which have stock codes, descriptions and prices. The descriptions are all very different but it is the order codes that can be matched to tab 1 column A.

In tab 5 via a pivot table cut and paste special I have extracted unique values of stock codes and descriptions from tab 1. In this tab the idea is to have the 3 supplier prices against the 7000 or so unique products.

I have matched the supplier stock codes in tab 1 to effectively give me every match for 3 suppliers (circa 5000 lines per supplier for the 140,000 lines) and then done another formula to transfer this to tab 5 so the output is neat and clear for unique values only (formula to extract values from tab1). This has been done by using vlookups, sumifs and other such formula.

My problem is that Excel 2010 on a new PC doesn't seem to cope very well with this. It takes a lot of time to process and when I try to change it hangs and gets me a '...not responding' message.

I have tried pivot tables and also the power pivot with create relations link but as my data has duplicates I can't get this to work.

This works with formula which is what I know (I don't know VBA and admit to knowing little about Pivot tables), should excel get stuck with this much data using these type of formula? The file is 17mb.

It's getting quite frustrating - I have tried to design the sheet simply and with minimal formula - I struggle to simplify it. Could anyone offer any advice/help?

regards

Amtrad
<!-- / message -->

 

[dcardno]

I don't understand some of what you have said:
In tab 1 you have stock codes which are all unique in column A... This list goes to circa 140,000 lines In my mind, this implies 140K unique stock items.
In tab 5 you have unique values of stock codes and descriptions from tab 1...the idea is to have the 3 supplier prices against the 7000 or so unique products. There is a disconnect between 140K unique stock items and 7K unique products.

Anyway...
I don't think you need a pivot table (and I can't quite envision how you have applied one - but that's my limitation): if the supplier tabs contain unique stock codes, you can return the price data from each one with a vlookup formula, using the order code on tab1 as the value to be looked up and the relevant supplier tab (or active part of the tab) as the table array to be searched - you could return that value in each line, with each supplier in a separate column.

Excel should be able to handle this volume of data - I have just checked one of my larger sheets, and it contains 130,000 data points (about 21MB) and it seems fine under XL 2003 and 2007. Pivot tables are great, but they are a resource drain, and that may be part of your problem. With this volume of data, I would also investigate a database solution.

 

[amtrad]

Hi Dean

Thanks for the reply.

The stock codes are all unique but the descriptions are not - for example there are 20 stock codes for the same stock description as 20 different suppliers have 20 different ways to map that stock item in their database. HTH.

I have already got the Vlookup to work but when I make a change it freezes and given the use to which I want to put the application I was wondering whether I had the optimal solution or whether there was something else I could do to improve performance.

I tend to agree with you about database solution but I don't know about Access and I need a few weeks to learn - time I don't have but probably need to factor in for this and future issues?

Thanks for your response - any further help appreciated.

 

[dcardno]

How are you using a pivot table in this application? As I said, they are significant resource drains; justified when required, but if you don't need it they are slowing things down.

I have already got the Vlookup to work but when I make a change it freezes

What sort of change are you making, and to what data?

 

[amtrad]

Hi Dean

I update the suppliers list on a monthly basis as the prices change on a monthly basis. This is work in progress but it seems to slow and unstable as it freezes constantly - hence the need for rationalisation of data or alternative solution I guess?