Pivot Table Data Grouping Help

Hi all,

A colleague and I have been tasked with a project at work and we're desperately trying to get our heads around it, so I wonder if you can help.

Shortly and basically, we have a wealth of data that we need to have arranged within a Pivot Table. We need an accurate number on the number of 'items' in our data, but we unfortunately have duplicates in the data (unavoidable, and for a reason).

Is there a way we can use some formula or function that will group the duplicates into just one 'item', thereby getting an accurate amount in our Pivot Table?

Many thanks to anyone that can help, and I apologise if I make little sense!
I'll be happy to give more information if needed.

highlight the items you want to combine, right-click and select group, name the group and collapse the field by clicking the little minus sign that appears.

Hi all,

Thanks for the advice.
Sorry, I'll be a little clearer in what I'm trying to do.

I work for a college. I have a wealth of data (and I'm talking around 100,000 rows of data, not mentioning the columns) and we need to answer some questions with this data, such as how many 16-18 year-old apprentices we have in the last year and so on. The thing is, an apprentice may be studying more than one aim (Functional Skills Maths, for example) and so for each aim they'll have a new row. But for our calculations to be accurate, we want to have them as just one learner, as they are in fact one learner, regardless of the amount of aims they are studying.

Is there a way I can group this data automatically, through the Pivot Table or otherwise, without highlighting and grouping them myself? This would take weeks to do, which I unfortunately do not have.

A bit of extra info, each learner has a unique number, so is there any way I could use that in order to sort and group them and their aims together?

Many thanks, I REALLY appreciate it.