Excel Filtering

New Contributor

Hi guys, I'm new to excel so any help is appreciated, I feel this is a fairly straight forward question but I haven't been able to work it out.

 

Essentially I have a spreadsheet with information on 2500 people, all of them with a unique ID number. All I would like to do is to be able to search up if a person appears 5 times or more. I know there's a feature where you can input a number and see if it appears 5 or more times, but that means I'd have to do everyone individually. Is there a feature that highlights if ANY individual number comes up or is repeated 5 or more times?

 

Thanks in advance.

3 Replies

@sam735 I'd recommend you to look into the use of a pivot table, where you put the Unique ID in the Rows field as well as in the Values field (summarise by Count), and then filter it as you like.

 

Now, you may never have seen this but there are plenty of tutorials on line. Just google for "pivot table Excel". Attached workbook contains a very small and simple (unfiltered) example. 

@Riny_van_Eekelen 

 

Hi Riny, thanks for your help. I've worked out how to use pivot tables and for 80% of the values it works but as can be seen in my screenshot, for one of the values, the pivot table has the count at 8 but then when I go to search for the number it can only find 7 instances of it. Do you know what I'm doing wrong? Thank you so much for your help!

@sam735 Double-click on the 8 in the pivot table. It will open a new sheet with the underlying items. Should come up with 8 records. Can't really tell if something is wrong with the file.