Pivot table analysis question - freq. of occurrence

[vbacoder]

Hi everyone,

I have a pivot table with a list of items in different rows on the left/y-axis side and years along the top (x-axis side). The table is populated with the frequency of occurrence of each item in the list - e.g. if Item A was found 5 times in the year 2010, there would be a 5 in the row for Item A and column corresponding to the year 2010. If there are no occurrences then the cell will be blank.

What I would like to do is to find the find the year of the first occurrence of each item in the table. Foe e.g. if Item A had no counts in years leading up to 2001, with at least one count in 2001, then the first year of occurrence would be 2001. Just can't think of an efficient way to do this.

Any help would be very much appreciated! Thank you very much in return.

vcoder

 

[BrianMH]

If the pivot table is based on a table object in 2007 or above I would add an additional column with a formula to find the first year for each item. It could be as simple as sorting by the value you want to search on and the date and then doing a vlookup.

 

[vbacoder]

Thanks for your reply and suggestion, Brian.

The problem I am facing is how to find the first year for each item. I have several thousand items on the left of the table and a matrix of counts (frequencies of occurrence) by year in the body of the matrix. To do the sort I would need to manually apply this (or construct a macro to do this) for each item, which is not practical. I need a formula that can read off the year (running sequentially along the top of the table) for each item based on the first occurrence of a non-blank cell when moving from left to right (because the years are increasing in the same direction).

A picture' worth a thousand words so here's a sketch of the problem. The first year in which Item A appears is 2004, the first year that Item B appears is 2002 and the first year that Item C appears is 2003. I just don't know how to code this, let alone ensuring the cells are evaluated in the left to right direction.

Columns-->2001-----2002------2003-----2004--...---20XX
Item A--->blank-----blank-------blank-------3----...-----4
Item B--->blank-------2----------1----------5----...-----8
Item C--->blank-----blank--------3----------2----...-----1

Many thanks for your help.

vcoder

 

[vbacoder]

So basically I would like to scan each row in the above table and return the first year in which each item (A, B, C... etc.) recorded a count other than 'blank'.

 

[BrianMH]

Assuming your years started in A1 and and went to E1 and your data was directly under it. Enter this formula as an array formula. If this is a table in excel 2007 or above the formula will automatically copy down. Name the column appropriately and it will show up in your pivot table.

=INDEX($A$1:$E$1,MATCH(1,(A2:E2>"")*1,0))

 

[Fazza]

Maybe make a pivot table with the year as a data field (in the middle of the table, not a column field at the top like currently), set to return minimum.

 

[vbacoder]

Thank you Brian and Fazza for your kind help. Both approaches worked!

I'm very greateful for your help!

 

[Fazza]