How to Add up cell value based on certain condition in other cell

Help Activities
, ,
1

Please refer to Attached Excel Sheet, “Input” and Required Output is in “Output”.
Whenever I have Duplicate Value in "Material ID " column add value in “Quantity” Col, Merge the Value in “Category” Column, if different value found.
If Material ID: Duplicate Value and Quality Check = “Completed” and
Combine the rows and Add up the No.of Quanity
If Material Code: Duplicate Value; Quality Check = “Partial Accepted” or “Rejected” or "Sample Material"and some rows Quality Check = “Completed”;
Combine the rows and Add up the No.of Quanity, of “Completed”
Leave the row, which has “Partial Accepted” or “Rejected” or “Sample Material”
Please help
Demo - IQC.xlsx (13.2 KB)

2

@Palaniyappan , can you please help me with this ?

3

Hi @Sudhir_P !

I have some questions to fully help you.

Would you mind confirming again the rule ?

  • If Material ID is duplicated and Quality Check = “Completed” → Merge the Value in “Category” AND sum up the “No.of Quanity”

  • If Material ID is duplicated and Quality Check = “Partial Accepted” OR “Rejected” OR “Sample Material” → Merge the Value in “Category”

  • If we have other quality check data (like Waiting or Accepted), being it duplicates or not for Material ID → we should keep the rows untouched.

Is that so ?

4

firstly, thank you for taking time for me.
First Point: You got it right.
Second Point : If Material ID is duplicated and has combination of Quality Check = “Completed” and Quality Check = “Partial Accepted” OR “Rejected” OR “Sample Material”.
In this Case,
For Quality Check: Completed, Merge the Value in “Category” AND sum up the “No.of Quanity” as First Point.
For Remaining Quality Check : “Partial Accepted” OR “Rejected” OR “Sample Material”.
Merge the respective Value in “Category”, AND sum up No.of Quianity, if you have more than one "Partial Accepted” OR “Rejected” OR “Sample Material” for similar Material ID.
If not, leave as it is.
I hope i made it clear, if not please reply me, i try to explain it better.
please refer to spreadsheet for more clarity

5

Hi @Sudhir_P !

Here is a suggestion of what I understood: Add_values_on_excel.xaml (17.6 KB)

Here is the result that I got at the end:

image
image1920×1080 192 KB

Let us know if it’s not the expected result !

6

Thank you for .xaml file, it’s close enough. Need just few changes.
Attached Excel sheet and add comments where change need in “out” Sheet and
New output should be like sheet "out2
Demo - IQC - New.xlsx (19.0 KB)
"

7

Hi @Sudhir_P !

Here is the new output taking into consideration what you wrote (in Out_corrected): Demo - IQC - New.xlsx (21.7 KB)
And here is the xaml file modified to get the result: Add_values_on_excel.xaml (17.8 KB)

Is it the expected output ?