Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Totalling amounts in groups

  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 file that dumps from our financial system. The information is sorted by hierarchy and under each hierarchy code a cost code. The cost code belongs to a particular expense group. I need to be able to determine the total for each expense group, by hierarchy for a 12 month period.

    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.

    Help?

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Minnesota
    Posts
    92
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    It makes a big difference in how your data is arranged. I'm assuming that since this is being imported from a CSV file that all of your data basically goes straight down 3 columns. Is each hierarchy separated by a blank row? How many hierarchies are there? Always the same number or does it vary? How about Expense Groups?

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

    Default

    dan
    see cindy's previous post for more info:
    http://www.mrexcel.com/board/viewtop...=139&forum=2&7

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

    Default

    On 2002-02-19 06:16, Dan Aragon wrote:
    It makes a big difference in how your data is arranged. I'm assuming that since this is being imported from a CSV file that all of your data basically goes straight down 3 columns. Is each hierarchy separated by a blank row? How many hierarchies are there? Always the same number or does it vary? How about Expense Groups?
    OK. The data is arranged in 3 columns. No, each hierarchy is not separated by a blank row. And no, there is not always the same number. Expense groups are limited to 7, but there may be many entries to each expense gruop per hierarchy. I think we have about 25 or so hierarchies. One thing I did think of is that if I could substitute the account codes for the hierarchy name (ie; do a copy function until the next hierarchy line), then I could concatenate the hierarchy name with the expense group, and sort that way. Any idea how to do this automatically? And how to actually get a subtotal per expense group per hierarchy?

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,184
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Cindy --

    Without seeing the SS its a tough one but i have conserns here. Finance love it i would my field..

    Few questions ----

    Why CSV?
    Your saying your pulling in data how? and from where.
    I guess Unix ScO5 or 5.5 and via Exel Query??? So i move on to ODBC corrcect if so the agro i say is the Kopen3Live data ODBC transfer link, needs some oiling. ICT Dept will have this covered or should, i do this ever day and when you get GOOD data (OK i got shot for saying that last time GOOD data is the data you want, not data you need to work on or rearrange) you can doa s you please .

    If could have read this totally rong but.. your pulling in data you need to convert - i would only ever pull in clean data GOOD DATA - just my way i have Scripts that do this for me as i really cant be bothered ripping live data (again so i dont get shot live data in up to the minute dat - not 5 minutes old data - things change FAST in finance) so its easy to refresh and let scripts take oper.

    HTH

    Rdgs
    ==========
    Jack

    PS if i can help more please let me no |||| or post me a private message and ill reply soon as i can !


Some videos you may like

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
  •