+ Reply to Thread
Results 1 to 2 of 2

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

  1. #1
    Registered User
    Join Date
    08-27-2012
    Location
    Denver, CO
    MS-Off Ver
    Excel 2010
    Posts
    1

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

    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
    Attached Files Attached Files

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    36,007

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

    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
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1