need advice in dealing with large data sets in 2007

[Jeffrey Green]

I have large spreadsheets in 2007 . . 200,000 rows sometimes . . . some formulas, mainly vlookups....

it just takes forever to do anything, Excel is always wanting to recalculate things . . . .I set my preferences to MANUAL, but it is still calculating, even without me Saving . . . . any advice would be helpful

 

[EXCEL-lence]

Hi Jeff,

What do you need to do with the data?

A database might be a better shout and using MS Access?

What do you think?

If you provide more details I can look into it

 

[Jeffrey Green]

I hate Access . . . . . and all of my data is fed to me in Excel.

Mainly I have a THOUSANDS of VINS on one sheet . . .and THOUSANDS of VINs, with the amount we paid for them on another sheet, and I do a vlookup to pull in the paid amount.

I always have hundreds that aren't on sheet two that I have to research . . .

 

[Jeffrey Green]

But excel keeps recalculating the entire book every time I paste in a couple thousand more lines that don't have formulas

 

[Excelestial]

ONly way I can figure it's still calculating is if it's doing an "autosave" everytime you paste in new data. Use your EXCEL OPTIONS button to turn off "recalculate before saving" option.

 

[Jeffrey Green]

One more reason I don't use Access . . . I send these VINS all over the place, finance, shippers, etc . . . . must each to just copy and paste that day's issues from-to an Excel worksheet

 

[Jeffrey Green]

I did that . . . . prior to posting here to MrExcel . . .

 

[Norie]

Jeffrey

You seriously should look at a database, doesn't need to be Access.

If this is what you've got any database can handle much better than Excel can.

A few simple lines of SQL would pull the price for all the VINs in one go.

Another would show you any missing VINS from either side.

This for the prices:

SELECT [VINTABLE].[VIN], [PRICETABLE].[PRICE]
FROM [VINTABLE] LEFT JOIN PRICETABLE ON [VINTABLE].[VIN] = [PRICETABLE].[VIN]

This for the VINS not in the price table.

SELECT VINTABLE.*
FROM VINTABLE LEFT JOIN PRICETABLE ON VINTABLE.[VIN] = PRICETABLE.[VIN]
WHERE PRICETABLE.VIN Is Null;

 

[Jeffrey Green]

Norie . . . tks for the help, but I live in a corporate world where Excel and Access are my only tools.

Additionally, most people here don't know access and any procedures that I need to write will have to be written at a 6th grade level, which means no SQL . . .

 

[Norie]

Jeffrey

So the users will need to know how to write SQL?

I suppose if that's the case it rules out a database.