+ Reply to Thread
Results 1 to 4 of 4

sorting data from auction clerking sheet

  1. #1
    Registered User
    Join Date
    08-13-2018
    Location
    66002
    MS-Off Ver
    365
    Posts
    2

    sorting data from auction clerking sheet

    I have this clerking sheet that i have been using, and i have tried using pivot tables, but i can not get the information to show like i need it to. the line # is the number we assign the the item won (col a), consignor (column B) bid and qty, then who it was sold to. i want to consolidate buyers purchases. for example in the sheet attached, frank bought more that on thing, and i would like that to show up in a table, telling me what frank bought, with a total amount due. I would also like a consignors report of what consignor sold what.

    My next "want" for this sheet... is i have an area at the top of clerk sheet where i enter the data, for current item being bid on. i fill it out hit enter, and it populates below and keeps a running tab of everything i enter.

    I've spent a few weeks trying to figure it out and i fold.
    Attached Files Attached Files

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,904

    Re: sorting data from auction clerking sheet

    Either I am missing something or this is fairly straightforward.

    First, I converted the data to an Excel Table. There are two reasons for this: Excel tables "know" how big they are so when data is added to them, formulas, charts and pivot tables that are built from them don't have to change; they will always reference the exact amount of data that they should. Secondly, Excel tables copy down formulas, formatting (including conditional formatting), validations, etc. automatically.

    There are two sets of pivot tables:

    The one on the Clerk Sheet has a couple of slicers associated with it. Slicers are fancy filters and are easy to implement. You can use this pivot table as sort of a dashboard. Selecting a Sold To will give you a summary of items bought by the person. Selecting a Consignor will do something similar - you can even get buyer - consignor pairings. To clear a slicer click on the funnel icon at the top-right of the slicer.

    The Pivot table sheet shows a more "traditional" or "static" layout in two separate pivot tables.

    I do not know what you mean with your next "want."

    Something I could give you is that you have another sheet and on it you have two tables: items to be sold with consignor (and line number) and another table with registered bidders.

    You pull up an unsold item from the table and sell it. When you enter the sold for and quantity, it will mark the item as sold in the items to be sold table (so it does not show up on the list), and adds it to the bottom of the table on the clerk sheet. This will take a bit of VB tough.
    Attached Files Attached Files
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,580

    Re: sorting data from auction clerking sheet

    If you are having difficulty setting up the PT, the attached file shows how you can do this without using them. I've added a new sheet (Bought_summary), on which you can select the name in B1 (you could have this as a drop-down). Then in the table below the details of items bought by that person are shown, along with a total.

    This is achieved by means of a helper column (H) in the Clerk sheet, which identifies matching records and gives each a unique identifier, and then a simple INDEX/MATCH formula in the summary sheet which picks out just the records identified in column H of the Clerk sheet.

    You could take a similar approach to the Consignor.

    Hope this helps.

    Pete
    Attached Files Attached Files

  4. #4
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Microsoft Office 2013
    Posts
    3,213

    Re: sorting data from auction clerking sheet

    Using pivot you can do this. See attach file sheet1.
    Attached Files Attached Files


    atul


    If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.

    Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".

+ 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. [SOLVED] Sorting data (dates) in one sheet and finding the sorted data in another sheet?
    By Elijah in forum Excel Programming / VBA / Macros
    Replies: 53
    Last Post: 12-28-2018, 09:59 AM
  2. Replies: 0
    Last Post: 01-09-2018, 02:38 PM
  3. Replies: 16
    Last Post: 04-20-2017, 12:18 PM
  4. sorting data from master sheet to another sheet
    By afelikian1 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-09-2016, 09:17 PM
  5. Replies: 5
    Last Post: 12-30-2014, 12:41 PM
  6. Replies: 2
    Last Post: 04-14-2012, 08:19 AM
  7. [SOLVED] form to enter Silent Auction data?
    By suezquesteen in forum Excel General
    Replies: 0
    Last Post: 08-05-2005, 12:05 PM

Tags for this Thread

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