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)''"))
