Merging Cells in Only Some Rows

[torbenbillow]

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. 

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. 

[danilang]

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]

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]

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 · Accepted solution]

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

Dan

[torbenbillow]

That helps a lot. Thank you!