App: Calc
Version: 4.2.1.1
Build ID: 420m0(Build:1)
I have the following where I have various items with a weight per item, count of how many of that item, the total weight of item and the location ID(s) where that item will be.
A B C D E
1. Item Name Weight Count Total Weight Location ID(s)
2. ItemName1 53 4 =PRODUCT(B3*C3) 4,6
3. ItemName2 23 6 =PRODUCT(B4*C4) 1,5
4. ItemName3 85 15 =PRODUCT(B5*C5) 1,3,4
5. ItemName4 12 8 =PRODUCT(B6*C6) 2
I then have the following, which corresponds to the location IDs in the above.
A B C
1. Location Name ID Total Weight
2. Name1 1 494 = (D3 / 2 (comma items in E3)) + (D4 / 3 (comma items in E4))
3. Name2 2 96 = (D5 / 1 (comma items in E5))
4. Name3 3 425 = (D4 / 3 (comma items in E4))
5. Name4 4 531 = (D2 / 2 (comma items in E2)) + (D4 / 3 (comma items in E4))
6. Name5 5 69 = (D3 / 2 (comma items in E3))
7. Name6 6 106 = (D2 / 2 (comma items in E2))
What I am trying to do is calculate the total weight the a location will have. This is done by finding all E
cells which contain the substring of the column ID. If the cell contains that ID I would then get the D
value for that row and divide it’s value by the total amount of location IDs for that item. That value calculated would be summed up for all items which contain that location ID.
I have no idea where to begin on this. Is it even possible to match by substring and also get a count of how many comma separated IDs there are? I’m kinda new to Calc so if there is a better way for me to do this please tell me.