+ Reply to Thread
Results 1 to 5 of 5

A complicated "sort"

  1. #1
    Forum Contributor
    Join Date
    02-24-2010
    Location
    BC, Canada
    MS-Off Ver
    Excel 2010
    Posts
    174

    A complicated "sort"

    The software for cemetery that I use wasn't made by an accountant. It spits out data in the most horrible format possible. I've attached an example of how the data is outputted and also how I would like it to be. Somehow I need to search for each account number (they are formatted 0000-00) and then put the corresponding information with it. The example book will show you what I mean. The number of lines for each invoice is different so there's no simply formula that I can come up with that will work.

    Is this possible to do or is it far too complicated?

    Also note that the actual data is about 4500 lines long.

    Thanks
    Attached Files Attached Files
    Last edited by The Phil; 09-30-2010 at 04:13 PM.

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: A complicated "sort"

    Hi Phil,

    So you want to look at each row from top to bottom of your RawData and:
    If Column A has the word Sale Number - Save that number in a variable (SaleNo)
    Also get from that row the Sale Date - Save in another variable (SaleDate)
    If Col A says "Customer - save this in another variable (Customer)
    if column A has a "-" in it grab the account number and either credit or debit

    Copy this stuff above on a another sheet below the last row and keep going.

    Sounds easy enough.

    Tell me you have more than 200 of these. I hate writing code that takes longer to do than if it was done by hand.

  3. #3
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: A complicated "sort"

    Hi Phil,

    Find the attached with a Macro that does what you desire and expressed above.

    It can be cleaned to do other things but is a good start for your sort/filter.
    Attached Files Attached Files

  4. #4
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: A complicated "sort"

    Try this:
    Please Login or Register  to view this content.
    Attached Files Attached Files
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  5. #5
    Forum Contributor
    Join Date
    02-24-2010
    Location
    BC, Canada
    MS-Off Ver
    Excel 2010
    Posts
    174

    Re: A complicated "sort"

    MarvinP, thanks for taking the time to respond. I couldn't quite get your sheet to do anything though :S

    JB, the sheet works great, thanks!

    Reps to both of you!

+ 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