Become a MacRumors Supporter for $50/year with no ads, ability to filter front page stories, and private forums.

JPM42

macrumors 6502
Original poster
Oct 21, 2007
275
7
Hi everyone,

I currently have a large set of Classical metadata in a spreadsheet that I'm sorting out for someone. This spreadsheet includes all participant and composer information, but from looking at the composer information on several tracks, it looks like many additional composers are included on tracks who shouldn't be listed (i.e. librettists, etc.). So what I'd like to do is use a formula that can count the amount of times the name "Composer" appears amongst the participants relative to each track title. This way, if more than one composer is identified, I can filter and work on fixing those areas. To provide some clarity, I've created an example spreadsheet using The Beatles:

Screen Shot 2018-02-17 at 8.59.57 AM.png

As you can see, I've included participant and composer information. What I'd like to find is a formula that could spit out in column E the number of composers. For example, for "Hey Jude" there should be 2, for "Something" and "Octopus's Garden", just 1. Unfortunately, my Numbers/Excel knowledge isn't too great, so I was hoping if anyone had any ideas on if this is achievable.

Thanks!
 

ShinyDren

macrumors member
Jun 25, 2010
67
23
Ambitoysterous, USA
Hi everyone,

I currently have a large set of Classical metadata in a spreadsheet that I'm sorting out for someone. This spreadsheet includes all participant and composer information, but from looking at the composer information on several tracks, it looks like many additional composers are included on tracks who shouldn't be listed (i.e. librettists, etc.). So what I'd like to do is use a formula that can count the amount of times the name "Composer" appears amongst the participants relative to each track title. This way, if more than one composer is identified, I can filter and work on fixing those areas. To provide some clarity, I've created an example spreadsheet using The Beatles:

View attachment 751588

As you can see, I've included participant and composer information. What I'd like to find is a formula that could spit out in column E the number of composers. For example, for "Hey Jude" there should be 2, for "Something" and "Octopus's Garden", just 1. Unfortunately, my Numbers/Excel knowledge isn't too great, so I was hoping if anyone had any ideas on if this is achievable.

Thanks!
Just realized when submitting this reply that I didn’t included the blank header column that was in your screenshot. You should still be able to follow along.

The easiest way that should get the information you want is to use a combined column on your main sheet and an additional sheet to do the formula work on.

First create a “Combined” column and put this formula in cell D2:
Code:
=CONCATENATE(A2,"—",C2)

Then fill down to copy the formula to the rest of the column cells (with cell selected, hover the mouse over the bottom of cell near the middle, drag the yellow dot down).

Create a second sheet. Name the columns “Combined (Unique)”, “Count”, “Track Title”, “Role”.
In cell A2 put:
Code:
=Sheet 1::Table 1::D2
In cell A3 put:
Code:
=OFFSET( Sheet 1::Table 1::$D$1, SUM($B$1:B2)+1,0,1,1)
In cell B2 put:
Code:
=COUNTIF(Sheet 1::Table 1::D, A2)

Then fill down cells A3 and B2 for the number of rows needed (you can add more rows later if you need).

In cell C2 put:
Code:
=INDEX(Sheet 1::Table 1::$A,MATCH($A2,Sheet 1::Table 1::$D,0),1,1)
In cell D2 put:
Code:
=INDEX(Sheet 1::Table 1::$C,MATCH($A2,Sheet 1::Table 1::$D,0),1,1)

Fill those down to match the other two columns. You can use these columns to Filter the results to only show what you want at the time (“Composer” or whatever else).

Add a Conditional Highlighting rule for column B to change the background if it is greater than 1 to highlight the ones to check.

There are a couple of requirements for this method to work correctly:
  1. The main data sheet (Sheet 1) must be sorted on the new “Combined” column (ascending/descending doesn’t matter). This is so identical values are next to each other.
  2. The second sheet (Sheet 2) can not be sorted (otherwise the formulas break). But you can use the Filter option to only show what you want (after inputting and copying all the formulas).

See attached Numbers file with working formulas.


Specific source references:
https://discussions.apple.com/message/26276909#message26276909
https://discussions.apple.com/message/26557289#message26557289


More Info:
If you are interested in how this would be in Google Sheets you can check this linked file:
https://docs.google.com/spreadsheets/d/1VjW59-BSCtl1zmzmBZ34Bng-IdF2hgTyaOd2EIBeXeU/edit?usp=sharing

The Google Sheets version does not have the requirement #1 because it has other formula options available.
 

Attachments

  • Composers-test.numbers.zip
    102.1 KB · Views: 69
Register on MacRumors! This sidebar will go away, and you'll see fewer ads.