This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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.
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.
If I'm thinking this through correctly, you can use a filter to isolate the P records, and a summarize tool to concatenate them together. Union back with the original data, and you have your concatenated fields.
@Claje That is so helpful, thank you! This had crossed my mind as an option but I don't have enough experience with the transpose tool. Such a big help in getting me on my way with my workflow. Really appreciate it.