Merge cells similar to excel

[JP_1]

Hi there - I was hoping someone could help with a problem I am facing.  I have some data which I am combining, where Personnel may feature on more than one row.  I want to add a new column (which I know how to do) to be completed offline out of Alteryx but I want the cells in the column to be merged to one cell for each Personnel, exactly like the Cleared [Yes/No] column below.  It is because the Cleared column will be populated manually out of Alteryx and the entry going into that column (i.e. Yes or No) applies to each individual rather than each Client/Contract.  Therefore, I am trying to avoid a situation where we manually have to drag entries down that column as Personnel could appear thousands of times.  Appreciate any help that people can provide.

Personnel	Personnel ID	Client	Contract Name	Cleared [Yes/No]
JP	123	Client1	Contract1
JP	123	Client1	Contract2
JP	123	Client2	Contract3
XG	562	Client3	Contract4
XG	562	Client3	Contract5
XG	562	Client1	Contract2
XG	562	Client3	Contract5
XG	562	Client4	Contract6
IA	901	Client2	Contract3
AC	492	Client1	Contract2
DG	674	Client5	Contract7
DG	674	Client5	Contract8
DG	674	Client5	Contract9

[atcodedog05]

Hi @JP_1 

Its not completely clear can you provide some sample input and expected output it will help us get a better understanding of the usecase.

[JP_1]

The sample input is just more detailed data than shown in the attached pictures which I have Summarized down.  It is the additional column with merged cells by Personnel that I am struggling with.  I have attached a picture as the table I was drawing in the box wasn't formatting correctly.

[messi007]

@JP_1,

I suppose the input look like this :

You can use a multi-row formula to take the first value for each Personnel

Please see below the final output

Attached the workflow,

Regards,

[JP_1]

Thank you for your response.  

As I mentioned, the Cleared column will be populated manually out of Alteryx.  It will not be coming back into Alteryx.  I am looking to create a column with empty merged cells and merged per Personnel (similar to the drawing I have attached).

[danilang]

Hi @JP_1 

Edit:  I stand corrected.  Brilliant,  outside-the-box solution from @Christina_H. (See below)  I'd upvote this more than once if I could.

The merging that you see in Excel is formatting that is applied by the Excel engine.  There is no way to output data from Alteryx with either merged rows or merged columns that don't involve merging the data into a single cell.  To do this you'd have to merge the data in all the cells in the row like this.  Data from rows 2 and 3 from your initial table are in a single cell per column separated by line breaks.

Personnel	Personnel ID	Client	Contract Name	Cleared [Yes/No]
JP	123	Client1	Contract1
JP JP	123 123	Client1 Client 2	Contract2 Contract3
XG	562	Client3	Contract4

Dan

[messi007]

@JP_1,

May be something like this could help :

Then you use render tool to create the excel file

Regards,

[Ben_H]

Hi @JP_1,

What you're suggesting isn't really good practice, merged cells within excel tables cause all sorts of problems.

I also don't know if Alteryx can actually generate merged cells on output to Excel.

Wouldn't it make more sense to simply have a separate table with a single row for each staff member with their "Cleared" status and just look that up into your bigger table?

Regards,

Ben

[Christina_H · Accepted solution]

This can be done with nested tables, it renders to Excel with merged cells.  This version doesn't have the headers yet, those would need to be created separately.

[atcodedog05]

Hi @JP_1 

Here is my take on it. I was facing border issue gave it another shot was able to resolve it now by using pipe | (I guess I should have given it another shot before only 😅).

Workflow:

Output:

Hope this helps : )