+ Post New Thread
Page 1 of 2 12 LastLast
Results 1 to 15 of 17
Educational Software Thread, Calling All Excel Gods in Technical; Right I hardly use Excel and am haing trouble with the following: I have a table with staff name in ...
  1. #1

    SYSMAN_MK's Avatar
    Join Date
    Sep 2005
    Posts
    4,006
    Thank Post
    489
    Thanked 1,343 Times in 729 Posts
    Rep Power
    429

    Calling All Excel Gods

    Right I hardly use Excel and am haing trouble with the following:

    I have a table with staff name in one column, value x in the next and value y in the next. I have multiple rows of data for the same users. E.G 10 rows for user1 15 rows for user2 etc etc.

    How can i merge the data so I have one row for user 1, user 2 etc.

    Cheers in advance.

  2. #2
    krisd32's Avatar
    Join Date
    Feb 2006
    Location
    Longridge, Preston
    Posts
    545
    Thank Post
    85
    Thanked 68 Times in 47 Posts
    Rep Power
    43
    think you could use concatenate to merge data into single cells but not rows!

    good old copy and paste could be your friend here!

    sorry for not being much help

    Kris

  3. #3

    plexer's Avatar
    Join Date
    Dec 2005
    Location
    Norfolk
    Posts
    13,661
    Thank Post
    658
    Thanked 1,624 Times in 1,453 Posts
    Rep Power
    422
    Can you not use:

    = a1 & " " & b1 & " " & c1 type formula?

    Ben

  4. #4
    Pete10141748's Avatar
    Join Date
    Nov 2007
    Posts
    1,365
    Thank Post
    106
    Thanked 220 Times in 130 Posts
    Rep Power
    86
    Seems to me that you have multiple X and Y data for each member of staff.
    As such, your kinda stuck with having the rows, unless you want to have one very long row with the headings "X" "Y" X2" "Y2" and so forth...

    From this;



    To this;



    Other than that, perhaps you'd want to consider using a database maybe?
    That way you could have one table for the members of staff (only needs to be entered the once) and then another table holding the multiple entires for each staff member?

    Just a thought.

    Pete

  5. Thanks to Pete10141748 from:

    ITWombat (12th April 2008)

  6. #5

    SYSMAN_MK's Avatar
    Join Date
    Sep 2005
    Posts
    4,006
    Thank Post
    489
    Thanked 1,343 Times in 729 Posts
    Rep Power
    429
    Right bit more background. Am using the free PaperCut™ Print Logger and want to create a chart to show user usage. PaperCut Print Logger logs each print job as a separate entry. So if User 1 prints 10 different documents in a day then there are 10 rows of info in the Excel sheet. So when I create my bar chart for example i get 10 bars for User 1 rather than 1 single bar.

  7. #6
    apeo's Avatar
    Join Date
    Sep 2005
    Location
    Lost
    Posts
    1,612
    Thank Post
    95
    Thanked 115 Times in 111 Posts
    Rep Power
    42
    Ok a copy of the data or even a screen shot would be useful but going on what you are saying... maybe creating a pivot table from the raw data could work for you but i couldnt say for sure as i dont actually know as im have a hard time figuring out what you are producing.. im still knackered from my flight...

  8. #7
    Iain's Avatar
    Join Date
    Oct 2006
    Location
    Warwickshire
    Posts
    188
    Thank Post
    28
    Thanked 93 Times in 53 Posts
    Rep Power
    32
    If you just want simple totals, take a look at the sumif function.

    Iain.

  9. #8
    clareq's Avatar
    Join Date
    Dec 2005
    Location
    Doncaster
    Posts
    730
    Thank Post
    53
    Thanked 188 Times in 124 Posts
    Rep Power
    101
    How about a pivot table? Usernames in one column, sum of print usage in the second.

  10. #9

    SYSMAN_MK's Avatar
    Join Date
    Sep 2005
    Posts
    4,006
    Thank Post
    489
    Thanked 1,343 Times in 729 Posts
    Rep Power
    429
    Here is the example data.

    How do you setup a pivot table then?
    Attached Images Attached Images

  11. #10

    CESIL's Avatar
    Join Date
    Nov 2006
    Location
    Hampshire
    Posts
    1,404
    Thank Post
    109
    Thanked 267 Times in 198 Posts
    Rep Power
    168
    Load the .csv file from the logger

    From the menu select Data...PivotTable and PivotChart report

    Click next

    Check the whole data area is selected then click next

    Click Layout

    Drag the user field and drop on Rows

    Drag the printer field onto columns

    Drag the pages field onto data

    Click OK

    Click Finish

    You should now see a sheet with names down the left, printers across the top and total pages in the columns with grand totals on the right and at the bottom

  12. 4 Thanks to CESIL:

    Geoff (2nd July 2009), K.C.Leblanc (6th March 2008), MikeW (9th March 2008), SYSMAN_MK (4th March 2008)

  13. #11
    apeo's Avatar
    Join Date
    Sep 2005
    Location
    Lost
    Posts
    1,612
    Thank Post
    95
    Thanked 115 Times in 111 Posts
    Rep Power
    42
    Ok heres how you create a pivot table:

    Select the entire table.
    Goto Data> Pivottable and PivotChart Report.
    It should aready be Mircrosoft Excel and PivotTable. Click Next.
    The ranges should aready be set. Click Next.
    Choose if you want a New Worksheet or not.
    Click Layout, Drag User to Row Box, Drag Pages and Copies to Data Box.
    Click Ok and Finish.

    That should be it.

    Edit: Looks like someone beat me to it...

  14. 2 Thanks to apeo:

    K.C.Leblanc (6th March 2008), SYSMAN_MK (4th March 2008)

  15. #12

    Join Date
    Aug 2005
    Location
    London
    Posts
    3,156
    Thank Post
    116
    Thanked 529 Times in 452 Posts
    Blog Entries
    2
    Rep Power
    124
    Quote Originally Posted by SYSMAN_MK View Post
    Right I hardly use Excel and am haing trouble with the following:

    I have a table with staff name in one column, value x in the next and value y in the next. I have multiple rows of data for the same users. E.G 10 rows for user1 15 rows for user2 etc etc.

    How can i merge the data so I have one row for user 1, user 2 etc.

    Cheers in advance.
    This is a classic of "if all you have is a hammer then everything looks like a nail" :-)

    Although you have a spreadsheet, what you want is a database. Excel's pivot table will do pretty much what you want but a report in Access might be easier to work with.

  16. #13
    apeo's Avatar
    Join Date
    Sep 2005
    Location
    Lost
    Posts
    1,612
    Thank Post
    95
    Thanked 115 Times in 111 Posts
    Rep Power
    42
    Quote Originally Posted by srochford View Post
    This is a classic of "if all you have is a hammer then everything looks like a nail" :-)

    Although you have a spreadsheet, what you want is a database. Excel's pivot table will do pretty much what you want but a report in Access might be easier to work with.


    Problem is, does the printer proggy export to a database or can the data be imported into a database. In this case i think having a database not needed cause this is just a dump of the log and a report needs to be produced on it. Creating a database just to do that, i think is a bit excessive... thats what i think anyway.

  17. #14

    Edu-IT's Avatar
    Join Date
    Nov 2007
    Posts
    7,148
    Thank Post
    403
    Thanked 623 Times in 569 Posts
    Rep Power
    181
    Quote Originally Posted by SYSMAN_MK View Post
    Right bit more background. Am using the free PaperCut™ Print Logger and want to create a chart to show user usage. PaperCut Print Logger logs each print job as a separate entry. So if User 1 prints 10 different documents in a day then there are 10 rows of info in the Excel sheet. So when I create my bar chart for example i get 10 bars for User 1 rather than 1 single bar.
    Just out of interest how does that work, do you just install it on the server and tell it what printers to monitor or is it more complex than that?

  18. #15

    SYNACK's Avatar
    Join Date
    Oct 2007
    Posts
    11,223
    Thank Post
    874
    Thanked 2,717 Times in 2,302 Posts
    Blog Entries
    11
    Rep Power
    780
    Depending on how good your scripting is you could make an ASP page that will create these results on the fly from the data files. You can actually connect to an excel or csv file as if it is a database and run sql commands on the data.

    Possibly a bit extreme for a one time thing but could be useful for automating the procedure for something that will be regular.

SHARE:
+ Post New Thread
Page 1 of 2 12 LastLast

Similar Threads

  1. Calling out the Scottish
    By ZeroHour in forum General Chat
    Replies: 24
    Last Post: 4th December 2009, 01:11 PM
  2. Calling all Top Gear fans
    By PEO in forum General Chat
    Replies: 26
    Last Post: 11th October 2007, 02:43 PM
  3. Calling for Joomla Help!
    By deano in forum Web Development
    Replies: 3
    Last Post: 5th June 2007, 09:45 PM
  4. Calling all ICT Technicians
    By firefox_2006 in forum Educational IT Jobs
    Replies: 1
    Last Post: 4th March 2007, 10:54 AM
  5. How to get business with out cold calling
    By russdev in forum Recommended Suppliers
    Replies: 0
    Last Post: 16th December 2006, 08:00 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
  •