need help to create critical macro

Apple Macbook notebook
April 3, 2010 at 09:35:37
Specs: Windows 7
dear all,

I need your kind help to create an critical macro in which i have 2 files, 1. rawdata in which i need to filter for certain conditions to match in the summary file and paste it to the corresponding matching cell..

here is my creteria...
1. raw data
i have columns like item, supplier, region, sourcecontrol & business group under which i need to filter for 3 or more criteria in the supplier column and one criteria in region and one criteria in business group and take that sum of value and open the summary file in which contains in columns it contains global ops, subtotal, cpc,mrp,irp,smi as header .... and in ops it contains it contains vi and nonvi and total, under which i need to filter the value in rawdata file and then sum that and paste it in summary corresponding to global ops, cpc, crp, mrp (these are headrs of my summary file)and first column:global ops contains in cell a4 VI AND in A5 NVI...S
how do i do that please help... it is very critical and i am very new to macro

My requirement is like this.. in rawdata file filter for region column"ASIA" and then filter in business group column "globalops" and then filter in supplier column"SHARP","SAMSUNG","SONY" as "VI" value for asia and then get the sum of this filtered value and then activate the Summary file and then compare column A:global ops under "VI" and paste the summed value in the subtotal column... next go again to rawdata and then all filters same, only in supplier column exclude "SHARP","SAMSUNG","SONY" to get as "NVI" value.. and then again activate summary file and compare global ops column and paste it in subtotal column for valueand paste it.... next again go to rawdata file and then keep same filters for "VI" and then all other filter same then filter for "CRP" in sourcontrol column of rawdata and sum the value for this filter then activate summary file then compare the value for column global ops "VI" and then compare for same "CRP" and corresponding to VI paste the value in "CRP" column value.. and same continuous for CPC,MRP,IRP,SMI...

Kindly do the needfulll.
It will be great help for me.. because i have to do the same for 120 files by commodity wise.... kindly help me.. it will be great help for me

See More: need help to create critical macro

Report •

April 4, 2010 at 06:42:21
I would like to help, but I am having a lot of trouble understanding your requirements. Perhaps if you did these 2 things, your post would be easier to understand:

1 - Give us some examples of what the RawData and Summary sheets look like. If you use the pre tags found at the top of the Comments box you can line the data up in columns. You don't have to show us the entire sheet, just the columns that are important to your task.

For example:

        A       C         D          F
1     Item  Supplier   Region  Source Control
2      AA    Sony       ASIA        CRP
3      BB    Sharp      ASIA        CRP

2 - Tell us your requirements in a list.

For example:

1 - Filter the Supplier column on criteria
2 - Sum the filtered data on criteria
3 - Copy the sum to Summary Sheet
4 - etc.

Report •

April 4, 2010 at 10:55:52
Dear sir... thanks a lot for your kind response..
i tried to use comment box... i dont how to use it to get it in right format...

could you please provide me your "EMAIL ID" if possible, i will send you the sample file... so that u will get an idea ... sorry my english is very poor and explanation as well...

once again thnks a lot for your helping hand

Raw data look like this

Item Supplier Region S Control Busnes grp
1 AA Sony ASIA CRP glbal ops
2 BB Sharp ASIA CRP automotive
3 CC Sony america MRP infra
4 DD rim europe IRP global ops
5 FF sharp america SMI automotive

.............etc many suppliers repeats with source control and business group with different item number

etc.. and follows other few more columns...

Summary file look like this

Penetration Subtotal % CRP MRP SMI IRP
1 NVI $ % $ $ $ $
2 VI $ % $ $ $ $
3 Total $ % $ $ $ $

My requirement is as follows...
In my company they filter supplier SHARP and RIM and MULTEK as "VI"(vertical integration) and rest of supplier as "NVI" as shown in summary file example..
1. first i need to filter "Business group" column as "GLOBAL OPS"
2. second i need to filter "Region" column as "ASIA"
3. thirdly i need to filter "MULTEK", "SHARP", "RIM" (to considered as "VI")and finally SUM these filtered value and paste it in corresponding "VI" value in "subtotal" column ($)...
4. then i have to maintain the same filters then again filter for Source control column for "CRP" value in raw data and then copy paste it into corresponding "VI" in the "CRP" column($ place) ...
5. then once "VI" value has been updated in summary file.. then go to raw data file and keep the all the filter same except in "SUPPLIER" column.... in "SUPPLIER" column just do the opposite.. exclude "SONY", "SHARP" &"MULTEK" to get the "NVI" value and follow the same as did for "VI"

I found this very critical hence i tried to create pivot to get the subtotal of "VI" and "NVI" ...PIVOT has given me the correct data as i want but i found difficulty while copy and pasting it into the Summary file... i think we need to use "IF" function and "LOOP" to compare the subtotaled data in "raw data" file and paste the same in the Summary file by lookup..\\\

Pivot only shows whatever the data available in the rawdata file hence if any one of MRP value is missing then it will not show in the pivot header.. and while doing vlookup if the data is for MRP is missing then vlookup will take whatever the first column value like "CRP" for "VI" value and will paste it in the "MRP" column.... hence it will create a problem by pasting wrong data...

Report •

April 4, 2010 at 12:22:03
re: ...and finally SUM these filtered value and paste it...

I'm not sure what you are "summing" but it seems like SUMPRODUCT will get you what you need.

This will SUM the range F2:F6 for your VI suppliers in Global Ops in ASIA:

=SUMPRODUCT((B$2:B$6="Sharp")*(C$2:C$6="ASIA")*(E$2:E$6="Global Ops")*(F$2:F$6))
+SUMPRODUCT((B$2:B$6="RIM")*(C$2:C$6="ASIA")*(E$2:E$6="Global Ops")*(F$2:F$6))
+SUMPRODUCT((B$2:B$6="Multek")*(C$2:C$6="ASIA")*(E$2:E$6="Global Ops")*(F$2:F$6))

This will SUM F2:F6 for your NVI Vendors by summing all suppliers in Global Ops in ASIA and subtracting the VI suppliers:

=SUMPRODUCT((C$2:C$6="ASIA")*(E$2:E$6="GLBAL OPS")*(F$2:F$6))
-(SUMPRODUCT((B$2:B$6="Sharp")*(C$2:C$6="ASIA")*(E$2:E$6="GLBAL OPS")*(F$2:F$6))
+SUMPRODUCT((B$2:B$6="RIM")*(C$2:C$6="ASIA")*(E$2:E$6="GLBAL OPS")*(F$2:F$6))
+SUMPRODUCT((B$2:B$6="Multek")*(C$2:C$6="ASIA")*(E$2:E$6="GLBAL OPS")*(F$2:F$6)))

Obviously you can shorten this last formula by subtracting the "cell" that contains the VI formula, e.g.

=SUMPRODUCT((C$2:C$6="ASIA")*(E$2:E$6="GLBAL OPS")*(F$2:F$6))-$B$2

Report •

Related Solutions

April 4, 2010 at 19:14:40
Thanks a lot for your help..

i think this will help when i do this manually and that for one condition...
somebody said in this case pivot will help to do the subtotal by conditions... this helps me until subtotal the data but problem arise when i neeed to vlookup the subtotal data in rawdata file to summary file... since pivot will displays value if exist.. for example if "MRP" value is missing for south asia then it will not show in the pivot and hence it will create a problem when macro does the vlookup in rawdata pivot for "MRP" and corresponding "MRP" in Summary file... eventhough "MRP" value doesnot exist in rawdata pivot the vlookup just take the existing Cell value and paste it in the corresponding "MRP" in summary file..

hence i would like macro to do check "IF" "MRP" value exist in Rawdata pivot if it exist then paste it in the Summary file "MRP" cell else paste 0... and again repeat the same for "CRP","SMI", "IRP"..

If i do the pivot it look like this

Penetration Region MRP CRP IRP SMI
VI ASIA $ $ $ $
NVI ASIA $ $ $ $

and in summary file
Summary file look like this

Penetration Subtotal % CRP MRP SMI IRP
1 NVI $ % $ $ $ $
2 VI $ % $ $ $ $
3 Total $ % $ $ $ $

Macro should paste the value in summary file automatically if "MRP" value exist in rawdata else "0" and again go and check for "CRP" in rawdata pivot and then paste the corresponding "CRP" value in Summary file... and repeat the same for "SMI", "IRP" etc...

so that pivot will help me to filter this criteria to do the subtotal value
1. filter "Business group" column as "GLOBAL OPS"
2. second filter "Region" column as "ASIA"
3. filter "MULTEK", "SHARP", "RIM" (to considered as "VI")condition ....
then check the subtotal value for corresponding "MRP",CRP", "SMI", in rawdata pivot using "IF" condition and then if value exist then automatically paste it in corresponding summary file (the macro should take raw data pivot file reference and look for the value and again automatically go to summary file and then paste the same in corresponding cell)

sorry for bugging you sir... it will very helpfull for me if i get this done.. you are the only one replied for my request...

thanks a lot again..

Report •

April 4, 2010 at 21:13:45

From what I think you are wanting to do, I do not see any need for a macro.

You have a source worksheet (named raw data) and a summary worksheet.

What appears on the summary worksheet is just a series of totals of various rows and columns of data on the raw data worksheet.

It looks as though you want to summarize five amounts (CRP, MRP, SMI, IRP) and an item called SubTotal.
I assume that subtotal is a total sales value - but as you did not include a total value as part of your information in the raw data sheet, I am guessing that it is the sum of the other 4 items.

You want these five totals for each of the following combinations:

Asia	Global ops	VI
	Automotive	VI
	Infra		VI
America	Global ops	VI
	Automotive	VI
	Infra		VI
Europe	Global ops	VI
	Automotive	VI
	Infra		VI

First of all it is absolutely essential that all names are standardized. You cannot use America and america, or Asia and ASIA.
Use only America, Asia and so on.
The same applies to the worksheet names:
Use only 'raw data' and 'summary'

In the worksheet named 'raw data' you have rows of data - 1 for each product
Each row has the following columns (there may be other columns, but they are not used for the Summary worksheet.

A	B		C	D		E
Item	Supplier	Region	S Control	Business grp

However, you have not identified a column containing the dollar amount for each line.
For this example the dollar amount is in column Y.

A. VI / NonVI
1. As your Summary has to be broken down by VI and NonVI you need each row of data to include a column containing either VI or NonVI.
2. In an empty part of the 'raw data' worksheet create a list of all companies with VI or NonVI in the next column.
For this example use columns AA and AB
In AA1 enter 'Company'
In AB1 enter 'VI/NonVI'
In column AA starting at AA2 enter every company name
In column AB starting as AB2 enter VI or NonVI for each company.
Make sure that VI or NonVI is entered exactly the same.
For this example there are 50 companies with names in AA2 to AA51 and VI or NonVI in columns AB2 to AB51

3. Now use a new column to the' raw data' worksheet - for this example it is column X
In cell X2 enter this formula

Drag this formula down as many rows as you have product data.
This column will now show VI or NonVI for each product based on the company.

B. Sales or SubTotal
1. You have a need to show 'SubTotal($)' in the 'summary' worksheet
2. It is assumed that this is a total of the values in column Y.

C. summary Worksheet layout
1. The summary worksheet has the following headings in row 1

A	B	C	D		E		F	G	H
Region	Business Group	VI/NonVI	Subtotal ($)	CRP	MRP	SMI	IRP

As shown earlier, there are groups of headings as follows:
Asia	Global ops	VI
	Automotive	VI
	Infra		VI
under each of the first three columns.

In the following formulas all the references to the source data (raw data) are to rows 2 to 7.
Row 2 is fixed, but row 7 must be replaced by the last row containing data in the raw data worksheet.

D. Summary Worksheet formulas
1. SubTotal($) - column D
enter the formulas in cells D2, D3, D4, D5 and D6
See next response for these formulas.

2. CRP - column E
enter these formulas in cells E2, E3, E4, E5 and E6
See next response for these formulas.

3. Remaining columns F to H
Select cells E 2 to E19.
Drag the cells across to column H

The data on the summary sheet must be checked against the data on the raw data sheet to ensure that the formulas are returning the correct values.

For example use this formula on the Worksheet 'raw data'
and check that it returns the same total as
=SUM(E2:E19) on worksheet 'summary'

Hope this helps.

I may have not fully understood the issues, so please reply with further details.


Report •

April 5, 2010 at 04:56:49
Here are the formulas referred to
in the last previous response:

1. SubTotal($) - column D
enter these formulas in cells D2, D3, D4, D5 and D6

=SUMPRODUCT(('raw data'!$C$2:$C$7=summary!$A2)*('raw data'!$E$2:$E$7=summary!$B2)*('raw data'!$X$2:$X$7=summary!$C2)*('raw data'!$Y$2:$Y$7))
=SUMPRODUCT(('raw data'!$C$2:$C$7=summary!$A2)*('raw data'!$E$2:$E$7=summary!$B2)*('raw data'!$X$2:$X$7=summary!$C3)*('raw data'!$Y$2:$Y$7))
=SUMPRODUCT(('raw data'!$C$2:$C$7=summary!$A2)*('raw data'!$E$2:$E$7=summary!$B4)*('raw data'!$X$2:$X$7=summary!$C4)*('raw data'!$Y$2:$Y$7))
=SUMPRODUCT(('raw data'!$C$2:$C$7=summary!$A2)*('raw data'!$E$2:$E$7=summary!$B4)*('raw data'!$X$2:$X$7=summary!$C5)*('raw data'!$Y$2:$Y$7))
=SUMPRODUCT(('raw data'!$C$2:$C$7=summary!$A2)*('raw data'!$E$2:$E$7=summary!$B6)*('raw data'!$X$2:$X$7=summary!$C6)*('raw data'!$Y$2:$Y$7))
=SUMPRODUCT(('raw data'!$C$2:$C$7=summary!$A2)*('raw data'!$E$2:$E$7=summary!$B6)*('raw data'!$X$2:$X$7=summary!$C7)*('raw data'!$Y$2:$Y$7))

2. CRP - column E
enter these formulas in cells E2, E3, E4, E5 and E6
=SUMPRODUCT(('raw data'!$C$2:$C$7=summary!$A2)*('raw data'!$E$2:$E$7=summary!$B2)*('raw data'!$X$2:$X$7=summary!$C2)*('raw data'!$D$2:$D$7=summary!E$1)*('raw data'!$Y$2:$Y$7))
=SUMPRODUCT(('raw data'!$C$2:$C$7=summary!$A2)*('raw data'!$E$2:$E$7=summary!$B2)*('raw data'!$X$2:$X$7=summary!$C3)*('raw data'!$D$2:$D$7=summary!E$1)*('raw data'!$Y$2:$Y$7))
=SUMPRODUCT(('raw data'!$C$2:$C$7=summary!$A2)*('raw data'!$E$2:$E$7=summary!$B4)*('raw data'!$X$2:$X$7=summary!$C4)*('raw data'!$D$2:$D$7=summary!E$1)*('raw data'!$Y$2:$Y$7))
=SUMPRODUCT(('raw data'!$C$2:$C$7=summary!$A2)*('raw data'!$E$2:$E$7=summary!$B4)*('raw data'!$X$2:$X$7=summary!$C5)*('raw data'!$D$2:$D$7=summary!E$1)*('raw data'!$Y$2:$Y$7))
=SUMPRODUCT(('raw data'!$C$2:$C$7=summary!$A2)*('raw data'!$E$2:$E$7=summary!$B6)*('raw data'!$X$2:$X$7=summary!$C6)*('raw data'!$D$2:$D$7=summary!E$1)*('raw data'!$Y$2:$Y$7))
=SUMPRODUCT(('raw data'!$C$2:$C$7=summary!$A2)*('raw data'!$E$2:$E$7=summary!$B6)*('raw data'!$X$2:$X$7=summary!$C7)*('raw data'!$D$2:$D$7=summary!E$1)*('raw data'!$Y$2:$Y$7))

Report •

April 5, 2010 at 06:02:58
I was just giving you an example of how SUMPRODUCT could be used for a single set of criteria.

I didn't bother mentioning that modifications to my formula could be used for the other conditions; perhaps I should have.

Without digging into the details of Humar's formulae, I assume that they are basically modifications of my suggestion.

I don't know much about Pivot tables, so perhaps you are right in saying that they are needed, but as far as I can tell, it still looks like SUMPRODUCT will get you what you want.

Report •

April 5, 2010 at 07:11:40

Interestingly DerbyDad03 and I have both offered solutions based on a similar approach. As I had not read DerbyDad03's solution at the time I started working on this, I think it tells you that there is likely a solution to your problem based on using formulas.

As I said before, there is no need to use macros, and I don't see the need to create pivot tables.

I know that some users of Excel use Pivot Tables extensively, but I have not used them and cannot comment on whether they would provide an alternative solution.

In response number 4 you say:hence i would like macro to do check "IF" "MRP" value exist in Rawdata pivot if it exist then paste it in the Summary file "MRP" cell else paste 0
and: Macro should paste the value in summary file automatically if "MRP" value exist in rawdata else "0"

Again I do not see why you need a macro to copy a value.

If an entry is not present you can use an IF() statement to test if something is present and if it is return a value from a related cell and if it is not present, return a zero, like this:

=IF('raw data'!D4="MRP",'raw data'!G4,0)

If you want to get a solution I suggest that you work on the approach offered, basically using formulas - such as SUMPRODUCT(). Concentrate on one solution !

IMHO if you start combining Pivot Tables with formulas it will become far too complicated.


Report •

April 6, 2010 at 02:57:22
Dear humar/DerbyDad03
thanks a lot for your kind response and solution for this.

It will work fine when only one file need to be fill this information...

actually i have 160 files by commodity needs to be updated the same... to update 160 files it consumes lot of time for me hence i would like to create a MACRO and update all the 160 files automatically so that i can save lot of time and no need to update it manually each and every time..

can you help me to create macro using this SUMPRODUCT formula....

please help :(

Yes there is an "total value" field in the rawdata file, sorry i had not mentioned in rawdata example

once again thanks...

Report •

April 6, 2010 at 03:11:04
Dear humar/DerbyDad03
your suggestions are excellent
I want to mention that yes that subtotal is a total sales value is the sum of the other 4 items of CRP,MRP,IRP, SMI in raw data file....

thanks for your response... and solution..

is it possible to get me a macro for this please :( since i have to update more than 160 files by commodity...

Report •

April 6, 2010 at 05:15:05

If you get the formulas working in one workbook, you could create a macro to open the 160 or so other files and copy the formulas into them.

To do this it would be necessary to know how the names of the 160 workbooks are related.

If all 160 workbook names have to be entered into a macro, and as this is a one timeoperation, writing and testing the macro will take as long as opening 160 files and pasting the formulas in.

But maybe I am missing something.


Report •

April 6, 2010 at 10:53:37
Hi DerbyDad03..
I have tried the SUMPRODUCT formula which u have provided... works very fine for the "VI" list of suppliers... since they are only three supplier
2.but problem arise when i would like to apply the same formula for "NVI" because.... ("NVI" is the except the those three suppliers which used in "VI").. contains more than 200 suppliers name so it is very difficult to add the formula for entire suppliers of "NVI"...
i think then it will work fine.....

Report •

April 6, 2010 at 11:04:31
Please re-read the 2nd part of Response #3 where I addressed that very issue.

If you sum all of the suppliers based on whatever other criteria you need (ASIA, Global Ops, etc) and then subtract the sum of the 3 VI suppliers, you'll be left with the sum of the NVI suppliers - without having to use any of the 200 NVI names in a formula.

Since you already have the SUM of the VI suppliers in another cell, just subtract that cell from the SUM of all of the suppliers.

Report •

April 6, 2010 at 11:22:44
Isn't that like the basic Set Theory we learned in school?

The number of houses in the Set of "All Houses" is 10.

The number of Red houses in the Set of "All Houses" is 6.

How many houses in the Set of "All Houses" are not Red?

Report •

April 7, 2010 at 01:29:22
Hi DerbyDad03
it works..sorry, actually i dint check that previously....later on i got that tooo...


Report •

April 7, 2010 at 04:37:16

If you wanted to show VI or NonVI against each supplier, you just need a simple VLOOKUP().
If the names of your three VI suppliers are in cells AA2 to AA4
use this formula in an empty column, say column X as I used in response number 5,

(The supplier name is in column C).
Drag the formula as required and you have VI or NonVI on each row.


Report •

Ask Question