Need Pivot for Three Datasets with Count of Yes & No

[naveeddil]

Dear All,

I have 25 Districts, 36 Questions with Yes or No.

I want the districts Name / Questions on Either sides but in the values i need the Count of Yes & Count of No.

Can you please Help.

I'm Attaching the excel sheet for easy reference.

Thanks in Advance

[FDibbins]

Im not sure I understand what your need here is? Unless your actual data does not match your sample, you will only EVER have a count of 1 or 0 in each YES/NO column for each district?

District 1 Q1 will either be 1 yes or 1 no, so what are you trying to count?

What would a sample answer look like?

See if this is what you want...
C4=IF(INDEX(Data!$D$2:$AM$34,MATCH('What I Want'!C$2,Data!$B$2:$B$34,0),MATCH('What I Want'!$B4,Data!$D$1:$AM$1,0))="No",0,1)
D4=IF(C4=1,0,1)
both, as a "set", copied down and across

[naveeddil]

Dear Ford,

Actually for same 25 Districts and Same 36 Questions i'll be receiving data daily for next two weeks that mean i'll be receiving dozens of Yes/No for each question.
I want district-wise Yes / No count for each question in a pivot.

I've added some more data and added their manual required answers in "what i want sheet". Kindly review it.


Thanks

[teylyn]

Hello,

in order to create a pivot table with the questions on the rows, your data needs to be in a different structure. As it is, the data is already a pivoted report. I used Power Query to unpivot it and turn it into a simple list. Then I created a pivot table that has exactly the format you describe.

The green table is the result of the Power Query, the Blue table is the pivot table.

2015-06-04_14-31-30.png

cheers, teylyn

[FDibbins]

@ teylyn, depending on the browser being used (like the 1 I am currently using - IE9), some pic formats (png is 1) do not show in here, they just show a place holder. So, for instance, at work, all I see is the place holder, but at home, using FireFosx or Chrome, I will probably be able to see that pic

[naveeddil]

Dear teylyn,

The dataset will be lengthy (and a bit time consuming for me to copy/paste here) but seems helpful and data will be as in a good shape after pivot.

Can you please tell me how to unpivot data via Powerquery.

[teylyn]

@ FDibbins, IE9 can display png files just fine. Your company may have a specific setup that causes a conflict.

naveeddil, download Power Query for Excel from this page and read the Getting Started on this page. There are many pages on the web that explain how Power Query works.

Once you have installed it and activated the Add-in with File > Options > Add-Ins > Com Add-ins, you will see the Power Query ribbon.

On your "Data" sheet, click any cell of your table. Then click the Power Query ribbon > From Table. This will open the table in the Power Query editor.

Select the Date, District and Site Name columns. In the Transform ribbon, click the dropdown for "Unpivot Columns" and select "Unpivot Other Columns".
Change the name of the Attribute column to "Question".
Select the Value column and click the Transform ribbon > Format dropdown > Trim (this will remove leading and trailing spaces from your data.
Give the Query a name in the right hand side properties panel, then on the Home ribbon click Close and Load.

This will insert a new sheet with the cleaned up data. Each step is recorded in a code language called "M". The M for the steps is:

Please Login or Register  to view this content.

On the Data sheet you can now add more data or change existing data. For example, there is one column that has numbers instead of Yes or No. After you have changed the Data table, go back to the green query table, right-click a cell inside and select "Refresh". This will quickly re-run all the steps you did in the Query Editor and load the new data into the sheet.

You can build a pivot table as describe and refresh it after the query table has been refreshed.

Let me know how you get on.

[FDibbins]

@ FDibbins, IE9 can display png files just fine. Your company may have a specific setup that causes a conflict.

What we may or may not have at my specific work location is is hardly relevant, I am not the only member that has this problem. I was merely pointing out that not everyone can see images posted on this forum.

See http://www.excelforum.com/suggestion...romised-2.html from about post 22 onwards, and also here http://www.excelforum.com/the-water-...-the-like.html

edit: I have just checked at home using IE11, and while it still does not show the pic, it does show a clickable link to it

[teylyn]

So png images posted in this forum cannot be seen in all modern browsers?

I suggest you take that up with the forum owner. He should be outraged. PNG files are a web standard and the only problem older versions of IE have is that IE did not honor the transparency in PNGs. But that was back in IE 6 days.

I need to be mindful of the lack of knowledge of the tech team and post images as attachments. Is that what you're saying?

No thanks.

Other VBulletin forums don't have problems with png files. I won't adjust to the tech team's skill level and make their problem go away. That's tackling the problem from the wrong end.

[FDibbins]

I could not agree more, the problem has already been raised, as was mentioned in those 2 thread links I provided. And no, I am not saying you need to be mindful of any knowledge lack, I was just pointing out a limitation that we have to work within. How you choose to deal with that, is obviously up to you

[naveeddil]

Any Help on thread
http://www.excelforum.com/excel-char...ml#post4123648
is appreciated

I need to unpivot some data

Thanks