Invoice No | Material | Output | Output Ans should be like below | |
2190216929 | MS ROD | 1 | ||
2190216929 | MS ROD | 1 | ||
2190216929 | CEMENT | 2 | ||
2190216932 | MS ROD | 1 | ||
2190216932 | MARBEL | 2 | ||
2190216934 | MS ROD | 1 | ||
2190216934 | MS ROD | 1 | ||
2190216934 | SS ROD | 2 | ||
2190216934 | MARBEL | 3 | ||
2190216934 | MARBEL | 3 |
types of material mean types of material in material column based on invoice no.
Invoice No Material Output Output Ans should be like below 2190216929 MS ROD 1 2190216929 MS ROD 1 2190216929 CEMENT 2 2190216932 MS ROD 1 2190216932 MARBEL 2 2190216934 MS ROD 1 2190216934 MS ROD 1 2190216934 SS ROD 2 2190216934 MARBEL 3 2190216934 MARBEL 3
Try,
In C2, formula copied down :
=SUMPRODUCT(((A$2:A2=A2))/COUNTIFS(A$2:A2,A$2:A2&"",B$2:B2,B$2:B2&""))
View attachment 72807
Not the first time, and certainly not the last time, @bosco_yip is able to provide a correct answer when nobody else seems to understand the slightest bit of the OP's request. How do you do that?
This could work as well? [E4]=((C4=C3)*N(F3))+OR(C4<>C3,D4<>D3)
Like so?Your shorter formula, after making some trick that can remove N() and become :
=(C4=C3)*E3+OR(C4<>C3,D4<>D3)
View attachment 72834
How to do that ?
Yes !Like so?
View attachment 72839