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

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

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

1 Like

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?

See `#edit` in my answer

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.

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

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â€¦