Spreadsheet with over 10000 rows, Need to display each unique Item only once

[AlexK888]

I have searched and tried many ways, but no success!

This spread sheet will have 2 to 3 hundred unique Items, with many Recurrences, and each recurrence can have a different Value.
The spread sheet may contain as many as 9 to 15 thousand rows with Recurrence of any given Item.

I can get the Total Value for each unique Item, and the Number of Recurrences. But since there are only 2 to 3 hundred unique Items, I need help to set it to skip the Recurrences and display each unique Item only once with respective Total Value and Number of Recurrences, so it prints 2 to 3 hundred line report.

I can only check this thread once in a few days, as I am on the road a lot >;^)

The ScreenShot should show the issue >:^)

P.S. I am using LibreOffice 7.3.72 on Linux Mint 21

Thanks a bunch in advanced

[karolus]

Hallo

Select »A4:B11000« , goto →→Data→Pivottable→create…
Drag&drop »Items« to Rowfields
drag&drop »Value« to Datafield

#edit: to count the Items
drag&drop »Items« to Datafield double-click change Aggregat_function to »Count«
#/edit
→hit the »ok« -button

[AlexK888]

Thank You for the Pivot Tables suggestion, it is most of the solution >:^) Did a bunch of tutorials, and got the basics.
Note in the new ScreenShot, I updated C2 and D2 Text of the formula to match reality, but you all know that LOL

The only question is left, why the column D that is a “COUNTIF”, is being processed as a “Sum” in column H of the Pivot Table? (F9:H14)
Safe to assume Pivot table doe not handle COUNTIF formula? Or my Pivot Table is not done correctly?

The results should match cells H5:H8 of my example; Note that, though the numbers are correct, the cells don’t contain any formulas, numbers are typed in for show and tel. Any way to fix this issue?

Thanks in advance >:^)

[karolus]

@AlexK888

Safe to assume Pivot table doe not handle COUNTIF formula? Or my Pivot Table is not done correctly?

See #edit in my answer

[keme1]

@AlexK888

Safe to assume Pivot table doe not handle COUNTIF formula? Or my Pivot Table is not done correctly?

The formula columns already sum and count, doing the same job that the pivot table is expected to do. Summing a count (count) times will give you the square of the count, as you can see.

solution

• Right click your pivot table to edit properties.
• Delete the recurrence field from the data pane.
• Insert Item field in the data fields pane.
  It should now be present in both the “row fields” and “Data” panes.
• Double click that, and in the list of aggregation operations select Count instead of Sum

When you use a pivot table, you don’t really need the columns with your formulas.

[AlexK888]

Thank you, the hint of double clicking was it, I hadn’t seen this in the tutorial sites.

So, in short, The Pivot Table is the solution, I just had to double click what was “Sum - Number of Recurrence” in the Data Fields, and choose “Count”. Now it says “Count - Number of Recurrence.”

P.S.
Folks, thank you a bunch and even more bunch. In retrospect, I should have done deeper dive in learning the features of LibreOffice. I absolutely am amazed at the hard and tireless work that has been put into this product.

Truly thank you
Alex

[Wanderer]

I’d suggest to learn to work with pivot-tables, as @karolus suggested. They are great time savers.
.
In this simple case you already did your own sum and counting. So all you need to find out is, if in the current line the “Test n” has its first occurrencie, by checking the lines above with COUNTIF.

=COUNTIF($A$3:$A3,A3)

in E3 would do this counting if you drag the formula down. Then filter all columns where this count gives 1.
.
A variant would be to use this count as condition to display your sum and count of recurrences as in

=IF(COUNTIF($A$3:$A3,A3)<>1; "" ;
        SUMIF($A$3:$A$9; A3; $B$3:$B$9)

Then you could also use a filter to show all rows with counts >0.
.
If necessary, filters can also write the filtered lines to another spreadsheet.
.
And as PS: If you’d put your data in a “little” sql-database the inly difference in your query would be “select distinct” instead of “select” for a query (and the result can be dragged to a spreadsheet easily, if you afterwards wish to continue exploring…