Extract data from Pivot Table (If statement & GetPivotdata)

Hi All,

I'd like to extract some data from a pivot table if a cell matches another cell. For example, if a particular company offered sale, I want to list all the years they had sale in separated by commas. I have a pivot table where the Company numbers (titled 'Company') and years (titled 'year') are in the Report Filter section.

Then, in an excel tab I have the following:

Company	Year	Sale
1		sale
2		no sale
3		sale

So, I'd like to have a macro which can input the years in Column 2 from the pivot for company 1 if Column 3 says sale.

So, I'd get something like below:

Company	Year	Sale
1	2012, 2013, 2014	sale
2		no sale
3	2014, 2015	sale

Many thanks in advance

I guess it would be easier to create new pivot table with Company and Years data and put Sale on a report filter to filter only ‘sale’ and then create macro to extract data you need.

It would be easier if you could attach file or describe in more details how does source looks like.

@wator89 - That's a good idea. In the pivot table I've Company Numbers and then years for all those companies in the Report Filter Area. So I could extract the data into a "working tab" where in column A I could have all the company numbers and in B have the years listed.

Can someone write me a macro to do the above?

Thanks

Please attach sample file with:
-pivot source sheet
-pivot report sheet
-sample output sheet

Please leave all headers, layout, etc. as it is. For test purposes you could substitute you data with some simple dummy data.