Macro needed to reduce excel processing time

[Anmol Singh]

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

 

[shawnhet]

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]

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]

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