VBA. If criteria is met, sum up figures that are over two sheets (same workbook)

Hi all
One of my colleagues at work has asked my help to see if there could be macro based solution that could help her with one certain task she has to perform which is very frustrating for her. I have played around with some different formulas and have not had any luck to figure it out myself.

Objective is to find out the total sum of quantities for given part that potentially has been ordered within the last 30 days. Its bit hard to explain the objective in one sentence… Let me just crack on with what the macro should actually do.

There are two sheets and 6 columns involved. Let’s call these two sheets as “Procured Parts” and “Procured Parts delivered”. On both of these sheets there are the following columns that we are concerned with: C, E, F, H, J. Answers are populated on sheet “Procured Parts” in column L. Column L on sheet “Procured Parts delivered” is not to be checked in any way nor its values are to be changed.

Easiest way to explain anything is to have example data of before and after macro. First column is for identification of examples (i.e. ex1, ex2, etc..). Data before macro as below.

Before macro


sheet “Procured Parts”

Ex. ---------- Col C ---------------- Col E ----------------- Col F ---------------------- Col H -------------------- Col J -------- Col L
Ex1 --------- TLD ------------------ 30/09/2011 --------- Customer one --------- P333333333 --------- 1 ------------ 1
Ex2 --------- TLD Plus ----------- 01/10/2011 --------- Customer one --------- P333333333 --------- 1 ------------ 2
Ex3 --------- AB ------------------- 30/10/2011 --------- Customer one --------- P111111111 --------- 1 ------------ ----
Ex4 --------- TLD ------------------ 30/10/2011 --------- Spares ------------------ P222222222 --------- 1 ------------ ----
Ex5 --------- TLD Plus ----------- 30/10/2011 --------- Customer one --------- P333333333 --------- 2 ------------ ----
Ex6 --------- TLD ------------------ 30/10/2011 --------- Customer two --------- P444444444 --------- 1 ------------ ----
Ex7 --------- TLD ------------------ 30/10/2011 --------- Customer two --------- P555555555 --------- 2 ------------ ----


sheet “Procured Parts delivered”

Ex. ---------- Col C ---------------- Col E ----------------- Col F ---------------------- Col H -------------------- Col J -------- Col L
Ex8 --------- TLD ------------------ 01/06/2011 --------- Customer two --------- P444444444 --------- 1 ------------ 1
Ex9 --------- TLD ------------------ 30/09/2011 --------- Customer two --------- P555555555 --------- 3 ------------ 3
Ex10 ------- AB ------------------- 01/10/2011 --------- Customer two --------- P555555555 --------- 4 ------------ 4
Ex11 ------- TLD ------------------ 01/10/2011 --------- Spares ------------------ P555555555 --------- 4 ------------ 4
Ex12 ------- TLD ------------------ 01/10/2011 --------- Customer two --------- P555555555 --------- 4 ------------ 4




The first half of the macro would just check if certain conditions are met on sheet “Procured Parts”. If all conditions are met it will start to sum up certain quantities found in column J.

It would check the following:
Col L - check col L if there is a empty cell, If yes then proceed (ex 3,4,5,6,7), if cell is not empty ,then do not proceed to next step (Ex1,2).
Col C – Check if cell equals “TLD” or “TLD PLUS”, if yes then proceed (ex 4,5,6,7), if not (ex 3) then populate value “n/a” to Col L
Col F – Check if cell equals “Customer one” or “Customer two”, if yes then proceed (ex 5,6,7, if not (ex 4) then populate value “n/a” to Col L


Now if all 3 checks cleared then it needs to check some more columns in order to do a calculation as below.
Macro needs to check column H, E and J
Column H – check for a part number. We need to compare apples with apples. Lets say the first part where we would need to perform calculations is part number P333333333 (ex 5). Macro would look quantities for the same part number (ex 1,2)
Column E – date check. Macro would look quantities that are within 30 days of this date stated in column E (inclusive)
Column J – macro would sum the quantities of column J if all criterias are met.

Macro has to sum up the quantities from both sheet that meet the criteria. Lets do some examples with some parts.

Ex1 and Ex2 - nothing is touched with these two lines as Column L already has value within it.

Ex 3 - P111111111 - column C does not have “TLD” or “TLD PLUS” so "n/a" is populated to col L

Ex4 - P222222222 - column F does not have “Customer one” or “Customer two” so "n/a" is populated to col L

Ex 5 - P333333333 - checks in col C and F are ok so we proceed with the macro. We are now checking part P333333333 on both sheets. Ex1 and Ex 2 on sheet “Procured Parts” meet the criteria. Checks with Ex 1,2 in col C and F are OK . We are only summing up the lines that are within the 30 days from the date stated in col E of Ex 5 (which is 30/10/2011). Ex 1 does not meet this criteria as its 31 days away, Ex 2 meets the criteria as its within 30 days. Values in column J of Ex 2 and Ex 5 are summed up which would be quantity of 3.

Ex 6 - P444444444 - checks in col C and F are ok so we proceed with the macro. We are now checking part P444444444 on both sheets. Ex 8 on sheet “Procured Parts delivered” meets the criteria
Check with Ex 8 in col C and F is OK . We are only summing up the lines that are within the 30 days from the date stated in col E of Ex 6 (which is 30/10/2011). Ex 8 does not meet this criteria as its 131 days away. Values in column J of Ex 6 are summed up which would be quantity of 1.


Ex 7 - P555555555 - checks in col C and F are ok so we proceed with the macro. We are now checking part P555555555 on both sheets. Ex 9,10,11,12 on sheet “Procured Parts delivered” meet the criteria. Checks with Ex 9,12 in col C and F are OK (Ex 10,11 are not ok) . We are only summing up the lines that are within the 30 days from the date stated in col E of Ex 7 (which is 30/10/2011). Ex 9 does not meet this criteria as its 31 days away, Ex 12 meets the criteria as its within 30 days. Values in column J of Ex 7 and Ex 12 are summed up which would be quantity of 6.


So after macro the sheet “Procured Parts” would look like this (values in Bold Green are the new values):
After macro

Ex. ---------- Col C ---------------- Col E ----------------- Col F ---------------------- Col H -------------------- Col J -------- Col L
Ex1 --------- TLD ------------------ 30/09/2011 --------- Customer one --------- P333333333 --------- 1 ------------ 1
Ex2 --------- TLD Plus ----------- 01/10/2011 --------- Customer one --------- P333333333 --------- 1 ------------ 2
Ex3 --------- AB ------------------- 30/10/2011 --------- Customer one --------- P111111111 --------- 1 ------------ n/a
Ex4 --------- TLD ------------------ 30/10/2011 --------- Spares ------------------ P222222222 --------- 1 ------------ n/a
Ex5 --------- TLD Plus ----------- 30/10/2011 --------- Customer one --------- P333333333 --------- 2 ------------ 3
Ex6 --------- TLD ------------------ 30/10/2011 --------- Customer two --------- P444444444 --------- 1 ------------ 1
Ex7 --------- TLD ------------------ 30/10/2011 --------- Customer two --------- P555555555 --------- 2 ------------ 6


I have attached a spreadsheet. I used my colleagues tracker, I took out all the sensitive information, made up these scenarios as above. After running the macro sheet "Procured Parts" should match with sheet "After".



Any help would be very appreciated as my colleague she is a lovely older lady who would find this very useful.

Cheers

Col L - check col L if there is a empty cell, If yes then proceed (ex 3,4,5,6,7), if cell is not empty ,then do not proceed to next step (Ex1,2).

1) Does an entry of "n/a" count as an empty cell?
None of the cells in col L are empty, they all have #, text, or n/a.
2) Can calculations be put into additional columns (say CA, CB, etc.) and hidden (if desired)? Most calculations are faster when done in Excel instead of VBA, and easier to understand if debugging is needed. Almost the entire procedure you described can be done with formulas in hidden (if desired) columns. The only thing that VBA is needed for is to actually put the values into column L

Additional question.
When you say within 30 days, I presume that means 30 days BEFORE the date in question, not 30 days AFTER the date, or both before and after?

Thank you. After doing some testing I can confirm it works very well. I'm sure my colleague will be very happy with it. I only changed the code little bit and so the spreadhseet has the calculation sheet ("working") permanently. it just unhides it and then hides it again.
Just a last question:

Please Login or Register  to view this content.

Does this line above actually affect anything in terms of the calculations? Is it being used to lookup some values or some information. What I'm concerned is that this part of the code matches the header text of some of the columns on the spreadsheet, if user would change anything on the header would it affect the code and the outcome? Its not a problem if this is the case as long as I can advise the user not to change anything on the headers.



Thank you for all of the help so far. Good stuff!