cryptic ツ's profile - activity

App: Calc (LibreOffice)
Version: 4.2.1.1
Build ID: 420m0(Build:1)

I have the following where I have an E column which contains a comma separated list of IDs.

   A            B            C        D            E
1. Item Name    ColName      Count    ColName      Location ID(s)
2. Tomato       somevalue    4        somevalue    1,4
3. Apple        somevalue    6        somevalue    1,2
4. Avocado      somevalue    2        somevalue    1,3,4
5. Pumpkin      somevalue    8        somevalue    2,3
...

On different sheets I want to auto-populate the rows by having it grab the A Item Name column, and the C Count column (but have the count value divided by 2) where a specified location ID is a substring in the location list for that item. This should change automatically if I add new rows to the above or change the location IDs.

The following are the example rows that would be generated from above sample for each location ID (note how the count value has been divided by 2):

Sheet 1 (location ID list contains 1):

   A            B
1. Item Name    Count
2. Tomato       2
3. Apple        3
4. Avocado      1
...

Sheet 2 (location ID list contains 2):

   A            B
1. Item Name    Count
3. Apple        3
5. Pumpkin      4
...

Sheet 3 (location ID list contains 3):

   A            B
1. Item Name    Count
4. Avocado      1
5. Pumpkin      4
...

Sheet 4 (location ID list contains 4):

   A            B
1. Item Name    Count
2. Tomato       2
4. Avocado      1
...

How would I got about auto-populating such a list and fetching only the rows that match the substring. I've done a similar thing using SUMIF() and a regular expression, but in this case I am not trying to calculate anything other than doing a simple division of the Count cell.

Both files incorrectly calculate the values. I ended up creating a new hidden column which is the total weight of the item divided by the value of above formula. I then changed your first formula to grab that value instead of the total weight value. I'm sorry if I'm not being clear about what I'm needing. I don't know how to combine the two formulas so that I would not need the hidden column though. I'm going to mark as accepted, but would appreciate an answer on how to combine the formulas.

I figured out how to count the number of location IDs =LEN(ref_cell)-LEN(SUBSTITUTE(ref_cell,",",""))+1 Could you please update your answer so it uses that to do the proper calculation alongside your formula? Once working will mark as answered and remove all these cluttering comments. Thank you for your help.

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.