+ Reply to Thread
Results 1 to 9 of 9

Automatic Sorting

  1. #1
    Registered User
    Join Date
    07-26-2011
    Location
    MA
    MS-Off Ver
    Excel 2007
    Posts
    19

    Automatic Sorting

    I was wondering if there is a way to automatically sort from an input sheet to an output sheet.

    On the output sheet, there would be three different tables that the data went into, based off of their category

    Categories include Office Expense - Meals, Office Expense - Groceries, and Office Expense - Office.

    I want to be able to set up an Input table in a separate tab, where you can just type in Date, description (Meals, Groceries, or Office), and amount.

    From there, I'd like the hard entered numbers dumped into a breakdown sheet with the three different tables.

    Is this feasible?

    Thanks in advance!Excel Help 3_9.xlsx

  2. #2
    Forum Contributor
    Join Date
    08-14-2006
    Location
    USA
    MS-Off Ver
    2019
    Posts
    684

    Re: Automatic Sorting

    yes but it requires running a macro after each entry.

    Have you considered just using Autofilter to filter your Input Sheet?
    You can subtotal the Filtered Data.

  3. #3
    Registered User
    Join Date
    07-26-2011
    Location
    MA
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Automatic Sorting

    The issue is, I am constructing this sheet for someone else to use. The less they have to do the better. It is supposed to be user friendly. That is, just put expenses in as they are incurred and they sort themselves out, based on words.

    I am unfamiliar with Macros/VBA. I understand the scripting, but how do they run? Would I set up a button within the sheet, that would be pressed after each entry, and it would filter according to my specifications?

  4. #4
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

    Re: Automatic Sorting

    Here is a formula based approach. Make sure the formulas extend down far enough to cover whatever the user will be inputting. First I separated out your groups and used Data Validation to allow for a dropdown. Then I used J, K and L to track your catagories

    Those columns put progressively higher numbers into the rows with Meals, Office or Groceries respectively using (for meals in J for example)
    =IF(F11="Meals",COUNT($J$1:$J10)+1,"")

    Then in the second sheet, I use INDEX and MATCH to bring in your values like so Again for meals description
    =IF(ROWS($A$1:$A1)>MAX('Office Expense Input'!$J$11:$J$50),"",INDEX('Office Expense Input'!$G$11:$G$50,MATCH(ROWS($A$1:$A1),'Office Expense Input'!$J$11:$J$50))) Adjust the "50" to match whatever amount of rows you expect to see on the first sheet.

    Take a look at the attachment. Is that what you want?
    Attached Files Attached Files
    ChemistB
    My 2

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  5. #5
    Registered User
    Join Date
    07-26-2011
    Location
    MA
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Automatic Sorting

    ChemistB,

    Thank you very much for your help. That sheet worked exceptionally well!

    Thank you!

  6. #6
    Registered User
    Join Date
    07-26-2011
    Location
    MA
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Automatic Sorting

    ChemistB,

    One last question, is there any way to make the tables in Office Expense Breakdown tab automatically update as I add new expenses into the Office Expense Input tab?

    Right now, I am just copying and pasting the formula down to row 1000, as well as altering the formula to extend to row 1000.

    Is there a way that is less of a "hack", so to say?

    Thanks again.

  7. #7
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

    Re: Automatic Sorting

    Not sure exactly what you are asking, but I don't think there is an easier way (not without macros). That's why I have the IF statements in there that should give blanks in excess rows. You should only have to do this once. Figure out how many rows (i.e. 1000) and adjust your formulas and drag them down. Hope that makes sense.

  8. #8
    Registered User
    Join Date
    07-26-2011
    Location
    MA
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Automatic Sorting

    That's what I was thinking. I just adjusted the row number to be 1000. Thus, it is highly unlikely that I need to make iterations to my formulae.

    Thank you again.

  9. #9
    Forum Expert icestationzbra's Avatar
    Join Date
    01-07-2004
    MS-Off Ver
    2007, 2010
    Posts
    1,417

    Re: Automatic Sorting

    another flavour of solution to this problem (check Different Method tab)... this does not require additional columns (J-L)on the Office Expense Input tab.
    Attached Files Attached Files

+ 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