Merge rows from same column

Question

Hi Alteryx Community,

I’m working on a project where I need to merge rows with similar descriptions into a single row, while combining their numerical values. For example, I have rows like:

* "Allowances for impairment losses on financing"
* "and advances, net"

I want to combine these rows into one, so the resulting row will include both descriptions and the summed numerical values from each row.

I’ve been considering using regex to identify and group similar descriptions, but I’m struggling to get it right.

Could anyone provide guidance or suggest an approach to achieve this? I’m open to ideas on how to effectively use regex or any other methods in Alteryx to accomplish this task.

Thank you!

Attached is the input and expected output

test.PNG

BrandonB · Answer

Multi row to establish groups followed by a summarize tool to concat and sum as needed, and a select tool to remove the grouping field used in prior steps. Workflow is attached.

Grouping.yxmd

TUSHAR050392 · Answer

Hey @hari24 Another approach -

I see your part of the string in F1 column is moving to next row along with the values and if that is the case always, you can use multi-row formula to correct F1 column. Please follow below steps -

1. Filter out the row which contains Adjust for value in F1 which you can union later if you want with all data.

2.In multi row formula, click on update existing column and choose F1.

3. Write the formula - IF !Isnull(F3) THEN ROW-1:F1 + F1 ELSE F1 ENDIF. This will concatenate the data from last row and current row.

4. Lastly use filter tool to remove rows where F3 is null.

These steps will provide you the expected output. Let me know if this works.

Bren_Spill2 · Answer

@hari24 - An easier way might to create a mapping field for the columns you want to combine. There are multiple ways you can add the mapping: Text Input + Join, Formula, etc.

Once the rows are mapped, you can use the summarize tool to concatenate the text and sum the amounts. See attached.

Concatenate.yxmd