Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Merging Cells in Only Some Rows

torbenbillow
6 - Meteoroid

Hi,

 

Does anyone know how I could merge cells in only a limited number of rows? I have a very large excel sheet where some of the cells have been split into multiple columns and therefore shifted to the right. 

 

I don't want to merge columns for the entire sheet, because it is only a limited number of cells/columns that are affected. 

example.PNG

The format of rows 750-752 is correct. Notice how in rows 753 and 754, columns 2, 3, and 4 should be combined into one column (column #2), column #5 should then be shifted to column #3, column #6 should be shifted to column #4, etc. Basically, in rows 753 and 754, there are 2 extra columns at the end because of this split/shift. 

 

So this issue is about both merging cells and shifting cells, but not across the entire spreadsheet. 

 

Any help is appreciated. This issue is from a system-generated report that I get frequently, so I'm trying to make the solution as replicable as possible. 

5 REPLIES 5
danilang
19 - Altair
19 - Altair

Hi @torbenbillow

 

What consistent criteria can we use to determine if column 2 is split or not?  Is it something like there's data in column X because of the shift and no data in that column for non shifted rows?

 

Also any chance of getting sample data

 

Dan

torbenbillow
6 - Meteoroid

Hi @danilang, 

 

Thanks for the question. 

 

I know column 2 is split if the subsequent columns have leading spaces. In my example, columns 3 and 4 have leading spaces because they're supposed to be part of column 2 as one sentence. But also, to your point, another way of determining if it's split is looking to see if there is data is two columns at the end (columns M and N). If there is data present in these columns, it means something was incorrectly split, causing everything to shift to the right. 

 

Thanks!

danilang
19 - Altair
19 - Altair

Is it always column 2 that gets split or can it be any column?  Also,  is it always spilt into 3 columns or can it be more or less?

 

Dan

danilang
19 - Altair
19 - Altair

Hi @torbenbillow

 

Assuming it's always column 2 and it gets split into 3 columns, the following workflow shows the idea.  You'll need to adjust it based on the number of columns in your actual report

 

1. Start by adding a record id. 

2. Split based on leading space in cell 3, for the affected rows(top branch)

3. Merge col 2, 3, and 4 into 2.

4 Union all based on column position 

5 resort

6 clean up . this is where you specify which columns are extra based on your actual data

 

 

Solution.png

 

 

Dan

torbenbillow
6 - Meteoroid

That helps a lot. Thank you!

 

Labels