I Need to Speed Up My Computer: Solution to Error: concatenate dynamic range of cells

Wednesday, September 21, 2016

Solution to Error: concatenate dynamic range of cells

concatenate dynamic range of cells

I have "Details" worksheet with a column having a value in each row. The column has a header cell.
The column has n rows below it populated. In a cell on another "Summary" worksheet, I would like to concatenate all the row values. For example on the "Details"
worksheet there would be:
Cell A1 has value "My Heading"
Cell A2 has value "R01"
Cell A3 has value "R06"
Cell A4 has value "R09"
Cell B1 has value "Another Heading"
Cell B2 has value "R01"
Cell C1 has value "One More Heading"
Cell C2 has value "R03"
Cell C3 has value "R04"
Cell C4 has value "R06"
Cell C5 has value "R07"
Cell C6 has value "R08"
Cell C7 has value "R10"

On the "Summary" worksheet, I would like to have a formula in Column B that would dynamically concatenate the rows 2 through n for each heading. For example:
Cell A1 would have "My Heading" and B1 would have "R01,R06,R09"
Cell A2 would have "Another Heading" and B2 would have "R01"
Cell A3 would have "One More Heading" and B3 would have "R03,R04,R06,R07,R08,R10"

Is this possible using just a single formula? (I do not have the option of using a macro.) If so, I'd appreciate any guidance on how to accomplish the task. (I can do it using some 'cheater' cells, but if possible to do it with one dynamic formula then
it would be much better.)

Thanks in advance,
Andrew

Keys to the Problem concatenate dynamic range of cells

Download Error Fixer for Free Now

Hi,

Try this

1.
Download and install the Morefunc add in from the following link on my website -

http://www.ashishmathur.com/extract-data-from-multiple-cells-of-closed-excel-files/
2.
Copy A1:C1 of Details sheet, select cell A1 of Summary sheet, right click > Paste Special > Transpose > OK
3.
Once the add in is downloaded and installed, use the following formula in cell B1 of the Summary sheet and copy down

=SUBSTITUTE(TRIM(MCONCAT(INDIRECT("'Details'!"&ADDRESS(2,MATCH(A1,Details!$A$1:$C$1,0))&":"&ADDRESS(10,MATCH(A1,Details!$A$1:$C$1,0)))," "))," ",",")

Hope this helps

Using Windows troubleshooter:

Click on Search on Charm bar and type Troubleshooting. Click on the very first option.
A new window will open. Click View all from the left pane.
Scroll down to the bottom and click on Windows update.
Troubleshooter window for Windows update will pop up. Click on NEXT. It may ask you for administrative permission.
The troubleshooter will automatically detect problems and fix it.
Now, close this window and try download/install the app which was previously showing error.

Another Safe way to Repair the Problem: concatenate dynamic range of cells:

How to Fix concatenate dynamic range of cells with SmartPCFixer?

1. Download Error Fixer . Install it on your computer. Click Scan, and it will perform a scan for your computer. The junk files will be shown in the list.