+ Reply to Thread
Results 1 to 19 of 19

Sort by month into two separate columns

  1. #1
    Registered User
    Join Date
    09-13-2016
    Location
    Carteret, NJ
    MS-Off Ver
    2007
    Posts
    10

    Sort by month into two separate columns

    Hi All,

    I am trying to sort data on a crystal Excel report. The report is generated by item code and gives me the date of purchase in one column and cost of item in a different column. The dates of purchase are mixed between July and August in the same column. Many items were purchased multiple times within the two months and the report shows each one.

    I need to compare the costs for each item in July vs. the costs in August. I would like one column with all the costs for July and another column next to it with all the costs for August for easy comparison. I am not so good with pivot tables but if that is the only/easiest way please let me know how it can be done.

    See attached workbook.

    Thanks!
    Attachment 538994
    Attached Files Attached Files
    Last edited by Juddk; 09-20-2017 at 10:52 AM.

  2. #2
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Sort by month into two separate columns

    Hi and welcome to the forum

    Pictures are of little value. Honestly, no one wants to re-type your data to try and solve your issue. Additionally, we would only be guessing at how your data was structured, ie. formulas, formatting, etc. Additionally, due to how some browsers behave, many of our members cannot see uploaded pictures/images. Please do not take this route.

    Please attach a sample file that represents what you have. The structure of your attachment should be the same structure as your actual data. Any proprietary information should be changed.

    Include in the attachment any code you're currently using (whether it works or not) and an "After" of what you wish the output to be.

    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.

  3. #3
    Registered User
    Join Date
    09-13-2016
    Location
    Carteret, NJ
    MS-Off Ver
    2007
    Posts
    10

    Re: Sort by month into two separate columns

    Where is quick post?

  4. #4
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Sort by month into two separate columns

    here : quick.jpg

  5. #5
    Registered User
    Join Date
    09-13-2016
    Location
    Carteret, NJ
    MS-Off Ver
    2007
    Posts
    10

    Re: Sort by month into two separate columns

    Thanks.

    Done

  6. #6
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Sort by month into two separate columns

    maybe like this with PT

  7. #7
    Registered User
    Join Date
    09-13-2016
    Location
    Carteret, NJ
    MS-Off Ver
    2007
    Posts
    10

    Re: Sort by month into two separate columns

    Thanks but I need July's unit costs side by side with August's unit costs.
    Total unit costs are not helpful to me.

  8. #8
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Sort by month into two separate columns

    Enter formula to get unique list of Item Codes in G2 and copy down
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Enter formula for JUL
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Enter formula for AUG
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  9. #9
    Registered User
    Join Date
    09-13-2016
    Location
    Carteret, NJ
    MS-Off Ver
    2007
    Posts
    10

    Re: Sort by month into two separate columns

    Thanks for helping.

    The values for the August column (column I) are incorrect (example BB6851 never cost $70.40)
    Also, it is only showing me one cost per month. Some items (like BB9121) had multiple costs per month.

  10. #10
    Valued Forum Contributor
    Join Date
    05-11-2013
    Location
    Wales
    MS-Off Ver
    Excel 2010
    Posts
    586

    Re: Sort by month into two separate columns

    Hi Juddk,

    Pivot Table attached - is this what you after ?

    Regards

    peterrc
    Attached Files Attached Files

  11. #11
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Sort by month into two separate columns

    Quote Originally Posted by Juddk View Post
    Thanks for helping.

    The values for the August column (column I) are incorrect (example BB6851 never cost $70.40)
    Also, it is only showing me one cost per month. Some items (like BB9121) had multiple costs per month.
    There is no BB6851 it is BB685I
    the same is for BB9121 it should be BB912I
    See the difference?

  12. #12
    Registered User
    Join Date
    09-13-2016
    Location
    Carteret, NJ
    MS-Off Ver
    2007
    Posts
    10

    Re: Sort by month into two separate columns

    Almost.
    But I don't want the sum of the values, I need to see each cost for each unique item separately by month. The way you separated the months is perfect though

  13. #13
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Sort by month into two separate columns

    Attach example file with AFTER, what you want to achieve. Mock data manually if necessary.

    but maybe like this one (this is like peterrc's solution but without grouping)
    Last edited by sandy666; 09-20-2017 at 12:19 PM.

  14. #14
    Registered User
    Join Date
    09-13-2016
    Location
    Carteret, NJ
    MS-Off Ver
    2007
    Posts
    10

    Re: Sort by month into two separate columns

    Yes. Definitely closer to the solution but I would like to see the dates in separate month columns. See attached
    Attached Files Attached Files

  15. #15
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Sort by month into two separate columns

    Is this what you wanted? Different shading = different formulas
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  16. #16
    Registered User
    Join Date
    09-13-2016
    Location
    Carteret, NJ
    MS-Off Ver
    2007
    Posts
    10

    Re: Sort by month into two separate columns

    Perfect!

    Thanks so much.

    I'll try to copy those formulas...

  17. #17
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Sort by month into two separate columns

    You're welcome.



    If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

  18. #18
    Valued Forum Contributor
    Join Date
    05-11-2013
    Location
    Wales
    MS-Off Ver
    Excel 2010
    Posts
    586

    Re: Sort by month into two separate columns

    Hi Juddk,

    Attached is another "version" of the Pivot Table I posted earlier.
    I've highlighted in colour the 3 Item Codes where the prices vary.

    Regards

    peterrc
    Attached Files Attached Files

  19. #19
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Sort by month into two separate columns

    here is for excel version higher than 2007 (PowerQuery)

+ 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. Macro to separate long string of characters and several spaces into separate columns
    By taylorsm in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-31-2017, 07:37 PM
  2. VBA to separate and sort multiple columns
    By paulblower in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-17-2014, 08:53 AM
  3. Replies: 6
    Last Post: 04-08-2014, 03:16 PM
  4. [SOLVED] Macro to copy 3 adjacent columns at a time to a separate worksheet & sort by Company name
    By anya1 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 04-20-2013, 06:50 AM
  5. [SOLVED] Pivot: Replace month numbers with month names and sort by fiscal year
    By jomili in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-29-2011, 02:13 PM
  6. Replies: 4
    Last Post: 05-23-2011, 09:15 AM
  7. Sort Data from one column to Separate Columns
    By Griff0 in forum Excel General
    Replies: 11
    Last Post: 06-06-2010, 08:43 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