getting started with rows that have multiple answers or just one answer ???

[steve78]

Hi Forum, your help would be greatly appreciated, i am unsure how to start with a new pivot table.

i have a record of attendees to each event that i would like to total in a pivot

each row could be unique number starting from 1
and then the next column would be the entry DAY or EVE
and then would be the data . so in column 3 i would need to enter the names of the attendees, it may be anything from 3 names up to 20 names.

how i have it in my mind is i would need a new row for EACH attendee OR put a, attendee name in a new colomn.
Just dont know how to get started with it all.

the end result would be to run a PIVOT TABLE to see for eg how many times JAMES has attended in the EVE.

As said, any help id massively appreciated. ( i have read some tutorials and have some knowledge but just unsure how to START recording it )

thanks

Steve R

[MarvinP]

Hi Steve,

It is always easier to help with questions when we have a sample worksheet you've attached.

Now, from your discription, I believe you are correct. You need to have 4 columns of ID, Date/Time, (single) Name.
I'm thinking you need to do Text to columns on your name column and then UnPivot your data.

UnPivot is a feature in Power Query which you can download for 2010 Excel or newer.

[steve78]

Thank you very much MARVIN P, it is most appreciated.

in respect of my data i have at the moment. it is just in hand written books. i am actually creating the table in EXCEL for the first time.

i have attached 2 ways that i know of how i can record the data and what i am looking to get out of it.

please let me know which option is the best way, or indeed if there is another way

many many thanks

steve r

[oeldere]

Option 1 is the best format to make a pivot table.

See if the attached pivot table meets your expectation.

[MarvinP]

Hi Steve,

I agree option 1 would be best. See my Pivot of your data attached. Is that what you need?
PT example for Robert.xlsx

[steve78]

Yes Indeed OLEDERE . that is exactly what i was looking for thanks.

there is one small difference i need to make. The OFFS should be deducted from the ON'S to create a NET VALUE. (being sum of ON - OFF). i think i can do that by creating a calculated field . i have forgotten how to do that but i can research it.

so after this is done how do i make the catchy looking charts with the drop down arrows where you can just select a name and it will give me all the data. i assume this is a PIVOT CHART.
IF SO Do i need to create the chart BEFORE i create the table (like the one you did) or do i create the pivot table FIRST then the chart.

many thanks

steve

[steve78]

Wow, thanks marving. 2 responses that are spot on to what i need.

i have responded to OELDERE with how i would want the info in what i think is a PIVOT CHART.
but i have the basis of how to create the raw data and the table now.

so many thanks to the both of you, cheers.

[oeldere]

make the pivot table first.

after that the pivot graph => analyse => privot graph

see an example in the attached file.