Spreadsheet Help

shopbot

Please can I have some help in making a formula? It's for a Google Spreadsheet but I find that most Excel commands work too.

I have a spreadsheet where each column has the set list from a gig. Each column represents a different gig. Each cell represents a song name.

I'd like to be able to make a further two columns which have the song name and next to it the amount of times it's been played.

I've managed to find a formula from another forum that does it for a single column. I've not managed to adapt it for multiple columns though:
=query(query($A$1:$A;"select A, upper(A) where (A is not null and A<>'')";0);"select Col1, count(Col2) group by Col1 order by count(Col2) desc label count(Col2) 'count'";1)

Any help gratefully received!

SB

andydiysaver

if for example you have your gig columns left of H column and in H you have a song name you want to query in H2 from a list in the D coulumn, put in I2




=COUNTIF($D:$D,$H2)


however many times the song in H2 occurs in D column the gets total


cheers

andydiysaver

or multiples ... = SUM(COUNTIF($D:$D,$H2),COUNTIF($C:$C,$H2))


...all instances of song H2 in C and D

shopbot

Thanks Andy - much appreciated!

That solution is good but it relies on inputting the song names. Let's say there are a lot of song names.

Is there a formula where it will populate the song names column automatically?

Thanks again
SB

Finknottle

shopbot wrote: »

Thanks Andy - much appreciated!

That solution is good but it relies on inputting the song names. Let's say there are a lot of song names.

Is there a formula where it will populate the song names column automatically?

Thanks again
SB

Is it an absolute requirement for you to have each gig in its own column?

If so, I think I still managed to do what you ask in Excel using some play data, BUT the formulas are a PITA to write and maintain as your number of gigs (and therefore columns) expands. If it were my own data I wouldn't trust the formulas to remain error-free over time.

Alternatively, if you're able to enter your data so that all the songs appear in one column, (any column, doesn't need to be Col.A) e.g. as below...

Col. A ......Col. B
============ ============
Songs .......Gigs

Blue Sky.....Gig 1 details
Deep Sea
Fast River
Old House
Black Forest
Nice Cake
Some Things
Well Hot
Stone Cold
Last Chance


Blue Sky.....Gig 2 details
Stone Cold
Some Things
Hard & Fast
My Laundry
Nice Cake
Fast River
Another Song
etc.


... then Excel is MUCH happier and you can easily harness the built-in power of the spreadsheet to maintain a simple pivot table which automatically populates itself and expands and updates an alphabetical summary list of songs with the number of times each song has been played.

You wouldn't have to write or maintain any formulas, and Excel can easily be told to ignore any blank rows.

Excel allows something like a million rows of data so you probably wouldn't run out of room to expand your entries of set lists.

Sorry, I have no clue what capabilities Google spreadsheets have, maybe they can do this easily. I only know Excel.

Hope this helps.

F

shopbot

Thanks Finknottle.

Each gig doesn't have to be in a separate column. I've now changed format so the Columns are Date, Venue, Song, Notes.

I'll need to work through your suggestion of a pivot table however in the meantime I did the following: Filtered by Song Name. Pasted these into a separate sheet. Used a script to remove duplicates and then the empty spaces. I then used andy's formula to get the song counts I needed.

Thanks again!

SB

Finknottle

shopbot wrote: »

Thanks Finknottle.

Each gig doesn't have to be in a separate column. I've now changed format so the Columns are Date, Venue, Song, Notes.

I'll need to work through your suggestion of a pivot table however in the meantime I did the following: Filtered by Song Name. Pasted these into a separate sheet. Used a script to remove duplicates and then the empty spaces. I then used andy's formula to get the song counts I needed.

Thanks again!

SB

OK, well done.

If you were using Excel, you could use "Advanced Filter" which includes options to select "Unique records only" and "Copy to another location" which basically automates your chosen method and might make it easier if you have to keep repeating/updating.
Good luck!

F