How to concatenate a variable number of rows/cells based on multiple criteria?

[jonharadon]

I looking for help with the following issue:

I would like to concatenate a number of cells (and bullet points and line carriages before each new cell) which meet certain criteria in other cells on the same row. The number of rows meeting the criteria would range from 1 to over 30. The final concatenated result is in a single cell and is something which, along with a number of other fields, gets imported to Word as a mail merge.

I believe a loop macro function would be best, but am not a programmer at all and so am not sure.

In the spreadsheet I have attached, you can see the data in the tab Evidence_Report_crosstab. The criteria that must be met is from column A (name) and column D (indicators). The data to be concatenated is in column F and G. Sometimes up to 30 rows will meet the criteria and thus all the data from columns F and G would need to be concatenated together (along with bullets and line carriages between rows).

You can see the end result in the tab 'LA', in the columns Z:AH, each column representing a different indicator. Each of these cells in these columns in the 'LA' tab contains data from multiple rows of the 'Evidence_Report_Crosstab'


(As an FYI, In the spreadsheet, you can also see how I currently go about doing this, which is tedious. I copy rows which meet the name and specific indicator to the tab 'Staging', making note of how many rows I copied. Below this is a list of formulas which concatenate the appropriate number of rows. I then find the appropriate formula, copy it, and then use paste-values only to place it in the appropriate cell in the tab 'LA'. I have to do this 9 times for every person, and in the actual database there are many, many people.)

Any help is greatly appreciated!! I'm also open to completely changing my routine, if anyone has creative ideas on how to accomplish this.

Jonathon

[TMS]

Have a look at the ConcatAll function posted by TigerAvatar:

http://www.excelforum.com/tips-and-t...geravatar.html


Maybe you could use that ... although it might help to sort the data first.


Regards, TMS