Merge Rows Data into 1 - Names

Hi Everyone,

I'm looking to merge 2 rows in order to have a "Last Name,First Name" in the same cell followed by that persons ID number and currently have a list that looks like this:

What I have

1 Smith

2 John

3 000001

4 (Blank)

5 Smith

6 Jane

7 000002

8 (Blank)

9...

etc

What I need:

Column A Column B

1 Smith,John 000001

2 Smith,Jane 000002

...etc

Any help you could provide would be greatly appreciated.

Thank you!

Workflow

Developer

Developer Tools

Input

Reporting

Regex

Tips and Tricks

Dynamic Processing

Join

Preparation

Optimization

In Database

Interface Tools

Documentation

In-Database

Accepted answers

ggruccio

Got it,

I've revised the logic to use the Formula tool - wondering if there was an issue with the append fields logic that worked on a relatively small dataset, but had difficulty when it got larger.

Additionally the workflow as it is built assumes that the same pattern of four rows of input data converts to one row of output data. Is this the case for all the data, or does it vary sometimes 3 rows should make up an output row, or 5 etc?

answer row question - revised.yxmd

All comments

ggruccio

Hi @DanielRod,

There may be a way to do this with fewer steps, but here's how I would approach it. Append a value that you can use to group on. It looks like every four rows you have a break and then the next record, so use the multi-row formula tool to go back four rows and add +1. Then summarize on that field and concatenate the values. The rest is clean-up. I've attached a workflow that does this.

See the attached workflow below:

answer row question.yxmd

DanielRod

Hi @ggruccio,

Thank you for your help with this and for building this workflow for me. Since I'm quite new to Alteryx, I have a couple questions.....

1. Why did you add 2 Text Input tools if I only have 1 document? Is there an underlying reason?

2. I should be getting about 1200 rows but when I upload the document which have around 4000, it says that the limit is 1000, and by the end, when it gets to the output tool, it only gives me 250 rows.....Is there something I should be doing to achieve a higher number of rows?

Thank you ever so much

DanielRod

Hi @ggruccio ,

Thank you for your help with this and for building this workflow for me. Since I'm quite new to Alteryx, I have a couple questions.....

1. Why did you add 2 Text Input tools if I only have 1 document? Is there an underlying reason?

Thank you ever so much

P.S.: Sorry for the post duplication

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703