Find missing values

Topic

New Reply

Out of curiosity, over the last 4 years I have kept track of which songs were sung out of our hymnal at church. I have been asked to give some facts about our hymnal and I would like to find out which songs have not been sung. I am totally lost on how to get that figure. I would also like to know which song is sung the most, and perhaps the top 10 or 20 that are sung. The numbers in the spreadsheet are the numbers of the song in the hymnal. I don’t have the hymnal with me right now, but I think there are around 660 songs. Can someone help me?

Reply | Quote

Replies

Your spreadsheet would be better if you included a Unique values lookup table for Song Names and Numbers.

From your data I firstly typed a list of all 660 song numbers, I then created a counting formula to find the song number in your listing and count how many times it appeared. I then determined what count was the highest number, and created a formula to show all those rows that matched. I then did the same with any zero’s to find which songs had not been sung. To get the Top 10 you can use the filter tools on column D to get this.

See attached updated file.

Regards,
Maria

Reply | Quote

Thank you. That’s what I needed.

OK. I’m not 100% sure I understood this part. In other words, I should have the actual song name (in text) in another column? Or, is Column C the ‘Unique Value’ you were mentioning?

Reply | Quote

Thank you. That’s what I needed.

OK. I’m not 100% sure I understood this part. In other words, I should have the actual song name (in text) in another column? Or, is Column C the ‘Unique Value’ you were mentioning?

You are welcome – glad I could help. What I meant about the unique values, was that if you had instead of my Column C a full list of song numbers in Column C and Names in Column D then once you have determined which song numbers were sung most or not at all, then you could easily lookup the name of the song.

Regards,
Maria

Reply | Quote

Understood. Thanks again.

Reply | Quote