Version: 7.3.4.2 (x64) / LibreOffice Community
Build ID: 728fec16bd5f605073805c3c9e7c4212a0120dc5
CPU threads: 4; OS: Windows 10.0 Build 19044; UI render: Skia/Raster; VCL: win
Locale: ru-RU (ru_RU); UI: en-US
Calc: threaded
Good time of the day everyone!
After inserting a block into an Excel table from another table, this block is duplicated!
How to fix it?
Please do not suggest the ODS format, since the application (to import the table) requires exactly xls.
You have selected the entire row in which the source is to be pasted. It should be duplicated to the end of the spreadsheet. Just select the top left cell of the target range then Edit > Paste Special > Paste Special > Shift Cells > Down
Let me ask you, is this really the regular behavior of the application? What is the rationale for duplicating a block?
It is how spreadsheets behave. Excel has an extra menu item Insert Copied Cells which moves the cells and pastes just one instance.
If you paste instead into a taller column of blank cells then Excel will duplicate to the end of the range.
If you paste into a wider row Excel will behave differently and only paste a single copy. This seems to be inconsistent behaviour.
Maybe you can ask for an enhancement for Calc to also have a Insert Copied Cells in the menu, How to Report Bugs in LibreOffice - The Document Foundation Wiki
I don’t have Excel handy to check. But … I can not believe in such logic. In fact, my original tables are somewhat larger. They take up multiple screens to scroll through. I noticed this extra insert by accident, looking at the tables to the end to the right. Couldn’t understand where the extra blocks come from? I can not believe the meaning of this behavior, sorry.
See this simple example: you put a formula in one cell, then copy the cell (or the formula), select a column, and paste - expecting that your formula is duplicated.
According to the settings of the paste dialog box, I expect that: the copied block will be pasted “as values”; the copied block will be inserted into the prepared place from empty lines (i.e. the target table will not be affected); the copied block will be pasted once (no options hint at any duplication). Best Regards!
Then, I suggest you to use Excel. Nothing else is compatible with the old, binary, never standardized .xls file format (even the Excel IS NOT).
You can try to use the “strict” version of the newer .xlsx file format. The Excel has a setting to change the property of the Save procedure from the default “transitional” version of the OOXML to the “strict” version.
While I agree with your opinion the file format is of no concern here. The xls will be converted to internal LibreOffice-data and saved after the problem of @Frants happened to xls. Not to recommend but saving as .ods would not change anything on his problem.
Thank you for your help! I don’t have Excel to test this situation.
But, I don’t understand, where does the format (ODS, XLS or another) have to do with it. I am working with clipboard.
I saved the documents as ODS. The result is the same.
I saved the documents as XLSX. The result hasn’t changed.
If, when inserting a block, only a cell is in focus, the block is inserted normally,
the bottom lines just shift.
If a row is in focus when inserting a block, the lower rows are also shifted, but the block is inserted twice.
OK! Maybe this is useful to someone. I’ll just remember that when inserting a block, I should avoid focusing the row. Best Regards!
Not twice, but in the entire row or column selected (as @EarnestAl wrote).
Please, share a simple sample file where you have pasted as you say. Thanks.
I can not reproduce the problem with
Version: 7.3.4.2 / LibreOffice Community
Build ID: 728fec16bd5f605073805c3c9e7c4212a0120dc5
CPU threads: 4; OS: Linux 5.4; UI render: default; VCL: x11
Locale: de-DE (de_DE.UTF-8); UI: en-US
Calc: threaded
This inserts 16 rows and 9 columns at A3:I21 and pastes the correct data without overwriting anything.
At the beginning of this topic, in my first post (at the bottom of it), there are three links: to the video, to the source table, to the target table. After watching the video, you can see that the block is being duplicated. If this is not duplication, then what is? Why is the block inserted twice? Although…maybe I see squares where everyone sees circles :). Best Regards!
Perhaps someone did not notice that during the insertion of a block, I right-click on the target row number (and not on the cell).
This selects the whole row of 256 cells as target range. xls is limited to 256 columns. Column 256 is IV. Your surplus data reach until column IV because Calc fills up the whole target range with pasted data. The 20 year old versions of Excel I used to know did exactly the same.
Thanks to you and everyone who took part in the discussion. I admit my ignorance, because of which I wasted so much of your time. But now I know a little more about Excel and Calc (I hope not to forget it in half a year :). For me, the discussion is over. Best Regards!