Sorting my data - how do I do it??
Find bottlenecks in your Excel workbooks
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Sorting my data - how do I do it??

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I have a csv dump from our financial system that gives me financial data for each account code. The account code contains expense elements which in turn are grouped to Expense Groups. For example, a 10 digit account code has the last 3 digits as an expense element. That expense element can be classified in a group such as Consumables, Labour, etc. What I need to do is the following:

    - Isolate the expense element for codes that are only 10 digits or longer (I guess this is a count function, then a right function)

    - Total the charges for each month by expense group. That is, if an expense element belongs to a certain group, the costs for one month will be added to the costs for the same month for all other expense elements in that group.

    - Now here is the tricky part. I also have a hierarchy, so I only want to do the second part for EACH HIERARCHY. When the data is dumped in the csv format, there is actually a break between hierarchy codes. I want to be able to recognise where a hierarchy code begins and ends and summarise the expense group costs for each hierarchy.

    Am I trying to do too much? Please tell me I'm not. It would be so much easier doing this with a single csv file, than the alternative (minimum 12).

  2. #2
    Board Regular RichardS's Avatar
    Join Date
    Feb 2002
    Location
    Victoria, Australia
    Posts
    761
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    The formula to get the right 3 numbers from the account code if account code is 10 digits long or longer, assuming account code is in column A, is
    =IF(LEN(A1)>=10,RIGHT(A1,3),"")
    I also assume the hierarchy code is in a different column? Did you want to subtotal each expense code by hierarchy? If you sort all the data by hierarchy, then by expense code, then use data menu subtotal, subtotal the amount at each change of expense code, this should get what you want, I hope.
    Richard

  3. #3
    New Member
    Join Date
    Feb 2002
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks for that, but the problem gets bigger. (May have been better to tackle it bit by bit). I have more than one expense element in a group. eg; 301,302,303,304,305 may all belong in one group. These are generally in order so there isn't a problem in saying if the number is between x and x, add it to the total for that group. But how??

    The other thing is that no, my hierarchy names are in the same column as my original expense code. Perhaps there is a way to rangename from one hierarchy start to finish? Then add only in that range name? I am getting thoroughly confused...

  4. #4
    New Member
    Join Date
    Feb 2002
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Just another thought. I actually have a column to the right in my csv dump that says "E3 - Consumables", etc., indicating which expense group each line belongs to. My hierarchy names are alpha only, and my account codes are number only. Is there a way to delete all numbers from the column, leaving the alpha in, then trying to add the total of each expense group (eg; E3), for each hierarchy section?

  5. #5
    Board Regular RichardS's Avatar
    Join Date
    Feb 2002
    Location
    Victoria, Australia
    Posts
    761
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Cindy,
    It is very difficult to come up with a solution without seeing what data you are workig with.
    Is it possible when you import the csv file to parse out the hierarchy. I assume there is no comma, but if the hierarchy is a constant length to the left of the rest of the account number, perhaps you could use the data text to columns feature to separate the two, then sort on hierarchy, the "E3-Consumables", and do the subtotal thing as advised in previous post.
    If this doesn't make sense or doesn't work, can you post a sample line of data, and what you want to achieve?
    Richard

  6. #6
    New Member
    Join Date
    Feb 2002
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Assume my data is as follows:

    Account Code Expense Group Amount
    HIERONE
    1234567890 E3 100
    9876543219 E1 200
    2587945632 E2 300
    1258795463 E1 400
    1258796532 E2 500
    1254899720 E3 600
    1258786931 E4 700

    HIERTWO
    1231657986 E4 100
    1236498710 E3 200
    1235789654 E2 300
    1235649874 E3 400
    1236798745 E3 500


    So for Hierarchy one (HIERONE), I want to be able to pull out a total of $600 for expense group E1, $800 for E2 and so on. And then do the same for the next hierarchy, and the next.

    Cindy

  7. #7
    Board Regular RichardS's Avatar
    Join Date
    Feb 2002
    Location
    Victoria, Australia
    Posts
    761
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Jeez, Cindy, now your getting tricky.

    I'm sure there is a way to get the HIERONE thing from where it is into a column to the left of your data, as long as there is a line between each HIER*. A VB'er could probably nut it out but that's beyond me. I could probably do it with the old Excel4 macros, but I think thats a bit slow compared to VB. Once you've figured that out, the text to columns the sort and subtotal idea would work OK. Try posting a question in the VB part of the board to solve the problem of shifting the HIER* to the left of the account codes. Post the sample data you posted here and see how you go.

    I'm about to knock off. Be interested to see how you went. Good luck

    Richard

  8. #8
    Board Regular
    Join Date
    Feb 2002
    Posts
    202
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    cindy
    if there is a one row break between your hierarchy ranges you can do this fairly easily although it's not really that automated, which is ideally what you were looking for?

    i am assuming that there is only one lot of hierarchy one data, one lot of hierarchy 2 data, etc., per csv file, and that you don't have dozens or hundreds of different hierarchies in each file. if that's not the case save your eyes and ignore what's below.

    first you need to take up richard's suggestion of doing a text to columns (data menu) to separate your data into separate columns (my reading is that the csv file dumps each record into one row instead of 3 or however many?). select the delimited option and select 'space' as the delimiter.

    once that's done, you'll need to identify the records with >10 characters, which richard has already provided a formula solution for (how you consolidate all the ones that meet the 'true' condition i don't know but sorting is one option).

    then use a pivot table (data menu) to summarise your E1s, E2s etc. if you haven't used these before go to the excel help topic - it's quite good. selecting the right data for the table is where your one row gap between hierarchies comes in. use ctrl+shift+down arrow, then right arrow with ctrl+shift still held down and that will select your hierarchy one data. don't worry about the 'HIERONE' in the column - the pivot table will essentially ignore it.

    do the same with the second (and subsequent?) hierarchy, except that you'll have to paste in the column headings to make the pivot table work.

    like i said, not a very automated solution but i think pivot tables are the way to go, as long as you don't have lots of different hierarchies - it gets a bit tedious.
    you might be able to record some of this as a vba macro but i can't be of much help to you there.
    good luck

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com