Concatenating unknown number of subsequent rows

jamesgough

I have data with sentences of differing length that are split by rows. In my attached example I want to concatenate all [value] rows where P is followed by another P. Usually there is just two P values together but some data it can be as many as 25, therefore a static solution to this problem doesn't help me too much (which I found out the hard way). 

I originally thought a multirow formula could work but unless I specify row numbers it just ends up that P1+P2 followed by P2+P3, etc. rather than altogether. 

I hope that makes sense. I feel instinctively that there is a simple solution to this problem that doesn't involve writing out a 25 row formula. 

Thanks.

Concat help.xlsx

Claje

Hi,

To do this I would use two tools.

The first is a Multi-Row formula tool, which we will use to create a record id that only increments when it does not find consecutive P's.

To do this, I built a formula like the following:

IF [Row-1:Name] = [Name] AND Name = 'P' THEN [Row-1:RecordID] ELSE [Row-1:RecordID]+1 ENDIF

This basically checks if the current row is a P and the prior row was also a P, and if that is true, it does not increment the recordid (the field we are creating).  Otherwise, it increments.

Then, we can use a summarize tool and Group by Record ID and Name, concatenating Value.  In the summarize tool when we click on the "concat_value" column, at the bottom of the tool it lets you define a separator.  If you delete this it will concatenate without a delimiter, basically the same as P1+P2+P3.

Hope this helps!