excelling_
New Member
- Joined
- Jan 1, 2022
- Messages
- 1
- Office Version
- 2013
Hello, I would like to use this excel table and make my changes: add/del table rows, add/del table columns and still receive summarized results (concatenated to 1 cell with delimiter of my choosing) on the sides like in the image. there are two kinds of summaries im looking for.
On I2:J6 its summarizing by row, in one cell, concatenated by a comma character. It says for each sub, what multi it is in
On D8:G9 its summarizing by column, in one cell, concatenated in the same way. Its saying what is in each multi: sub1, sub2, sub3, etc. I have moved the cells to be in column D just so its easier to see the results without wrapping, but it should be under D8:G9, as if its corresponding to each column under the table, like in image 2.
I will be doing data manipulation in the table mainly: adding/removing x's, adding/deleting rows to the table, adding,deleting columns to the table. filtering, sorting the table as well
What i tried to do (and maybe this should be ignored) was use some helper cells but I noticed that when i manipulated the table rows, columns, the helper cells formulas would break, and my summarized concatenated cell would be wrong.
example formulas I used:
if table is on A6:BC1800, and actual data is on D7:BC1800 then I put on BD7 =IF(ISBLANK(D$7),"",D$7&"+") and BD8 =BD7&IF(ISBLANK(D8),"",D8&"+") filled down to BD1800. Here instead of x's I had the sub# repeated in the cell, then on BD1800, i would have a concatenated result for column D. I found that using sub# in each cell was inefficient so I want to use x's instead.
So then I am working on a minitiarized example sheet I attached an image of here and I was able to get a partial summary on the right of the table in I2:J6 using Index/Match return column header (multiple occurrences) but its a fill to the right type of formula. The problem is I won't know how many cells I have to fill to the right? and that changes in the table break the formulas, and also that I'm looking for results in one cell instead.
I also messed around with some offset() type of formulas but I'm not sure how to do it with a table with amorphous rows, columns.
what could work for this? appreciate any response
On I2:J6 its summarizing by row, in one cell, concatenated by a comma character. It says for each sub, what multi it is in
On D8:G9 its summarizing by column, in one cell, concatenated in the same way. Its saying what is in each multi: sub1, sub2, sub3, etc. I have moved the cells to be in column D just so its easier to see the results without wrapping, but it should be under D8:G9, as if its corresponding to each column under the table, like in image 2.
I will be doing data manipulation in the table mainly: adding/removing x's, adding/deleting rows to the table, adding,deleting columns to the table. filtering, sorting the table as well
What i tried to do (and maybe this should be ignored) was use some helper cells but I noticed that when i manipulated the table rows, columns, the helper cells formulas would break, and my summarized concatenated cell would be wrong.
example formulas I used:
if table is on A6:BC1800, and actual data is on D7:BC1800 then I put on BD7 =IF(ISBLANK(D$7),"",D$7&"+") and BD8 =BD7&IF(ISBLANK(D8),"",D8&"+") filled down to BD1800. Here instead of x's I had the sub# repeated in the cell, then on BD1800, i would have a concatenated result for column D. I found that using sub# in each cell was inefficient so I want to use x's instead.
So then I am working on a minitiarized example sheet I attached an image of here and I was able to get a partial summary on the right of the table in I2:J6 using Index/Match return column header (multiple occurrences) but its a fill to the right type of formula. The problem is I won't know how many cells I have to fill to the right? and that changes in the table break the formulas, and also that I'm looking for results in one cell instead.
I also messed around with some offset() type of formulas but I'm not sure how to do it with a table with amorphous rows, columns.
what could work for this? appreciate any response