Office Software Thread, csv to report in Technical; I need help to automate some work I have to do with a csv job log. I get csv logs ...
4th January 2012, 01:46 PM #1
csv to report
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)
4th January 2012, 01:56 PM #2
Where are you stuck?
4th January 2012, 02:36 PM #3
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 03:28 PM.
4th January 2012, 03:20 PM #4
# 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 03:41 PM.
Thanks to mac_shinobi from:
chazzy2501 (4th January 2012)
By FN-GM in forum General Chat
Last Post: 3rd December 2010, 11:46 PM
Last Post: 15th September 2009, 12:28 PM
Last Post: 21st May 2009, 09:34 AM
By TechMonkey in forum EduGeek.net Site Problems
Last Post: 10th November 2008, 04:44 PM
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)