Sorting my data - how do I do it??

Cindy

New Member
Joined
Feb 17, 2002
Messages
16
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).
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
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
 
Upvote 0
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...
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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 :)
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top