Concatenating Subsequent Row Records.

[MD2050]

Hello Everyone- 

I have following Data Set :

<V1.0> A1-V
Comments – 13	“Regular – 1/16/2019”
Comments – 14	“1110N”
Comments – 13	“Regular – 1/16/2019”
Comments – 14	“1110N-2”
Comments – 13	“Regular – 1/16/2019”
Comments – 14	“1111N-4”
records continue ……
<V2.0> B1-V
Comments – 13	“Regular – 1/18/2019”
Comments – 14	“2220N”
Comments – 13	“Regular – 1/19/2019”
Comments – 14	“212N-1”
records continue ……
<V3.0> C1-V
Comments – 13	“Regular – 1/16/2019”
Comments – 14	“3330N”
records continue ……

Desired output: please note that there are several records between <V1> , <V2> and we need to iterate the Comments-13 and Comments-14 rows to get merged in one row, please see the desired output table below. 

<V1.0> A1-V	“Regular – 1/16/2019”  “1110N”
New Field Name	“Regular – 1/16/2019” “1110N-2”
New Field Name	“Regular – 1/16/2019”  “1111N-4”
New Field Name
<V1.0> B1-V
New Field Name	“Regular – 1/18/2019”  “2220N”
New Field Name	“Regular – 1/19/2019”  “212N-1”
<V1.0> C1-V
New Field Name	“Regular – 1/16/2019”  “3330N”

I tried to add a Summarize tool at the end and did “Concat” to pull all records in one row and then slice it using “Text to Columns” using  “<>” as delimiter . Logic doesn't seems to work ,as I couldn’t see all the records. Will really appreciate any help.

Thank you very much.

[JoBen · Accepted solution]

Hi @MD2050, here is what I came up with. I had to use a few different tools and logic, but ultimately, I think I came up with what you are looking for. 

[MD2050]

Thank you @JoBen - 

Your solution worked perfectly.

Can you please help elaborate what was the purpose of "$1" ? by using MOD() you are trying to check if 13 is even or odd and based on that you are incrementing the count of RecordID .Is that correct?

IIf ( Mod ( ToNumber ( Regex_Replace (  [Right_Test],".+?(\d+)", "$1" ) ),2 )=1,[Row-1:RecordID]+1, [Row-1:RecordID] )

Thank you very much.

[JoBen]

Okay. Glad it worked for you! The "$1" in a Regex formula is basically separating out the first marked section of an expression (anything in parenthesizes). So in this case, "$1" = "\d+", which is an unspecified amount of digits together. For the Mod(), you are correct. I was trying to find out whether the number produced was odd or even, and then basing the RecordID field on the result.