Jul 21 2021 07:08 PM
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.
Jul 21 2021 08:56 PM
@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.
Jul 22 2021 01:48 AM
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!
Jul 22 2021 01:57 AM
@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.