Getting data organized before using formulas is the good idea.
The test steps - LibreOffice 7.5.2.2
OS: Windows 11 Home
Download from the developer's website: https://www.libreoffice.org/download/download-libreoffice
1. In the picture above, duplicates are ID 556 and 523. I selected the table area by selecting cell A1, then hold down a shift key and click cell D9.
Activate 'Standard Filter', see picture below.
Set values, see picture below.
- Options: check 'No Duplicates' and 'Range contains column labels'
The result is ID 556 and 523 show only one record. In the picture below, row 5 and 8 are hidden.
To see the original data, change ID to none and uncheck 'No Duplicates'.
2. To show duplicates only, select a table. Click a target column, in this case is A.
Activate 'Conditional Formatting', see picture below.
Set the values, see picture below.
The result is duplicates have a red background.
Sort data, select a table, activate 'AutoFilter'.
The result is below.
Column heads have drop-down menus.
At column A, click menus, select 'Sort Ascending'.
The result is below.
Click column A, activate 'Standard Filter'. See the picture below for setting values.
Filed ID - Background color: red.
The result is below.
Only duplicates will show in the table.
You can delete the rows that have duplicates without messing others by selecting rows, right click at row and select 'Delete Rows'.
The result is below.
Click column A, activate 'Standard Filter' then click 'Clear' and click 'OK'.
The result is below.
The other data is not deleted.
3. If you want to delete one record of ID 523 and one record of ID 556, you can do it.
Stay at step that shows duplicates only, see picture below.
To get rid of hidden rows, copy the table and paste to cell A1 of the new sheet.
Right click then insert sheet.
In the new sheet, click column A and activate 'Standard Filter'.
See picture above for setting values, then click 'OK'.
The result is below.
Copy the contents without column headers.
Append to the table that all duplicates are deleted by pasting.
The result is below.
You have one record of ID 523, one record of ID 556 and the other records.
The test steps - LibreOffice 7.5.2.2
OS: Windows 11 Home
Download from the developer's website: https://www.libreoffice.org/download/download-libreoffice
No comments:
Post a Comment