[ms office - 2010] csv to report

[chazzy2501]

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!

 

sample

 

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

[Steve21]

Where are you stuck?

 

Steve

[chazzy2501]

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!

Edited January 4, 2012 by chazzy2501

[mac_shinobi]

# 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

 

[TABLE=class: grid, width: 500, align: center]

[TR]

[TD]username[/TD]

[TD]Printed[/TD]

[/TR]

[TR]

[TD]user_one[/TD]

[TD]1[/TD]

[/TR]

[TR]

[TD]user_two[/TD]

[TD]1[/TD]

[/TR]

[TR]

[TD]user_three[/TD]

[TD]2[/TD]

[/TR]

[TR]

[TD]user_three[/TD]

[TD]2[/TD]

[/TR]

[TR]

[TD][/TD]

[TD]=SUMIF(A2:A6, "=user_three",B2:B6)[/TD]

[/TR]

[/TABLE]

 

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

Edited January 4, 2012 by mac_shinobi