Calculating nr of books a certain author has published

[wolverine2710]

I'm new to excel but googled a lot today but was unable to find th
answer. Hopefully someone can supply me with a formula/solution or ca
point me in the right direction. Its hard to explain so I start with
snippet of a (condensed) spreadsheet.

sheet 1
Author Book paragraph
author1 book3 1
author2 book2 3
author1 book2 10
author1 book3 4
author2 book2 1
author3 book3 10

Sheet 2
Author nr_of_books_published
author1 2
author2 1
author3 1

What I'm trying to accomplish is to calculate the number of uniqe book
a certain author has published. In the example above for author1 thi
should be 2 and for author2 this is 1 and for author3 this is also 1. I
sheet2 the input for the formula which calculates the 'nr of boo
publised' is colum1 (author), the formula is in colum2 where also th
output is shown. The data is in sheet.

Wolverine271

 

[Spencer101]

I'm new to excel but googled a lot today but was unable to find th
answer. Hopefully someone can supply me with a formula/solution or ca
point me in the right direction. Its hard to explain so I start with
snippet of a (condensed) spreadsheet.

sheet 1
Author Book paragraph
author1 book3 1
author2 book2 3
author1 book2 10
author1 book3 4
author2 book2 1
author3 book3 10

Sheet 2
Author nr_of_books_published
author1 2
author2 1
author3 1

What I'm trying to accomplish is to calculate the number of uniqe book
a certain author has published. In the example above for author1 thi
should be 2 and for author2 this is 1 and for author3 this is also 1. I
sheet2 the input for the formula which calculates the 'nr of boo
publised' is colum1 (author), the formula is in colum2 where also th
output is shown. The data is in sheet.

Wolverine2710

Hi,

I'm sure there are in-cell formula ways of doing this but it's far t
early in the day for my brain to come up with one.

However, in the attached I've provided a simple way of solving thi
problem by using a couple of simple helper columns.

I've added notes within the file that should help explain.

Let me know if this helps or not and feel free to ask if you need any o
it explaining further.

S

+-------------------------------------------------------------------
|Filename: Wolverine2710 Example.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=570
+-------------------------------------------------------------------

 

[wolverine2710]

Hi,

I'm sure there are in-cell formula ways of doing this but it's far t
early in the day for my brain to come up with one.

However, in the attached I've provided a simple way of solving thi
problem by using a couple of simple helper columns.

I've added notes within the file that should help explain.

Let me know if this helps or not and feel free to ask if you need any o
it explaining further.

S.

Thanks a lot for the solution and the fact you provided me with
working excel sheet. Forgot to mention I use Excel 2010. I very muc
appreciate the effort. It gets the job done and I can even understan
what your are doing.I personally like to keep my (work)sheets 'clean
(no hidden cells if not really needed because I will distribute th
file) so should you happen to find an 'in cell' formula when your brai
is up to it it would be again very much appreciated.

Of course if someone else has found a solution for this little puzzle
would be glad to hear from. You can take Spencers sheet as a startin
point because all data for testing is already in it

+-------------------------------------------------------------------
+-------------------------------------------------------------------