# Macro needed to reduce excel processing time

#### Anmol Singh

##### New Member
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

### Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

#### shawnhet

##### Well-known Member
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
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
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

Replies
9
Views
108
Replies
25
Views
723
Replies
9
Views
431
Replies
0
Views
383
Replies
7
Views
152

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,466
Messages
5,831,792
Members
430,087
Latest member
meagerd

### 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.

### Which adblocker are you using?

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

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