Is upgrading to Office 2013 Pro the answer to my memory problems?

masplin

Active Member
Joined
May 10, 2010
Messages
413
Hi

I have 64-bit 16GB Ram machine that is constantly running out of memory on a big messy project I'm working on. The underlying data is coming form software written in Pascal and it a right told mess!!! i am having to do an enormous amount of filter calculation which I assume where the problem lies. I have read that the new Office 2013 is a massive improvement specifically in this area.

I was thinking I had to buy a much more powerful PC, but sounds like I should be trying the Office 2013 version first. I understand as an at home consultant this is going to be tricky as i have no interest in paying £10 a month for Office 365. So the question is could I go down the volume licensing route mentioned in another post even though I am based in the UK? Has anything changed on how a lowly individual can get hold of this?

Thanks for any advice

Mike
 
Ah technical question!!! Can I do that within the powerpivot import as I have no access ot the SQL tables and have failed miserably to get the sfotware company to get the data in the format I need. We are a small cusotmer of theirs and they have said they will do it, but who knows when.
 
Upvote 0

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
If you have 2 tables called TableA and TableB your query could look something like:

Code:
select
columnA,
columnB,
columnC,
columnD

from dbo.TableA

Union

select
columnA,
columnB,
columnC,
columnD

from dbo.TableB

As long as the 2 queries have the same number of columns and each column has the same data then this construct should work. You could potentially introduce duplicates unintentional duplicates which you could avoid using two fields concatenated together as a key. Also worth looking up the difference between UNION and UNION ALL.

Hope this helps
Jacob
 
Upvote 0
Unfortunately what I have is one table with 6 bits of data I need and another table with 12 bits of different data. The common link between them is they both have invoice numbers. So I'm having to do a calculate values where invoice match type lookup to get the data from table 1 into table 2.
 
Upvote 0
OK - so I take from this you are lacking the SQL know how to pull the 2 together. Depending on how your tables are set out this could well be extraordinarily simple!!

If you can post the table names and required columns I can write the query for you.

Jacob
 
Upvote 0
Hi Jacob

yes sorry I'm completely untrianed and making this up as I go along!!! All I have worked out is how to connect to the DB and select a prexisting table. Does this mena I will lose all the work I've already done building measures based on the tabled pulled straight down?
 
Upvote 0
Not necessarily in that your new joined table will have all the same columns that your previous one did just with the extra info from table 2 integrated.
 
Upvote 0
Hi Jacob

So the main table i'm using is called dbo.VW_InOutStock. Columns are:
Depot
Sysdate
customer
CTSalut
CTfirstN
CTSurname
CTFullname
Name1
ADDR11
ADDR12
ADDR13
ADDR14
PCODE1
PHONE1
CTPHONE
CTMOBILE
CTEMAIL
Group
INVNUM
INVDATE
STCODE
PRDGRP
SUMCODE
DESCRIPN
SALESN
STOCKN
VATTYPE
VATPC
QUANTITY
SELLPRICE
LINETOTAL
COST
LINECOST


The 2nd table I need to add is called custom.report3full. I'm not sure this is an sql tablns i need are
RType
TType
Description
RegNum
DVMake
MOTDue
ServiceDue

This table also contains the InvNum and all the above fields are duplicated in the multiple lines that make up one invoice. so I have been using a calculate(values(2nd table column),filter(2nd table, 1st table[invnum]=2ndtable[invnum]) to get them inot the first table as new columns.

I'm guess I go to existing connections and go to "write an sql query"

one hitch the 2nd table is updated every day, but the first table I got done once for me and have never been able ot get thme ot update it. It therefore is shorted and ends on 31/1/2013. Does this knacker up any union?

Thanks for your hlep

Mike
 
Upvote 0
Mike, a Union is basically where you join two identically structured data sets 'vertically' (i.e. one on top of the other). What seems to be required here is a simple join - the fact that one of your tables is stale isn't an issue as we can use a left join to ensure that we get every thing from the main table but you will obviously get some blanks for records where there is no data in the second table. What I don't see is a common column but assuming its invoice number as you mentioned earlier a query could look like:

Code:
select

[COLOR=#574123]IO.Depot,[/COLOR]
[COLOR=#574123]IO.[/COLOR][COLOR=#574123]Sysdate,[/COLOR]
[COLOR=#574123]IO.[/COLOR][COLOR=#574123]customer,[/COLOR]
[COLOR=#574123]IO.[/COLOR][COLOR=#574123]CTSalut,[/COLOR]
[COLOR=#574123]IO.[/COLOR][COLOR=#574123]CTfirstN,[/COLOR]
[COLOR=#574123]IO.[/COLOR][COLOR=#574123]CTSurname,[/COLOR]
[COLOR=#574123]IO.[/COLOR][COLOR=#574123]CTFullname,[/COLOR]
[COLOR=#574123]IO.[/COLOR][COLOR=#574123]Name1,[/COLOR]
[COLOR=#574123]IO.[/COLOR][COLOR=#574123]ADDR11,[/COLOR]
[COLOR=#574123]IO.[/COLOR][COLOR=#574123]ADDR12,[/COLOR]
[COLOR=#574123]IO.[/COLOR][COLOR=#574123]ADDR13,[/COLOR]
[COLOR=#574123]IO.[/COLOR][COLOR=#574123]ADDR14,
[/COLOR][COLOR=#574123]CU.RType,[/COLOR]
[COLOR=#574123]CU.TType,[/COLOR]
[COLOR=#574123]CU.Description,[/COLOR]
[COLOR=#574123]CU.RegNum[/COLOR][COLOR=#574123]
[/COLOR]
from [COLOR=#574123]dbo.VW_InOutStock IO
[/COLOR]left join dbo.[COLOR=#574123]custom.report3full CU on CU.invoicenumber = IO.invoicenumber [/COLOR]

Should be pretty obvious how the join itself works - the trick is aliasing the tables, I've used IO and CU but these could be anything. The aliases mean you can reference the second table.

Something like the above should give you the necessary single table!

Hope this helps.
Jacob
 
Last edited:
Upvote 0
ah excellent - you learn something everyday whoich makes it worth getting up! Essentailly an invoice may contian up to 20 lines of transactions, but every line has the same INVNUM and the same data from table 2. So there is a 20 to 20 lookup and VALUES only works becuase the table retruned by the filter has the same value. As long as this just takes the INVNUM form Table 1 and looks up on TABLE 2 and takes the first match it should work fine. Will give it a go. Thanks
 
Upvote 0
Before I could attempt this the software guys added the missing columns to the main table!!! Now I have the bizarre situation of removing al lthe filtered lookups has actually made the thing run like treacle!!!! so I'm now on Excel 2013 and using only 1 500,000 row table and seem to be worse off than before!!! Is there some clever way of cleaning Excel 2013 up as know there was some clever trick to force Powerpivot 2012 to sort of rebuild?
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top