Merging multiple rows into one

[koralynah]

Hi there, 

I have a data set where there are multiple rows for the same request number. Each row details provides a sequence step and time processed. I would like to combine all of the sequences and times processed into a single row (by request number). I've tried doing so by using the transpose/cross tab tool but it's not getting me anywhere. Any suggestions? 

[NicoleJohnson]

I am guessing that by keeping your Sequence ID in your Group By selections for the Cross Tab tool at the end of your original workflow, you might have been ending up listing them twice, when you're really capturing that info in the column headers and don't still want them listed out individually in your records below. Try adding this:

1. Transpose tool: Key fields = Request #, Action By, Comment Text (exclude Sequence ID)

2. Filter out any Null/0 values in the new "Value" column

3. Use the Cross-Tab tool to put the columns back at the top: Group by Request #, Action By, and Comment Text... Column Headers = Name... and Values = Value column.

Does that work for you? :) If not, perhaps a screenshot of your actual workflow or a sample with dummy data in a text input might help resolve!

NJ

[Philip]

I agree with @NicoleJohnson, checking the Sequence ID in the "Group Data by these Values" in the Cross Tab is causing it to be split out to separate rows instead of keeping on a single row. You don't need to keep it checked as they are becoming your New Column Headers. Uncheck the Sequence ID in the Group Data by these Values.

[mborriero]

@NicoleJohnsonapproach is a good solution.

Attached a workflow that create a row for each request and action by, adding columns for times, comments and types (Requestor, Elr Request, etc...).

[suli]

Hi,

I think the simplest  (1 step) solution is to use a summarize tool:

group by: Request, Action By

Concatenate: Action ID

Longest (can find under String solutions): all sequence and sequence time columns.

Please find attached sample workflow.

[vhackman]

I am having the same problem except I don't have a Sequence ID column, so I'm having a harder time figuring out the best way to go about solving this.

[Adrian01]

What Suli recommended worked perfectly for me with the same issue.

[mocalvo]

@suli's idea to use the Summarize tool worked perfectly for me! I needed to merge individual emails from separate rows into one field. Thanks!

[minhdvo]

Your idea is great.

It works for my problem.

Thanks,