Work around with merged cell in Alteryx.

[KrishnaChithrathil]

I've an excel file with merged cells. Dropping the picture below.

What I'm trying to do is, after loading to Alteryx, copy the 'Name' Jane from 1 to 5. (Jane should come in all 5 row of 'Name' column). similarly, Krishna should appear in 6 to10. Until here, I did it with MULTI-ROW FORMULA TOOL. 

Same logic is working perfectly for column 'Event' and 'Time'. 

So the challenge I'm facing here is,

1) This is a sample excel. The data and number of columns are randomly created by me. For each column, I'm using separate Multi-Row Formula Tool. What we'll do if we've 100 columns with merged cells? using 100 multi-row formula tool isn't practical, right? How can I solve this?

2) When I'm using this tool, the condition is if isnull([Name]) then [Row-1:Name] else [Name] endif. That is, if a cell is null, that cell will get updated with previous row value. ie. Row-1.

So for row 11, which is not a merged cell, but an empty cell or a null value cell, this also will get updated with Row-1 value. ie. Krishna. Row-1 should only work if it's a merged cell.

These are the two challenges I'm facing. Any help would be highly appreciated.

- Krishna

[ChrisTX]

Transpose might be your best option.

This post deals with headers in merged cells, but may give you an idea how to deal with your sheet:

https://www.altertricks.com/design-patterns/design-pattern-misaligned-and-merged-cells/

And here are a few posts about multi-row, multi-field:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Struggle-in-Excel/m-p/845302/highlight...

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Applying-multi-row-formula-to-multiple...

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Multi-field-Calculations-Multi-row-Cal...

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Multi-field-Calculations-Multi-row-Cal...

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Multi-Row-Multi-Field/m-p/50403#M19125

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Multi-Row-Multi-Field-formula-field/m-...

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Developing-a-Macro-for-filling-down-va...

from Tool Mastery | Multi-Row Formula
https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Tool-Mastery-Multi-Row-Formula/ta-p...

The Multi-Row Formula Tool can only update one field per tool instance. If you would like to update multiple fields (and you feel comfortable with both the Multi-Row Formula Tool and the Multi-Field Formula Tool), try out this macro that was posted on the public Alteryx Gallery:

Multi-Row Multi-Field   by patrick digan

Chris

[KrishnaChithrathil]

Hey @ChrisTX

Thank you. I tried it with transpose with no success.

This is the output I'm expecting. Any help?

- Krishna

[ChrisTX]

Did you try a multi-row, multi-field macro from some of the other related posts?

[OllieClarke]

Hi @KrishnaChithrathil with your second problem, I don't see a way to solve it in Alteryx unfortunately. Maybe you can have an excel macro which will convert all null cells to have a space in them, or something like that, then they won't be read in as nulls in alteryx. Or add a column which flags actual nulls and then you can use that in your logic in your workflow. 

I've attached a workflow showing how you can dynamically apply the filling in of merged cells.

Hope that helps,

Ollie

[Emmanuel_G]

Hi @KrishnaChithrathil ,

Hope you're well !

Find a test a ttached with the output you want. 

The only one tool you have to edit is summarize by grouping by all the fields of file( in yellow container ). 

Let me know if it works 🙂.

Cheers !

[KrishnaChithrathil]

@OllieClarke @ChrisTX @Emmanuel_G 

The major challenge here is, when we load our data to Alteryx, the empty cell as well as the merged empty cell is showing as Null. So I'm not able to differentiate which is the empty cell and which is the merged one. 

@OllieClarke The solution provided by you is working. But we need to make changes in the requirement excel, right? I don't know whether I can do that in a customer requirement. And If it is possible can I put some space in all empty cell in one go? If I have 'N' number of columns and rows? And thanks a bunch for spending time for my topic.

@Emmanuel_G 

Hello. Than you so much. I studied your workflow. But the thing is you're using a text input, right? How you're differentiating the merged data? Please find the requirement excel screenshot below. 

Thanks,

Krishna

[OllieClarke]

@KrishnaChithrathil it will be possible to have an excel macro, or bit of VBS code which will replace nulls with space (or another specified character). If you can find the VBS to do that, then you can run it from alteryx before inputting your excel with the Run Command tool. 

There's more info on that here: https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Alteryx-Run-Command-Executing-Excel... (although that script assumes you have an .xlsm file rather than running VBS on a normal .xlsx)

Hope that helps,

Ollie

[Emmanuel_G]

@KrishnaChithrathil 

In my case, when I import an excel file with merge cells, the value of merged cells is the the first rows which enable to use Mutli-Row Formula to duplicate values below until you get new value.

Can you provide us a draft of your file with merged cells to test please ?🙂

Cheers !

[OllieClarke]

@Emmanuel_G the issue is that there are un-merged null cells which alteryx can't differentiate. I made a very simple mockup of this yesterday