How to Tally and Rank Frequency of Occurrences of a Year in a Column

jdanniel

Member
Joined
Jul 16, 2018
Messages
84
Reaction score
0
Points
6
Excel Version(s)
MS365
May I please request some assistance with some data? My music inventory spreadsheet has a column named Year of Release. If I know the release year of an album, I enter it. If not, I leave it blank.
Is there a way to tally up the years of release, and create a list or something that shows the total number for each year, ranked from most first to least last?

For example, if the year 1969 appears 71 times, and is the year that appears the most frequently in that column, can I create something that looks a little like this:

1969 - 71
1970 - 46
1974 - 44
1975 - 43
1968 - 37

I'd also, if possible, like the option of ignoring or including empty cells.

And so on? Is there a way to do this? If so, what's the most practical method?
Thank you! Jd
 

Attachments

  • DVD-DL.xlsm
    56.5 KB · Views: 6
Power Query. You have it in Excel 2016. It’s great for producing summary tables like this and sorting them to suit.
 
Ali,
I think I got it. Would you be willing to take a look at my PQ results and tell me if I did it right? I think I did, but I'd be grateful for confirmation from an expert.

I chose to leave the "null" (empty) values alone.

I used the Group By option. If I did this correctly...Ali, this was too easy. Thank you! Jd
 

Attachments

  • Music Spreadsheet with Year of Release Count.xlsm
    199.3 KB · Views: 8
Ali,
I do have a follow-up question. It's probably simple.
I have done three Power Queries, as you can see in this most current spreadsheet. Is it possible to merge all three PQ's into one worksheet? I tried doing that with copy-and-paste, but it didn't retain the formatting. By that, I meant the header rows have filter options, which did not carry over during the pasting.

I tried to figure out how to do one power query for Artist and Year of Release. It didn't seem possible. It looked like I had to so a separate routine for the artists, and then one for Year of Release.
Am I wrong? If you could give me a nudge in the right direction, that would be great. Thank you again. Jd
 

Attachments

  • DL Music 2019.xlsm
    212 KB · Views: 9
I'll have a quick look. Don't have much time, but I'll see what I can advise. :)

Unfortunately, because the sources cannot be found, the queries are failing. That's because I'm not working on your machine. However, here's what I'd do:

1. Load the basedata into PQ.
2. Work through the steps needed to create the year query. Do NOT close and load yet!
3. In the queries panel on the left, right click the years query and click Reference - this makes a dependent copy of the years query.
4. Undo the steps in the second query and replace them with the steps for the artist query.
5. Finally, close and load. You will now have two query tables based on the same original data source.

Hope this helps!
 
I should have added that your results look about right, and yes, PQ makes some things an absolute breeze. Glad you are getting acquainted with it!
 
PS

To get all three queries into one sheet, instead of choosing Close & Load, choose Close and Load To ... and then you can specify a location. To change this after the event, right-click a query in the queries pane on the right in Excel and choose Load To ...
 
Back
Top