+ Reply to Thread
Results 1 to 8 of 8

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

  1. #1
    Registered User
    Join Date
    07-07-2019
    Location
    london
    MS-Off Ver
    2013
    Posts
    13

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

    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

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,250

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

    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.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Registered User
    Join Date
    07-07-2019
    Location
    london
    MS-Off Ver
    2013
    Posts
    13

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

    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

  4. #4
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

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

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

    See if the attached pivot table meets your expectation.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  5. #5
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,250

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

    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

  6. #6
    Registered User
    Join Date
    07-07-2019
    Location
    london
    MS-Off Ver
    2013
    Posts
    13

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

    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

  7. #7
    Registered User
    Join Date
    07-07-2019
    Location
    london
    MS-Off Ver
    2013
    Posts
    13

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

    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.

  8. #8
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

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

    make the pivot table first.

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

    see an example in the attached file.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 13
    Last Post: 06-09-2017, 12:57 AM
  2. Replies: 1
    Last Post: 05-28-2017, 09:41 PM
  3. Replies: 3
    Last Post: 06-14-2016, 12:43 PM
  4. [SOLVED] Multiple Macros to hide rows dependent on Yes/No answer on the same sheet
    By fatbloke83 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-22-2012, 10:43 AM
  5. Replies: 5
    Last Post: 03-26-2012, 10:42 AM
  6. Get Vlookup to look at multiple Rows for the answer
    By KDuxbury in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-12-2005, 11:05 AM
  7. [SOLVED] in a line of three possible answers how to prevent multiple answer
    By RichardZ in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-07-2005, 03:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1