Macro needed to reduce excel processing time

Anmol Singh

New Member
Joined
Oct 7, 2014
Messages
2
I have data in 2 sheets.


Sheet 1 is input sheet in which i have 7 Columns of data from a database running into 5lac rows. Column H, I, J have formulas as follows: Col H =IF(C4=Sheet2!$E$2,1,0), Col I =IF(H4=1,B4,0), Col J =IFERROR(IF(VLOOKUP(B4,I:I,1,0)<>0,1,0),0). Sheet2!$E$2 is the main input cell


Sheet 2 is output sheet with following formula =COUNTIFS(Sheet1!$J:$J,1,Sheet1!$C:$C,B6)


Aim is to build a good macro that reduces processing time. Currently processing takes 5 min
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

shawnhet

Well-known Member
Joined
Feb 12, 2011
Messages
547
As a first step, you may just want to try and simplify your expressions above.

If I read what you are trying to do correctly, you want to count all the cells in column C of Sheet1 that match E2 and possibly some other criteria. ( I find it a bit confusing).

If this is the case, you should be able to build a single countifs expression and not need the other three formulae at all.

Why don't you describe what you would like to do in words and we'll see if we can't just build a better exepression?

Cheers, :)
 

RickXL

MrExcel MVP
Joined
Sep 9, 2013
Messages
4,326
Anmol, Welcome to the MrExcel Message Board.

Am I correct in assuming that "5lac rows" means 500,000 rows?

Like Shawnhet, I think it should be possible to simplify your equations. For instance, the information worked out by the VLookUp formula is largely know when the Column I equation is calculated.

I have some macros that might work for you but I have not tested them with 500,000 rows!

If you provide some more information about what you are trying to achieve and whether the intermediate results are required of just a way of getting the final count I am sure we will be able to help.

Regards,
 

Anmol Singh

New Member
Joined
Oct 7, 2014
Messages
2
Thanks a lot for responding.

I will try to simplify the question. I am analysing billwise data, and one bill can contain multiple products. I am wanting to make a model that shows - if we input the article code of any product in sheet 2, cell E2, then it reads data in sheet 1 and gives me the bill count of all products that were sold along with the input product of cell E2

I am not expert in excel and i agree that my formulas can be improved. My objective is to reduce the time taken to process.

5 lac means 500,000 (Correct)

Pls. let me kn ow if anything more is requried from my end

Thanks & Rgds
Anmol
 

Watch MrExcel Video

Forum statistics

Threads
1,122,910
Messages
5,598,803
Members
414,260
Latest member
joishe

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
Top