How to convert merged cells into ordinary cells and copy data from merged cells to ordinary cells?

[froz]

In LibreOffice Calc 5.0.3.2 on Windows 7:

Type in data in the following cells:

A2: Country1

A4: Country2

B2: Customer1

B3: Customer2

B4: Customer3

B5: Customer4

C1: Quantity

C2: 100

C3: 100

C4: 100

C5: 100

2. Mark cells A2 and A3 and click button on toolbar “Merge and Center Cells”.

3. Mark cells A4 and A5 and click button on toolbar “Merge and Center Cells”.

4. Click on C1.

5. Click on Autofilter icon from toolbar.

6. Click on the filter arrow from A1 cell and you will notice beside Country1 and Country2 also a “empty” string.
   It looks like Cals behaves like merged cells A3 and A5 are “empty”.

Is there some way to fix this problem?

I can do this manually but it can be a big pain if table is huge:

a) Click on column A.

b) Click on “Merge and Center Cells” to unmerge the cells.

c) Manually copy text from C2 to C3 and C4 to C5.

P.S. There may be more then one cells merged it really depends how many customers belongs to particular country in the data I get from my coworkers.

Thanks

[JohnSUN]

Of course you can write special macro for this task… I’m usually do so

Or we can set formula without function IF() - just =<cell above>. In this case, you need to set the option “Skip empty cells” (many thanks @pierre-yves samyn for this trick!)

[PYS]

@JohnSUN - Nice…

Just for fun, another solution with copy-paste & skip empty cells in this video (from 01:00). It is in French, but should be understandable…

Regards

[JohnSUN]

@PYS Sorry, but I didn’t see “this video is in French.” But I think I understand what you’re saying. The answer is supplemented with this embodiment

[AlexKemp]

wot video? i see no videos.

[PYS]

Oups… here is the link to the video

[JohnSUN]

Thanks for the link - I still thought that we are talking about this rarely used possibility of Paste Special

[kingexcel]

You can merge cells or rows or columns values easily using Dose for Excel Add-In:
https://www.zbrainsoft.com/excel-merge.html

[Lupp]

strong text> Is there some way to fix this problem?

Yes. Do not merge cells.

Your sheet obviously is thought to keep data in a sense database tables do. Merging cells does not comply with the purpose.

It looks like A3 and A5 is empty.

A3 and A5 are empty, aren’t they? Following your description you never entered something there. You may have a wrong understanding of “merging”. The shown cell (topmost leftmost of the “merged range”) occupies an increased area also covering the areas of the cells “merged in”. Thus some logical cells get hidden. They still exist nonetheless in the sense that they can have any content, and they can be referenced. They even can have formats which will get reinstated after a split.

I virtually never merge cells, but if I did I expected exactly the behaviour described. In addition filters do not work with hidden cells, I think. If the software changed the behaviour with this respect, users sharing my expectations surely raised protest.

(Wherever using a table to keep data make sure that every set of data contains the complete information independent of a possible sort order. Databases never regard an order of the underlying data. They “sort on query” if demanded.)

Editing1: To countermand the merging of cells select a range containing the m,erged cells you want to “unmerge” and then going either

’Format’ > ‘Merge’ > ‘Split’ or

Rightclick in the range to get the context menue > 'Split’

I did not configure a toolbar to show the ‘Merge and Centre Cells’ button, but you obviously have, and using it a second time on a range containing merged cells should also split them. (Working like a toggle button if applicable.)

Editing2: (Concerning the second part of the reworked question) See this attached example.

(Sorry! JohnSUN already answered everything. I will not delete this answer nonetheless.)

[froz]

The problem is, I have written only a sample how to produce a problem. In my case I never do merge, I just get the document already produced in this way by my coworkers and customers and I need to do some analytics on it like filters etc.

My question is: Is there any simple way of transforming merged cells into unmerged cells AND copy all of the content from top most member to all unmerged cells after it.

[Lupp]

I do not know a short solution for the second part. It can be done in a few additional steps using a helper column, however. Maybe someone else hase a more “iconisable” idea. I am not an icon enthusiast.