Excel slow calculations VLOOKUP 125k rows

[kosmonautas]

Hello everyone,


I have sales report with more than 125k rows.


Each row must be multiplied by turnover discount (15% - 0,85 and etc.).


Since every row must be matched by product and seller, this means A LOT OF INDEX and MATCH.


Is there any way to overcome this and speed this process up?


Thanks!

 

[SirJB7]

Hi, kosmonautas!


Firstly, try turning calculation mode to manual and press F9 when needed updating. That's the easy way, but its efficiency'd depend on:

a) how, when, and in what number of records is the report updated?

b) how frequently do you change turnover discount value?

c) what other process do you perform in that workbook?


Would you please clarify those points? Thank you. A sample file will be useful too so as try a more accurate diagnostic, so consider it. Give a look at the second green sticky post at this forums main page for uploading guidelines.


Regards!


PS: Maybe you can edit the topic title to reflect more properly the issue you have, so people who might read it will get better info about the actual subject.

 

[Guest]

<p>With the present day economy,http://pd.learnedness.net/activity/p/85969/, barber shop trips are becoming extra burdens in people's lives. In fact, some people would rather maintain their shaggy hair rather balt ravens camo pants than take the trouble of having a nashville haircut. Whereas some people opt not to visit barbershops due to their rather busy lives, which is in no way an excuse, there are others who term the cost of visiting a barber too high when compared with the piled up bills that are yet to be paid. However, good things are emerging as it's now possible to acquire a haircut at the most economical way which helps you save some bucks for some other important things in future. The introduction of haircut coupons is a simple way of saving for your hair maintenance and personal grooming.<br packers hoodie women />
Although nashville haircut coupons and other discounted hair styling team steelers sweater where to buy programs are great way of helping men save when they visit their barbers, the saddest thing is that only a few selected barber shop actually offer the coupons and most of them will certainly not. So incase your local barber shop announces some discounted programs on haircut,http://www.avidgamerz.com/forums/topic/manual-milk-separator-advantages#post-20740, its good to take advantage of this. Although some barber shops will not necessarily offer discounted haircuts, a certain Nashville barber shop may be offering extra services to their clients when they get their hair styled at the shop. Some may even offer body wellness services such as massage and facial therapies for free. This way, you can get value for your money by opting to visit such a barber shop rather than one that offers you only plain nashville haircut with no additional benefits. This way, you do not wounded warrior project boys clothinghttp://http://sanfrancisco49ersjers...07/wounded-warrior-project-boys-clothing.html have to seek the services elsewhere which might make you incur more expenses given that massages are not such cheap.


There are various ways you can acquire a haircut coupon in Nashville. For instance, you can choose to compliment the barber shop for the excellent haircut services they have offered you by sending them an email. This way, they will yankees video rockerhttp://http://sanfrancisco49ersjerseyss.blogspot.com/2012/07/yankees-video-rocker.html see you as a loyal customer who appreciates the services received and a good barber shop will offer your discounted rates for your next nashville haircut at the same shop. Also, if the barber shop maintains a website and you subscribe to their periodic newsletter, you might end up receiving a complimentary coupon. If this is not the case,http://www.allnewyear.com/forum/viewtopic.php?p=125227#125227, various online sites offer free coupons which you can print and use them the next time you need a haircut. Actually, coupons offer people a chance of saving by getting the services at a lower rate than the real price being charged in the market. With these free coupons on haircut, there is no reason why you should neglect your physical looks even with hard economic times.</p>

 

[NARAYANK991]

Hi ,


You might find the following links helpful :


http://www.excelguru.ca/forums/showthread.php?132-INDEX-MATCH-versus-VLOOKUP


http://www.actuarialoutpost.com/actuarial_discussion_forum/archive/index.php/t-43184.html


http://www.decisionmodels.com/optspeede.htm


Narayan

 

[kosmonautas]

SirJB7,


a) this report is updated once a month, I save old data as values to different workbook and add new numbers to be calculated.

b) usually I do not change turnover discount values, but each new report needs to be recalculated since new data comes without discount

c) I perform few more columns with SUMIFS and VLOOKUPS to calculate logistics cost and group sellers.


All this data then goes to ACCESS for Pivot reporting.


I know this is silly, but at the moment this is only way to analyze sales data, since program they use for data entry is very limited.

 

[SirJB7]

Hi, kosmonautas!

Something about workbook structure? Worksheets design, columns, type of data, product & seller tables, ... A sample file?

Regards!