Combining Selected Rows to form extended rows

[Michael_Lambaskis]

Dear All,

I would like your help with a task that should be easy with the multi-row formula tool, but I didn't manage to succesfully perform it.

From the list of the rows on my table, I would like to select specific rows and and add them next to the above rows (extent the above row to have more columns).

My first column looks like this:

1   Nuevo
2   CM1123456789

3   Rostert
4   XH1233456789

So, I would like to select the first column, and choose that all rows that contain [A-Z0-9]{12} such as rows 2 and 4 below, be transposed to extend the previous row.

Hope it makes sense.

Thank you.

[danilang]

Hi @Michael_Lambaskis 

If you use this in your multi-row formula you'll get what you're looking for

if regex_match([Row+1:Field1],"[A-Z0-9]{12}") then
	[Row+1:Field1]
else
	null()
endif

[Michael_Lambaskis]

Dear Danilang,

Thank you for your answer. As this solution only appends one new column, is there any way to effect the transformation as shown below? Ie how can we append the below row to the one above it? 

[danilang · Accepted solution]

Hi @Michael_Lambaskis 

Is it always "all the fields from Row N+1 to Row N" or are there any other special conditions that you need to consider?  If it's always N+1 tacked on to the N rows you can use this

Add a RecordID.  Split the data set into even and odd records and join on record position, giving

Dan

[Michael_Lambaskis]

Dear Dan,

Basically I want that every time it detects [A-Z0-9]{12} in the below row,
to take that row and concatenate it to the above row.

Maybe should we first number such rows 1,1,22,33 etc with multirow formula
and then maybe use the Cross-Tab tool to concatenate all rows that have the
same number?

Michael Lambaskis

--


The information transmitted is intended only for the person or entity to
which it is addressed and may contain confidential and/or privileged
material. Any review, retransmission, dissemination or other use of, or
taking of any action in reliance upon, this information by persons or
entities other than the intended recipient is prohibited. If you received
this in error, please contact the sender and delete the material from any
computer.

[danilang]

Hi @Michael_Lambaskis 

Can you provide some more realistic sample data.  Include any edge cases where there may be more than one [A-Z0-9]{12} row or none, and any other exceptions that you can think of.  Does the number of columns change from one run to the next?  Which column(s) is the [A-Z0-9]{12} to be found in.

Dan