Occurences in Excel???

[Sleeslag]

I am seeking help with my spreadsheet. I asked before but couldn't get the answer I was looking for at:

http://www.mrexcel.com/forum/showth...to-work-with-occurences&p=3182142#post3182142

I am not even sure if this is possible...but here is the low down...

I think it is occurences. Let me try to explain what I am trying to achieve. I have a spreadsheet that has a heading of each week of 2012 beginning with column B. This data contains 100 rows of song titles and artists in the same column.

So each week I pull the info and update the columns of the week so I am on 6/30/2012 and currently at column Z

What I would like to display in column A are the song titles and artists and the number of occurrences it appears in the spreadsheet.

So if a song is

Starships, Nicki Minaj and it appeared in the top 100 for 8 weeks in 2012. Then I would like Starships, Nicki Minaj (8) or similar...even make two columns have this settings because I would like the most occurences appear at the top of the column. I am looking to have Column A to populate on it's own. Since this is only half the year, I have not acquired all the data. Each week I will be adding a new column. So I am hoping that Column A will continue to update as I add the data each week and to include this for the entire worksheet (year). So here is an example of two weeks of the top 3 songs on my spreadsheet... 14-Apr 21-Apr We Are Young, fun. Featuring Janelle Monae We Are Young, fun. Featuring Janelle Monae Boyfriend, Justin Bieber Somebody That I Used To Know, Gotye Featuring Kimbra Somebody That I Used To Know, Gotye Featuring Kimbra Glad You Came, The Wanted <tbody> </tbody> so column A would have something like this: Somebody That I Used To Know... (2) We Are Young, fun, ...(2) Boyfriend, Justin Bieber (1) Glad You Came, The Wanted (1) Again keep in mind, I want to be able to sort so that the longer the song stays in the top 100 that I can keep them at the top. So if two columns are necessary that is fine. <tbody> </tbody>

<tbody>
</tbody>

Count If functions do not look like they work for me because it seems that I would have to put in the name of each artist so I was looking for something a little more automated.

But I am an extreme basic Excel (2010) user and I have no idea how to create such a task.

 

[Tinbendr]

So each week I pull the info

Import or entered manually?

I think the solution provided should have been adequate. You have to add a new column after A. Then the formula goes into each cell. You'll have to set the range all the way to Col Z though.

[COLOR=#333333][LEFT]=A2&" ("&COUNTIF([/LEFT][/COLOR][COLOR=blue][LEFT]$C$2:$Z$100,A2[/LEFT][/COLOR][COLOR=#333333][LEFT])&")"[/LEFT][/COLOR]

You may have to change the max row number. $z$MaxRowNumber.

But, I'm not sure how this will sort.

You could put just the count in column b and sort B, then A.

=[LEFT][COLOR=#333333]COUNTIF([/COLOR][/LEFT][COLOR=blue][LEFT]$C$2:$Z$100,A2[/LEFT][/COLOR][COLOR=#333333][LEFT])

and copy down.[/LEFT][/COLOR]