+ Post New Thread
Results 1 to 4 of 4
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 ...
  1. #1
    chazzy2501's Avatar
    Join Date
    Jan 2008
    Location
    South West
    Posts
    1,781
    Thank Post
    213
    Thanked 263 Times in 213 Posts
    Rep Power
    67

    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!

    sampleUntitled.png

    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)

    cheers

  2. #2

    Steve21's Avatar
    Join Date
    Feb 2011
    Location
    Swindon
    Posts
    2,689
    Thank Post
    334
    Thanked 515 Times in 483 Posts
    Rep Power
    179
    Where are you stuck?

    Steve

  3. #3
    chazzy2501's Avatar
    Join Date
    Jan 2008
    Location
    South West
    Posts
    1,781
    Thank Post
    213
    Thanked 263 Times in 213 Posts
    Rep Power
    67
    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.

  4. #4

    mac_shinobi's Avatar
    Join Date
    Aug 2005
    Posts
    9,706
    Thank Post
    3,243
    Thanked 1,047 Times in 969 Posts
    Rep Power
    364
    # 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 :

    =SUMIF(A2:A6, "=pupil",B2:B6)

    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.

  5. Thanks to mac_shinobi from:

    chazzy2501 (4th January 2012)

SHARE:
+ Post New Thread

Similar Threads

  1. Woman dials 999 to report snowman theft in Kent
    By FN-GM in forum General Chat
    Replies: 15
    Last Post: 3rd December 2010, 10:46 PM
  2. CSV to individual text files
    By DanW in forum Scripts
    Replies: 14
    Last Post: 15th September 2009, 11:28 AM
  3. Save CSV to Server (serialize?)
    By danIT in forum Coding
    Replies: 3
    Last Post: 21st May 2009, 08:34 AM
  4. [CLOSED] Bug/Error: Not allowed to report a post
    By TechMonkey in forum EduGeek.net Site Problems
    Replies: 1
    Last Post: 10th November 2008, 03:44 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •