Merging Cells in Only Some Rows

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.

Workflow

Developer

Regex

Transformation

Accepted answers

danilang

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

conditional merge.yxmd

All comments

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!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703