Totalling amounts in groups

[Cindy]

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?

[Dan Aragon]

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?

[anno]

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

[Cindy]

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?

[Jack in the UK]

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 !