Ask Your Question

How to delete rows with 1st column value repeating and move their values in other columns?

asked 2019-04-01 14:40:35 +0100

OfficeLibre2 gravatar image

updated 2019-04-01 14:42:15 +0100

Hello, I have a table with some numbers, where: -1st column is filled with numbers, some of them repeat -other columns contain 1 number in each row How to automatically delete duplicates in 1st column, so numbers in other columns would remain assigned to proper numbers in the 1st column? I.e. if the table were:

1 2
1   3
2 5
2   4

after this operation it should be:

1 2 3
2 5 4
edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2019-04-01 16:52:06 +0100

Opaque gravatar image

updated 2019-04-01 16:52:51 +0100


the only way without writing a macro, is to use a pivot table (as far as I can imagine). Assume to original data look like in you example: image description

Define a pivot table using the follwing layout:

image description

And the resulting pivot table will be:

image description

Note: You may copy values from youe pivot table to some other place and delete the pivot.

Caveat This only works as intended, if what I called "Index" here, has only 1 value for a specific column. Otherwise pivot table will sum up.

edit flag offensive delete link more

answered 2019-04-02 18:00:07 +0100

OfficeLibre2 gravatar image

updated 2019-04-02 18:01:06 +0100

Thank you very much, it works. But I would suggest enable selecting more than one of available fields to drag&drop at once (i.e. with Shift button). I have only 10 columns in that table, but someone may have much more them - maximum sheet size is quite large...

edit flag offensive delete link more


Hello OfficeLibre2, please do not add comments to an answer as an aswer to your own question (that's why there's an add comment funktion below of an answer)

Regargding your suggestion I would suggest enable selecting more than one of available fields to drag&drop at once (i.e. with Shift button). This is in fact an enhancement request, which needs to be addressed to (People here are almost all users like you and this is not the right place to address enhancement request).

Finally, if my answer solves your question, it would be nice, if you mark my answer as correct by clicking the grey checkmark left of the beginning of my answer (will turn to green then).

Opaque gravatar imageOpaque ( 2019-04-03 09:32:25 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-04-01 14:40:35 +0100

Seen: 150 times

Last updated: Apr 02 '19