Solved: sum values based upon 2 conditions

[joeyc]

1.) I would like to sum certain values based upon two conditions. Please view the attached document. It is easier to understand as I describe the situation.

2.) What am I trying to do? I would like to automatically generate the values for the report in F17:F21 for all products. I have done this manually via formuals for two products.

3.) What I do not know? I do not know the names of my products or how many products will be in the file. It may be Mutual Fund A or another time it may be SCV or it may be Large Cap. The file may contain 2 products or 22. So, this has to be taken into consideration. I need to use some type of code that can distinguish between different problems and store those values in a variable.

4.) Although this looks simple and you may ask me what don't I just do this manually because the file is small, this is actually a simplificiation. I have compacted the material into this one case becasue if I can do this I can solve the bigger problems.

===================================================
Any help that one can give me to get started would be greatly appreciated.
Any help that can get me started would be greatly appeciated.

[Bob Phillips]

.

[joeyc]

I am reviewing this as I write this. I am not familiar with this code structures so I will take me some time to understand this. I will get back to you within 24 hours. Thank you for what you did so far though.

[joeyc]

Several notes.

1.) Your example is very good. I was able to learn from it and use your code for the three required formats I need done. I set up the 3 formats I need and extended columns A,B,C to 20000.

2.) However, there is a serious problem right now. I would like to be able to paste data into columns A,B, and C on the DATA TO ANAlYZE worksheet and have it calculated. Currently this isn't possible. I tried to use a real sample of data I had and only the report for the first product was generated. But, when I type this data manually in, this problem doesn't occur. Any thoughts of how to fix this?

[mdmackillop]

This will paste header values into Row 17
[VBA]Sub Macro1()
Columns("A:A").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"IV1"), Unique:=True
Range(Cells(2, "IV"), Cells(2, "IV").End(xlDown)).Copy
Range("H17").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Range(Cells(1, "IV"), Cells(1, "IV").End(xlDown)).ClearContents
End Sub
[/VBA]

[joeyc]

That was fast!

Let me test this out a bit to see if there are any other bugs. Give me 24 hours to put this thing to the test. I will come back and mark it as resolved. Thank you for the macro.

[mdmackillop]

Probably better to fix XLD's formula solution. Looks like it need to be adjusted to fit your pasted data.

[joeyc]

Hi again,

What is wrong with his formulas? "Probably better to fix XLD's formula solution. Looks like it need to be adjusted to fit your pasted data" I know that some breakdowns exceed 100% but I fixed this. Is this what needed fixing? Or is his formula flawed in some subtle way that I am not seeing?


One small problem. The header value for the last product isn't in the right place. It should be in t17 yet it appears all the way in y17.

[mdmackillop]

One small problem. The header value for the last product isn't in the right place. It should be in t17 yet it appears all the way in y17.

There is some old formatting left which is centering the last value in T:AF.

[VBA]Sub Clear()
'
' Clear Macro
' Clears data
'
'
Range("H17:AF17").ClearContents
Range(Cells(2, "A"), Cells(2, "C").End(xlDown)).ClearContents
End Sub
[/VBA]

[joeyc]

1.) Your clear macro works perfectly. I replaced with with the macro I recorded.
2.) The last value is still being centered. I couldn't located the formatting that is centering the last value. I initially thought it was being caused by the code of the macro that writes the labels. If you know a quick solution, I would appreciate it. Otherwise, no worries! &

[mdmackillop]

I couldn't located the formatting that is centering the last value.

Now, where would I have put that formatting??? I'll let you puzzle it out.

[Bob Phillips]

Why aren't you auto-generating the labels as I showed you?

[joeyc]

The reason I didn't use your formula to generate the labels or headers was because it only worked when you type in the values. For some strange reason, I couldn't copy and paste data into a,b, and c and have the labels filled across. I responded to this in #4 on this thread.

It is possible I could be doing something wrong. Nevertheless, your code help me immensely. Thank for once again.

I will mark this as resolved. This is good enough.

Thank you xld and madmackillop.

This issue is resolved.

[joeyc]

Now, where would I have put that formatting??? I'll let you puzzle it out.

Found it. Okay, I need glasses.

[Bob Phillips]

.