Analysing multiple rows of data for duplicate names

[Nicko88]

Hi,

First of all, I'm sorry I can't post a picture of the file I'm working with - I'm on a work computer which restricts downloads of unauthorised software...

I'm running Excel 2007 on Vista.

I have a workbook with 15 sheets of raw data - one for each of 15 years. Each sheet contains the details of artists submitting work to an exhibition in that year. The columns contain the following info:
A: Name of artist
B: Gender (there is an F if female, otherwise blank)
C: Type of submission
D-F: Further breakdown of submission
G: Whether the work was accepted to, or rejected from, the exhibition

In a number of cases, artists made more than one submission in any given year, so there are duplicate names.

I know I can manually filter the unique names, to find out how many unique artists submitted in each year (as opposed to how many works of art were submitted in each year), but I'm trying to find a way to do some more complex analysis. As a starting point, I would be interested in how I can find out:
How many unique artists had no work accepted (from all their submissions)
How many unique artists had some of their submissions accepted
How many had all their submissions accepted.
Average submissions per artist etc.

The next step would be to try and track artists over the course of the 15 year period, but baby steps first...

Given that I ultimately have 60,000 rows of data across the 15 tables, I'm quite keen to use a formula where ever possible. Is a pivot table likely to be the easier option?

Many thanks in advance for any help with this.

N

 

[Trevor G]

I would think that a number of Pivot tables to give you the different information would be a start point as you can consolidate all ranges from multiple sheets.

I would assume the layout is the same for each sheet.

So on something like a master sheet you could have multiple Pivots giving you the different results you require. The only thing to remember then is to be able to refresh all the pivots in 1 go and record some where when it was last updated.

 

[Nicko88]

Thanks Trevor.

The layout is indeed the same for every year. I think you're right about using pivot tables. Argh.

Many thanks,

Nick