+ Reply to Thread
Results 1 to 3 of 3

Data Sorting Issue

  1. #1
    Registered User
    Join Date
    11-11-2010
    Location
    west virginia
    MS-Off Ver
    Excel 2010 Developer
    Posts
    1

    Exclamation Data Sorting Issue

    Hey Ladies and Gents.,

    This is my first thread here on the Excel Forum. So happy I found a community like this.

    Here is my issue. I have attached the actual document I need sorted. Not asking you to do this for me, but as a reference and to explain how to do this for future uses. I work for a retail store that sells alcohol and tobacco. One of our vendors has asked that we give them a spreadsheet laying out the store name, transaction time, receipt number/transaction number, item description on receipt, UPC, quantity (qty), price, pack type, and extended price.

    Here is my dilemma. I need this by tomorrow (Friday the 12th (luckily not the 13th or I would be really screwed!))

    Here is dilemma 2...my Excel knowledge is limited although I am an IT-IS Manager for this retail business and I am ashamed to admit I could not figure this out. If you look at the attached spreadsheet. You will notice there is a transaction number column. When I use the subtotal function. It gives me a subtotal of the quantity of items for each transaction. I need it to cut the duplicates and give me a quantity on one row rather than the same item 10 times on 10 rows. Keep in mind there are more than one item on these transaction numbers and I want to account for those as well. I have also attached how I want this to look for better understanding reasons.

    In advance, I appreciate your help and look forward to hearing from any of you. Thanks so much!

    I have uploaded the files. The file I want you to look at is in the compressed zip folder. the finished product is the .xlsx file. Thanks again.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    10-17-2010
    Location
    Nottingham, England
    MS-Off Ver
    Excel 2003, 2007, 2010, 2013, 2016, 2019, 365
    Posts
    286

    Re: Data Sorting Issue

    Explore pivot tables - they are excellent for this sort of analysis.

    Insert > Tables Group > Pivot Table

    For ease of use, put it on a new sheet (default option)

    You'll get a set of panes on the right-hand side. Drag Transaction no to the row labels box, Extended price to the summary box (greek sigma). You get a list of transaction with total value.

    Experiment with dragging other features to other boxes and see what happens - if you don't like it, just drag them back to the top pane.

    You'll be amazed what you can do with it - totals by store, average per month, all sorts!

  3. #3
    Valued Forum Contributor
    Join Date
    10-17-2010
    Location
    Nottingham, England
    MS-Off Ver
    Excel 2003, 2007, 2010, 2013, 2016, 2019, 365
    Posts
    286

    Re: Data Sorting Issue

    I should have said, before you start select any one cell in your data.

+ 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