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
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
