Duplicate entries in spreadsheet

[Nifty]

I have a spreadsheet with peoples names, dates and one or both badges earned. Some have achieve multiple times.
I would like to see only the most recent badge earned for either the one or both badges. Can somebody help please.TEST.ods

Sample uploaded with thanks

[keme]

A spreadsheet is not optimal for this kind of task, but it is usually doable without too much trouble. The first thing that springs to mind is a pivot table setup.

There is one thing you need to submit to get more explicit help with this: A sample file.

• NOT a picture of how it looks on your screen. A proper ODS format file.
• NOT the original file with persons’ real names in it. Make a “mangled file” where names are replaced. This is a public place, so you don’t want to post confidential info.
• Depending on the size of your list, a full dataset may not be required, but make sure your mangled list demonstrates the offending duplicates, and also that the size of submitted data is sufficient to extract sensible results.

[keme]

Using a pivot table for the extract.

See this file. Does it do what you want?

The pivot table is not “live”. You must right click and select “update” when new entries are added.

EDIT: A somewhat refined solution, where I disabled sums (which does not make any sense anyway) and enabled drilldown (so you can doubleclick a date to show other relevant entries for this shooter).

[Nifty]

Hi - Thank you very for taking the trouble to look at my problem. When I add my entire spreadsheet the Pivot Table wants to sort on the SERVICENO column.

Nev

[keme]

No trouble. With a clean data table like yours it is pretty straightforward, so it was no big effort.

It seems that in a pivot table you cannot select which summary field to sort on, it is always left to right priority. You can right click the pivot table to edit settings, and drag the SERVICENo field down below names to have the list sorted alphabetically by name.

I assumed that SERVICENo was the only reliable “unique identifier”, where duplicate names may occur. Column can be hidden if it is not needed in the extract, and it can be removed if you are certain that there will be no name confusion (two persons with the same name).

[Nifty]

Again many thanks for you trouble - learning all the time. I must say your assistance has helped me greatly as I have this data is base and now have a spreadsheet with just the information I need. regards Nev

[schiavinatto]

@Nifty, SHOOTDATE column sorted ascending, column NAME = SURNAME concatenated with FIRSTNAME.

In M8 Inverted search, and in M10 multiple search function (Macro)

Test file

---

ATTENTION: If you would like to give more details to your question, use edit in question or add a comment below. Thank you.

If the answer met your need, please click on the ball to the left of the answer, to finish the question.