Trying to find a easy solution to summarise each type of building material in a table like below:
A(id) | B(WallType) | C(Quantity)) |
---|---|---|
1 | brick, drywall | 25 |
2 | drywall, stud, drywall | 10 |
3 | drywallFire, stud, drywallFire | 20 |
4 | drywallFire, drywallFire, stud, drywallFire, drywallFire | 30 |
The column B is a combination of material code that join by ", ". For example,
- Row 2 means drywall on both side, this row's drywall quantity should be 20 (10x2).
- Row 3 is multi-layers fire rated drywall, drywallFire should be 120 (30x4).
Is there a formula combination to get a list (unique) of material and quantity?
Material | Sum |
---|---|
brick | 25 |
drywall | 45 |
drywallFire | 160 |
stud | 60 |
CodePudding user response:
use:
=INDEX(QUERY(SPLIT(TRIM(FLATTEN(SPLIT(B2:B, ",")&"♦"&C2:C)), "♦"),
"select Col1,sum(Col2) where Col2 is not null group by Col1 label sum(Col2)''"))