To Summarize Data and Accumulate Other Related data into One Cell

I have some items, which have been defined into group....but located in different location...
So, I like to summarize all the items, then accumulate the location which the item located into one cell.

In data table, the first column would be the item group, then the next column would be the location of the item.
For example, Item A, located in Yard,Shed,Warehouse,Office,Yard,Shed,Warehouse,Office as per from data table.

As example, please find attached the Excel file.

The only way I know to do this is to use VBA. The user defined function CONCATALL ... code is below ... used in this way will do what you want.

The code is below this formula. Array enter this formula in F5 of your workbook and fill down. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Formula:

Please Login or Register  to view this content.

Instructions for installing the code is below this code window. The code for CONCATALL is:

Please Login or Register  to view this content.

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA function:
Enter the formula as above.

Originally Posted by FlameRetired

The only way I know to do this is to use VBA. The user defined function CONCATALL ... code is below ... used in this way will do what you want.

The code is below this formula. Array enter this formula in F5 of your workbook and fill down. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Formula:

Please Login or Register  to view this content.

Instructions for installing the code is below this code window. The code for CONCATALL is:

Please Login or Register  to view this content.

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA function:
Enter the formula as above.

Thanks for your effort on this. It helpful

You're welcome. Thanks for the feedback and the rep.