Where are you stuck?
I need help to automate some work I have to do with a csv job log. I get csv logs from the sharp printers and need to exclude some data (columbs with a total value of zero) sum other columbs to a total and I need to do this by each user. It's one of those tasks that takes me 2 hours every 3 months but its laborious and I know a large chunk could be automated!
So I think I need to do the following
1. tell excel that row 1 is a title for each columb
2. delete all columbs that have a sum of zero
3. I need it to order the data by user
4. sum all the data in each columb into a total for that user (I know of autosum but I have to create a blank row and manulay select that users data)
5. remove all the non sumed data
6. make pretty alternative colours for each row (make printout easy to read)
Where are you stuck?
all of it! but i suppose automating no 4 would be the hardest.
after sorting by user i'd have 900 rows with the identical user name and each of those columns would need to be sumed but none of the other users rows.
I was hoping to create a super macro but I've never made even a simple one!
Last edited by chazzy2501; 4th January 2012 at 02:28 PM.
# 1 - don't select the column headers when doing forumulas in excel that way you don't have to tell excel anything as it is not apart of the forumula
# 4 - you could use sumif
username Printed user_one 1 user_two 1 user_three 2 user_three 2 =SUMIF(A2:A6, "=user_three",B2:B6)
formula is : =SUMIF(A2:A6, "=user_three",B2:B6)
This will tally up any usernames that are equal to user_three and you could do the same for pupil, administrator so the formula would be :
You would obviously just need to adjust the range of cells in the first and last part of the above forumula ??
# 6 - in excel 2010 I have a 'format as table' option which makes each row a different and opposite colour
Last edited by mac_shinobi; 4th January 2012 at 02:41 PM.
chazzy2501 (4th January 2012)
There are currently 1 users browsing this thread. (0 members and 1 guests)