SUM IFS CONDITION COMPLEX SCENARIO

[raheelkhan]

Hi Sir,

I attached one excel file showing my Purchase Order table. Here item column contains two kind of items

CASE:
i) Materials without size wize break down: Showing Master Name of Item/Qty/Rate Directly
ii)Materials with size wize break down: Showing Master Name of Item BUT QTY/Rate/Value is "0", Qty/Rate/Value showing below
as sub item Name with size description in multiple number of rows right below the master description.
Along with attachment also added example below;





MPO
Date
PI #
Item
Qty
Price
Total


18-MPO-0004
1/2/2018
807-2018
Size Label
0.00
0.00
0.00


18-MPO-0004
1/2/2018
807-2018
NAVY - 10 REG - BRWL-122007-CA-BR4
500.00
0.01
6.96


18-MPO-0004
1/2/2018
807-2018
NAVY - 12 REG - BRWL-122007-CA-BR4
500.00
0.01
6.96


18-MPO-0005
1/2/2018
11-2018
239577-CA - -
427.00
0.07
31.17


18-MPO-0005
1/2/2018
11-2018
Hang tag
0.00
0.00
0.00


18-MPO-0005
1/2/2018
11-2018
239577 - -
2,635.00
0.07
192.36


18-MPO-0005
1/2/2018
11-2018
Hang tag
0.00
0.00
0.00


18-MPO-0005
1/2/2018
11-2018
BRHT-239577-CA-PET-RIDER - -
250.00
0.04
10.25


18-MPO-0005
1/2/2018
11-2018
BRHT-239577-CA-RIDER - -
250.00
0.04
10.25


18-MPO-0006
1/2/2018
12-2018
BRHT-172436
0.00
0.00
0.00


18-MPO-0006
1/2/2018
12-2018
172436 - -
881.00
0.07
59.40

































































Requirement:
Each Master Item sum up the quantity & Value of all sizes mentioned below the master item + showing the rate as well.

Kindly help in this matter.

Regards
raheel

[p45cal]

Try this macro in your attachment:
Sub blah()
Sheets("Sheet1").Copy After:=Sheets(Sheets.Count) 'keeps a copy of your original sheet untouched.
Set myRng = Range("A1").CurrentRegion
Range("H2").Value = 1
Range("H3:H" & myRng.Rows.Count).FormulaR1C1 = "=IF(SUM(RC[-3]:RC[-1])=0,R[-1]C+1,R[-1]C)"
Range("A1").Subtotal GroupBy:=8, Function:=xlSum, TotalList:=Array(5, 6, 7), Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Set *** = ActiveSheet.Outline
Set yyy = ActiveSheet.Rows(9)
myRng.Columns(8).Resize(myRng.Rows.Count + 2).Clear
myRng.Rows(myRng.Rows.Count + 2).Clear
Range("A1").ClearOutline
End Sub

[raheelkhan]

Thanks for your response, i am not aware about VBA and it's my first post here.

While trying above macro, it's Compiler/syntax error executing.
???

Rgds
Raheel

[p45cal]

The code goes into a Standard code module; see http://www.rondebruin.nl/win/code.htm

I see the code has been changed by this site as I posted, 3 asterisks replacing 3 x's.
You can ditch the lines beginning:
Set ***
and:
Set yyy
anyway.

Button in the attached runs the code.

[raheelkhan]

Thank you for your response but this is not exactly i required. I was required;
i-the master row should only be appeared with Total Qty/Value and rest must be deleted or removed
ii- Price must not be sum up rather show as same price as appeared in sub item price because it'll remain same in item sub category (The only diff. b/w Master Item & Sub Item is their item name)

[p45cal]

On sheet1 you've highlighted the Master rows in yellow, right?
Is that highlighting correct? You say that all the sub-category prices under a Master row are the same, but row 8 has a different price from those above it.
I note also that rows 16 and 18 are exactly the same; is that as it should be? The same at rows 9 and 11?

[p45cal]

try:
Sub blah()
Sheets("Sheet1").Copy After:=Sheets(Sheets.Count) 'keeps a copy of your original sheet untouched.
Set myrng = Range("A1").CurrentRegion
Range("H2").Value = 1
Range("H3:H" & myrng.Rows.Count).FormulaR1C1 = "=IF(SUM(RC[-3]:RC[-1])=0,R[-1]C+1,R[-1]C)"
Range("A1").Subtotal GroupBy:=8, Function:=xlSum, TotalList:=Array(5, 7), Replace:=True, PageBreaks:=False, SummaryBelowData:=False
myrng.Columns("F").SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[2]C"
myrng.Columns("A:D").SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[1]C"
DestnRow = 2
For rw = 2 To myrng.Rows.Count
If myrng.Rows(rw).EntireRow.OutlineLevel = 2 Then
myrng.Rows(DestnRow).Value = myrng.Rows(rw).Value
DestnRow = DestnRow + 1
End If
Next rw
Range("A1").ClearOutline
myrng.Columns(8).Resize(myrng.Rows.Count).Clear
Range(myrng.Rows(myrng.Rows.Count), myrng.Rows(DestnRow)).Clear
End Sub

[raheelkhan]

Hi Sir,
Sorry for late reply, Yes this is i want, Big Thanks!!!!!