Help sorting DJ playlists to find most played songs please.

**samteacher1** · 01-08-2014, 10:53 AM

Hi, I work for a large DJ company and need to compile a 'Most Popular' list of the tracks we play each month. Every week approx. 20 playlists are submitted by DJs around the country to our database. Each playlist contains about 80-100 artist and song titles. These playlists are submitted as .txt files. I'm aware that I can copy all these files into one .txt file and then open this in Excel. I have to clean the data a little at this point but I can get a long list of Artists in the first column and the respective song titles in the second column. I know to link both columns and sort by 'Artists' first and then 'Song title' second so all the Artist and Song cells stay together.

Unfortunately this is as far as my knowledge will allow me to get.

What I need to be able to do is compile a Top 200 list of the most popular tracks. Is there a way to get Excel to count up all the duplicate song titles in my spreadsheet, add the number of duplicate tracks into a third column and then sort my list numerically from highest to lowest give me the most popular tracks.

Thank-you in advance. I'm using Excel 2008 on Mac.

**ragulduy** · 01-08-2014, 11:01 AM

If your artists are in column a and songs are in column b then:

=countif(B:B,B2)
in C2 and copy down, then sort by column C and remove duplicates.

would probably be pretty close.

**FDibbins** · 01-08-2014, 11:06 AM

Hi and welcome to the forum

Im sure we can sort something out for you, can you attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

**newdoverman** · 01-08-2014, 11:10 AM

Without seeing your data, it seems to me that you could use a Pivot Table to give you the statistics that you want. The data would have to have column Titles and no blank rows. You could apply the Filter feature and delete the "garbage" from the data then create the Pivot Table.

Upload a sample of your data and a solution can be worked out for your I'm sure.