+ Reply to Thread
Results 1 to 44 of 44

pivot table not listing things in one row

  1. #1
    Registered User
    Join Date
    12-20-2012
    Location
    los angeles
    MS-Off Ver
    Excel 2010
    Posts
    29

    pivot table not listing things in one row

    I am trying to set up a pivot table and everytime a way i do it, it is not happening the way i want..
    below is the excel worksheet i am getting.

    Title City State 1/24/2013 1/25/2013 1/26/2013 1/27/2013 1/28/2013
    Monkey Spring Hill FL 7:10, 10:15. - - - -
    Monkey New York NY 5:40, 8:00, 10:30. - - - -
    Monkey New York NY - 5:40, 8:00, 10:30, 12:45 AM. - - -
    Monkey New York NY - - 3:20, 5:40, 8:00, 10:30, 12:45 AM. - -
    Monkey New York NY - - - 3:20, 5:40, 8:00, 10:30. -
    Monkey New York NY - - - - 5:40, 8:00, 10:30.
    Monkey Oak Creek WI 6:45, 9:10. 6:45, 9:10. 6:45, 9:10. 6:45, 9:10. 6:45, 9:10.
    Monkey Kailua HI 10:10 - - - -
    Monkey Kailua HI - 8:00, 10:10. - - -
    Monkey Kailua HI - - 1:30, 8:00, 10:10. - -
    Monkey Kailua HI - - - 1:30, 8:00. -
    Monkey Kailua HI - - - - 8:00, 10:10.
    Monkey Tempe AZ 12:00, 2:20. - - - -
    Monkey Pleasant Grove UT 9:30 - - - -
    Monkey Tucson AZ 12:10, 2:40, 5:10, 7:45. - - - -
    Monkey Tucson AZ - 12:25, 3:00. - - -
    Monkey Tucson AZ - - - 12:25, 3:00. 12:25, 3:00.
    Monkey Lancaster CA 2:15, 4:50. - - - -
    Monkey Victorville CA 12:10, 2:45, 5:15, 7:50. - - - -


    i want to have 1 row for each city and state and in that same row have the times for each day.
    is this possible.. is the worksheet am working from corupted?

    I want to put it into tabular form..and am doing in classic pivot table. just as i need a worksheet with the all time time put in one row.



    Title City State 1/24/2013 1/25/2013
    Monkey Kailua HI - -
    8:00, 10:10.
    10:10:00 AM -
    Lancaster CA 2:15, 4:50. -
    New York NY - -
    5:40, 8:00, 10:30, 12:45 AM.
    5:40, 8:00, 10:30. -
    Oak Creek WI 6:45, 9:10. 6:45, 9:10.
    Pleasant Grove UT 9:30:00 AM -
    Spring Hill FL 7:10, 10:15. -
    Tempe AZ 12:00, 2:20. -
    Tucson AZ - -
    12:25, 3:00.
    12:10, 2:40, 5:10, 7:45. -
    Victorville CA 12:10, 2:45, 5:15, 7:50. -





    as you can see no problem till i get to to a city with more the one dat with a time in it..


    Please help..
    Last edited by COLTS12OG; 01-24-2013 at 10:17 PM.

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Lord please help!!

    We would love to help you with your query, but first, before we can proceed, please see Forum Rule #1 about proper thread titles and adjust accordingly...

    • To change a Title on your post, click EDIT then Go Advanced.
    • If 2 days have passed, ask a moderator to do it for you.
    HTH
    Regards, Jeff

  3. #3
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: pivot table not listing things in one row

    It would probably help if you actually attached a sample workbook so the before and after is clearer.

  4. #4
    Registered User
    Join Date
    12-20-2012
    Location
    los angeles
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: pivot table not listing things in one row

    Left work but will do tomorrow ..pretty much pivoting everything except for the dates columns. Any clue?
    When I find replace - with blank, then pivot. The same thing appears..as excel thinks there's something in these cells .I noticed some of the times in the date columns are text and some are numbers ..but all are displayed like time????
    Last edited by COLTS12OG; 01-24-2013 at 10:38 PM.

  5. #5
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: pivot table not listing things in one row

    Must say hard for me to see exactly what is messing up for you so the attachment tmw will help tremendously.

    If you are just pivoting the City and State with only one row per it seems to work fine for me on a test by placing city and state as row labels in tabular form.

    Again, maybe I don't fully see what your picture is so it will have to be when you get a sample file ready.

  6. #6
    Registered User
    Join Date
    12-20-2012
    Location
    los angeles
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: pivot table not listing things in one row

    attatached is the worksheet am working with, before I try to pivot..
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    12-20-2012
    Location
    los angeles
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: pivot table not listing things in one row

    pivot Monkey worksheet.xlsx

    the worksheet attached is after I - replace all - with blank, then insert a pivot table. then tabular and classic pivot table views.

    problem am haveing is example; city Columbia
    - there are times all all the date colums. but when i pivot i does not show on one row.
    i would like to have one row for each city and show every day with timee(s)in one row.

    why is it showing blank spaces?

  8. #8
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: pivot table not listing things in one row

    Mabye something like this?
    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.

  9. #9
    Registered User
    Join Date
    12-20-2012
    Location
    los angeles
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: pivot table not listing things in one row

    Not really the way i want to see it..

    i want a colum for each date at the top (as a header)
    and i would like to see one city name only once..
    Ex. one row says new york -then state then dates. and times all in one row.


    Ty for the help though!!

  10. #10
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: pivot table not listing things in one row

    Maybe you can use a filter in my example (and filter on city).

  11. #11
    Registered User
    Join Date
    12-20-2012
    Location
    los angeles
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: pivot table not listing things in one row

    If i filtered city? want to see all the citys.. but just once.
    Ex. i want to see only one new york/

    Thank you again for the help!

  12. #12
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: pivot table not listing things in one row

    Looking at your sample data, New York consists of five rows and four that are filled.

    How do you expect to get on New York?

    And maybe further, what do you want to see?

    Can you provide a sample output for say just New York?

  13. #13
    Registered User
    Join Date
    12-20-2012
    Location
    los angeles
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: pivot table not listing things in one row

    Pretty much to condence the information.Going to use these work sheet, pivot it. and use it as a better work sheet..

    I there a problem with the file or times in the dates? do i need to format them?
    do i need to add a zero to the blank to pivot this?

  14. #14
    Registered User
    Join Date
    12-20-2012
    Location
    los angeles
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: pivot table not listing things in one row

    Monkey worksheet- what i want it to look like..xlsx

    Sorry am might not be explaing it well.
    Attached is a prerry good example of what i would like to see..

    I am all out of cluew with this. I thought a pivot table is what would help me with this.. is there maybe anouther way to display what i want to do here?


    Thank you!!! TY!!!

  15. #15
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: pivot table not listing things in one row

    responding to your reaction in #11.

    You can also filter on 1 item. (e.g. New York).

    I added an file to show you.
    Last edited by oeldere; 01-25-2013 at 02:46 PM.

  16. #16
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: pivot table not listing things in one row

    Quite honestly what you are looking for may not be able to be done thru a pivot table, nor do I think a formula configuration would help.

    It seems a VBA solution is required. I don't have time for a VBA solution right now, but maybe oeldere has something to offer...

  17. #17
    Registered User
    Join Date
    12-20-2012
    Location
    los angeles
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: pivot table not listing things in one row

    So i would not need to pivit this worksheet for the get go?
    I can just filter?? If so, can you show me an example? TY!! i am so lost.

  18. #18
    Registered User
    Join Date
    10-25-2012
    Location
    California, USA
    MS-Off Ver
    Excel 2007
    Posts
    71

    Re: pivot table not listing things in one row

    Ok, without some overly painful VBA programming I do not think this is possible in Excel. That said, I took your data, changed the entry format to Company, State, City, Date, Times and moved it into an Access database. It has a simple tool that generates cross tab queries like a pivot table with some differences. The resulting query can than be exported or copied into Excel if you need to share it. Attached is the sample database.

    Monkey(TestDB).zip

    Best of luck.

  19. #19
    Registered User
    Join Date
    12-20-2012
    Location
    los angeles
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: pivot table not listing things in one row

    Wow I thought excel could do everything..lol. am shocked.
    Still need to get this worksheet to look the right way.

    Where can i get access? is it free?



    Thank you, TY, TY

  20. #20
    Registered User
    Join Date
    12-20-2012
    Location
    los angeles
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: pivot table not listing things in one row

    I don not have Mircsoft Access so can not see the above attachement.
    Can you post a excel attachement.. just so I can see if it is indeed what I am looking to do?

    Ty TU. IF it looks good to me I will get access on the double!

  21. #21
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: pivot table not listing things in one row

    HI COLTS12OG

    No...although you may already have it as part of Office. It is a different language.
    Where can i get access? is it free?
    And yes, this is possible in Excel. Give me a bit of time...let me look at it. Get back to you by Monday Morning at the latest...possibly sooner.
    Last edited by jaslake; 01-25-2013 at 09:29 PM.
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

  22. #22
    Registered User
    Join Date
    12-20-2012
    Location
    los angeles
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: pivot table not listing things in one row

    Thank you all, I really need this info..
    i just never thought it would be this hard to get..

  23. #23
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: pivot table not listing things in one row

    hi COLTS12OG, i did a macro for you where there's a button for you to press in "yty". there's also another sheet called "Formulas" based purely on formulas. i assumed that 1 state would only be linked to 1 city in your file? you can use the formulas if so

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  24. #24
    Registered User
    Join Date
    12-20-2012
    Location
    los angeles
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: pivot table not listing things in one row

    File says it is enable to run the macro and that is locked by user.
    if this does work can you explain how i would go about starting the process?

    Ty again!

  25. #25
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: pivot table not listing things in one row

    @colts12og

    Maybe you missed my solution with an filter in #15.

    I can open the file from benishiryo.

    Maybe you are not allowed to work with macros?

  26. #26
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: pivot table not listing things in one row

    HI COLTS12OG

    How many DATE Columns will your actual worksheet have? Your sample file has 4.

  27. #27
    Registered User
    Join Date
    12-20-2012
    Location
    los angeles
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: pivot table not listing things in one row

    the date columns actually never end... but I need to only see 10 day out...i just delete the rest of the blank columns.
    Th rows can vary, in each worksheet am given. sometimes there are 6000 rows.

  28. #28
    Registered User
    Join Date
    12-20-2012
    Location
    los angeles
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: pivot table not listing things in one row

    @oeldere,

    #15 what are the step you are using and what what are you asking to filter?

    I am able to use macros..

    Moderators Note:
    • As per Forum Rule #12, please don't quote whole posts unless necessary-- it's just clutter...Thanks.
    Last edited by jeffreybrown; 01-26-2013 at 02:20 PM.

  29. #29
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: pivot table not listing things in one row

    HI COLTS12OG

    So Columns N to the right should be deleted and your Date Columns will be D to M?

  30. #30
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: pivot table not listing things in one row

    @colts12OG

    To filter,

    Excel 2007 => data => filter

  31. #31
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: pivot table not listing things in one row

    HI COLTS12OG

    This Code works on your Sample File. It'll need to be reworked a bit to handle your Real File. Please provide the information requested.

    The Merge Rows Code was adapted from Code written by Jindon here
    http://www.excelforum.com/excel-prog...e-columns.html

    Run the Code with Sheet yty active. Let me know of issues.
    Please Login or Register  to view this content.

  32. #32
    Forum Contributor
    Join Date
    11-11-2012
    Location
    Muscat, Oman
    MS-Off Ver
    Office 365
    Posts
    521

    Re: pivot table not listing things in one row

    Did u try removing sub totals?

  33. #33
    Registered User
    Join Date
    10-25-2012
    Location
    California, USA
    MS-Off Ver
    Excel 2007
    Posts
    71

    Re: pivot table not listing things in one row

    Sorry about the delay getting back to you. I was off the net for the weekend.

    Table1_Crosstab.xlsx

  34. #34
    Registered User
    Join Date
    12-20-2012
    Location
    los angeles
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: pivot table not listing things in one row

    John,
    i see two codes in the one above you provided?
    And when i enter in the code excel asks me which one i want to run?
    I choosen both to see which one works.. Merge cell seems to work a little better, i believe,

    Is there a reason why you put two codes in?

    Let me know., as far as the code, so good so far.. just want to make sure which one i use before i start using it on multiple worksheets...

  35. #35
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: pivot table not listing things in one row

    HI COLTS12OG

    Run the Delete_Empty_Rows Code...it does it's thing (deletes all empty rows) and then Calls Merge_Rows which does the actual merge of the records.

  36. #36
    Registered User
    Join Date
    12-20-2012
    Location
    los angeles
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: pivot table not listing things in one row

    john,
    having a promblem on a worksheet that is longer then the sample here..I am gong to be usually using a worksheet with 7000 rows.

    can i sent it to you directly..? and you let me know if there needs to be a new code added?

  37. #37
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: pivot table not listing things in one row

    HI COLTS12OG

    What's it doing (or not doing)...getting an error message? Taking a bit of time to run? 7000 rows of data is a lot of data to crunch...it'll take a bit of time.
    You can try zipping the large file and then attach and I'll look at it.

  38. #38
    Registered User
    Join Date
    12-20-2012
    Location
    los angeles
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: pivot table not listing things in one row

    in my new works sheet it works like the formula runs out of space.. i have about 7k rows down.. is this code able to handle that?
    Last edited by COLTS12OG; 01-28-2013 at 05:01 PM.

  39. #39
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: pivot table not listing things in one row

    HI COLTS12OG

    Does the Code work in your sample file?

  40. #40
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: pivot table not listing things in one row

    Try this one
    Please Login or Register  to view this content.

  41. #41
    Registered User
    Join Date
    12-20-2012
    Location
    los angeles
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: pivot table not listing things in one row

    Jindon,
    Yes, THIS WORKS!!
    I have tryed it in multipe worksheets and the information is now all merged!! thank you so much..

    only promblem i have is that the code above is for sheet "yty" and when i use it on anouther spreadsheet i have to chang yty to the new name of the spread sheet.
    a = Sheets("yty").Cells(1).CurrentRegion.Value

    Is there a way to get the code to be general.. so that i can record a marco and and use this code for what ever worksheet I am using???

    Thank you again to all for helping me excel in excel!!!

  42. #42
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: pivot table not listing things in one row

    Hi Paul

    You can try this...change this
    Please Login or Register  to view this content.
    to this
    Please Login or Register  to view this content.
    This assumes you have the Source Worksheet Active at the time you invoke the Code.

    This is a really neat piece of work by Jindon.

  43. #43
    Registered User
    Join Date
    12-20-2012
    Location
    los angeles
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: pivot table not listing things in one row

    HELL YES!
    You guys Rock!

  44. #44
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: pivot table not listing things in one row

    Hi Paul

    If you like it...click the Star.

    If this resolves your issue please mark your Thread as SOLVED.

    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:
    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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