Excel Chart on two different date columns

Question

Greetings all,

Please see the following photo showing an excerpt from an excel data.

I would like to show the count of RFQ and count of CD per month/year together on a line chart using pivot table. (sounds easy? no, please continue reading....)

What I have managed to do so far is create a pivot table and add RFQ received date (second column in attached photo) in my pivot Rows field then grouped on month/year.

Then added RFQ # and CD # to the Values field (with count value field setting) in pivot table, but here comes the issue. The count for CD# would be wrong because as you can see from the photo in line 259 the RFQ received date month/year Sept/2013 grouped in the Rows field of the pivot table would count CD# 122 Rev#0 which has month/year date of Jan/2014??

I tried, searched but to no avail. All what I'm trying to do is count RFQ# and CD# for month of Jan, Feb, March, etc..... and display two lines on one chart, one line for RFQ count per month and another one for CD count per month.

Thank you...

Answer 1

Excel 2010 with free Power Query Add-In.

Compatible with Office 2013 Pro Plus.

Rearranged data into a PivotTable friendly format with Power Query.

No formulas, no macro needs to be written.

http://805fbd0dee7e8ba6e8e2-24c8ffd6d2ff5b45792f1baccf09ac8d.r61.cf1.rackcdn.com/01_24_15.xlsx

Answer 2

Hi,

Upload your workbook to OneDrive and share the link of the workbook here.

Answer 3

Hi,

Thank you for choosing Microsoft Community,

For issues related to Excel file containing Pivot table, you may post your question

in Excel ITpro forums, click on the link below to post your query for better

assistance.

http://social.technet.microsoft.com/Forums/office/en-US/home?forum=excel

Thank you.