[SOLVED] Calc: Macro for removing duplicates and sorting

[kipana]

Hello guys,

I need help creating a macro that would sort entries by a specific column and then remove rows that contain duplicate entries in another column. From time to time, I have to process a large number of small datasets and the workflow is very similar, so I was wondering if this could be done with a macro, instead of doing it all by hand. Here is how it is usually done:

1. Create a new calc file.
2. Paste data into the file. The number of columns is always 4, the number of rows may be anything from 10 to 1000+ (an example is attached).
3. Sort everything by column C, descending order (Data->Sort / Sort by Column C, Descending)
4. Filter out duplicate entries in Column A using the Data->Filter function of Calc.

One of the quirks is that duplicate entries in column A may have different values in column C. In this scenario, I would like to keep the row that contains an entry with the largest value in column C and remove rows with other duplicates.

If anyone could help with this, it would save me tonnes of time in the future. Thank you!

[JeJe]

If you make a start then we can help if you get stuck...

[kipana]

If you make a start then we can help if you get stuck...

I would love to, but I am very far from even basic programming and I am afraid that investing a lot of time into learning programming languages will not do much good, since I can't really apply it that frequently. I have tried using the "record micro" option in calc, but that does not seem to work for filters.

[JeJe]

Someone might be willing to write it all for you, who knows. I can start it. 1) is

Code: Select all

Doc = Stardesktop.loadComponentFromURL("private:factory/scalc", "_blank", 0,array())

[RPG]

I think it is easy to do with consolidate or a pivottable. Use in both case MAX.

[JohnSUN-Pensioner]

Paste your list from A1, press button and copy result

[kipana]

Someone might be willing to write it all for you, who knows. I can start it. 1) is

Code: Select all

Doc = Stardesktop.loadComponentFromURL("private:factory/scalc", "_blank", 0,array())

That's what I am hoping, thank you for the part.

I think it is easy to do with consolidate or a pivottable. Use in both case MAX.

Thank you for trying. I may be explaining this incorrectly, but that's not exactly the outcome I need and creating a table like that would actually require more steps. I do not need to perform any calculations on data, I just need to sort rows in the descending order, using values in column C, and, if there are duplicate entries in column 1, remove the rows with these "extra" duplicates (leaving the entry with the largest value in column C).

Here is the same example file once it has been processed, this is the outcome I am looking for.

[JohnSUN-Pensioner]

You probably didn’t notice that there is an error in your algorithm? In the sample result, you have two rows of tulipanes en /state/ - duplicates, one of which must be deleted.
The fact is that you need to sort the list by two columns at once - by A, and by C

[kipana]

You probably didn’t notice that there is an error in your algorithm? In the sample result, you have two rows of tulipanes en /state/ - duplicates, one of which must be deleted.
The fact is that you need to sort the list by two columns at once - by A, and by C

Thank you for pointing this out, I have noticed that after I have posted the file.

The filter function in your file works like a charm, it does remove all duplicates, thank you. It does not seem to sort the results, values in Column C are presented in a seemingly random order, but I guess I can simply record another macro for that.

[JohnSUN-Pensioner]

I guess I can simply record another macro for that.

No need, it's done

[kipana]

I guess I can simply record another macro for that.

No need, it's done

Wow, you're amazing! This is exactly what I need, thank you!

Do you happen to accept donations for your work?

[Villeroy]

This is a 100% database task and a database makes this by far easier than anything else.

Add a first row of column headers to your list and save the spreadsheet to a dedicated directory as dBase (*.dbf)
menuFile>New>Database...
[X] Connect to existing database
Type: dBase
Point to the directory
[X] Register database
Save the database document.
menu:Insert>Query(SQL View...)

Code: Select all

SELECT DISTINCT "Column A", "Column B", "Column C", "Column D" FROM "Sheet1"

Replace the quoted column names and the table name "Sheet1" wwith your actual names.
Save the query, save and close the database.
A query is like a spreadsheet formula but instead of a single value it returns a whole table
.----------------------
You can linkk this sorted, unique record set to a spreadsheet:
Hit F4 (menu:View>Data Sources) and browse your database.
Drag the query icon into a spreadsheet. The result is a linked database range which can be refreshed via menu:Data>Refresh
.----------------------
You can drag this into a Writer document as well.
.----------------------
You can edit the table right in the data source window

[kipana]

This is a 100% database task and a database makes this by far easier than anything else.

Add a first row of column headers to your list and save the spreadsheet to a dedicated directory as dBase (*.dbf)
menuFile>New>Database...
[X] Connect to existing database
Type: dBase
Point to the directory
[X] Register database
Save the database document.
menu:Insert>Query(SQL View...)

Code: Select all

SELECT DISTINCT "Column A", "Column B", "Column C", "Column D" FROM "Sheet1"

Replace the quoted column names and the table name "Sheet1" wwith your actual names.
Save the query, save and close the database.
A query is like a spreadsheet formula but instead of a single value it returns a whole table
.----------------------
You can linkk this sorted, unique record set to a spreadsheet:
Hit F4 (menu:View>Data Sources) and browse your database.
Drag the query icon into a spreadsheet. The result is a linked database range which can be refreshed via menu:Data>Refresh
.----------------------
You can drag this into a Writer document as well.
.----------------------
You can edit the table right in the data source window

Thank you for contributing and providing an alternative.

Being the dummy that I am, I am going to have to stick to the solution offered by JohnSUN-Pensioner, it is fully sufficient for my purposes and the amount of work that needs to be done. Nevertheless, I really appreciate everyone trying to help.

[Villeroy]

So you prefer the complicated solution which you can not maintain?

[kipana]

So you prefer the complicated solution which you can not maintain?

I'm not here to argue or prove anything, honest. I do not store these processed files, I just extract the pieces I need to move on, so I do believe that I should be fine with the provided solution. Thanks again for trying to help.