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 ...
4th March 2008, 02:32 PM #1
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.
IDG Tech News
4th March 2008, 02:37 PM #2
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
4th March 2008, 02:44 PM #3
Can you not use:
= a1 & " " & b1 & " " & c1 type formula?
4th March 2008, 03:08 PM #4
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...
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.
Thanks to Pete10141748 from:
ITWombat (12th April 2008)
4th March 2008, 03:16 PM #5
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.
4th March 2008, 03:49 PM #6
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...
4th March 2008, 03:51 PM #7
If you just want simple totals, take a look at the sumif function.
4th March 2008, 03:52 PM #8
How about a pivot table? Usernames in one column, sum of print usage in the second.
4th March 2008, 04:01 PM #9
Here is the example data.
How do you setup a pivot table then?
4th March 2008, 04:17 PM #10
Load the .csv file from the logger
From the menu select Data...PivotTable and PivotChart report
Check the whole data area is selected then click next
Drag the user field and drop on Rows
Drag the printer field onto columns
Drag the pages field onto data
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
4 Thanks to CESIL:
Geoff (2nd July 2009), K.C.Leblanc (6th March 2008), MikeW (9th March 2008), SYSMAN_MK (4th March 2008)
4th March 2008, 04:20 PM #11
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...
2 Thanks to apeo:
K.C.Leblanc (6th March 2008), SYSMAN_MK (4th March 2008)
4th March 2008, 05:00 PM #12
This is a classic of "if all you have is a hammer then everything looks like a nail" :-)
Originally Posted by SYSMAN_MK
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.
5th March 2008, 10:00 AM #13
Originally Posted by srochford
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.
5th March 2008, 10:26 AM #14
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?
Originally Posted by SYSMAN_MK
5th March 2008, 10:29 AM #15
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.
By ZeroHour in forum General Chat
Last Post: 4th December 2009, 02:11 PM
By PEO in forum General Chat
Last Post: 11th October 2007, 03:43 PM
By deano in forum Web Development
Last Post: 5th June 2007, 10:45 PM
By firefox_2006 in forum Educational IT Jobs
Last Post: 4th March 2007, 11:54 AM
By russdev in forum Recommended Suppliers
Last Post: 16th December 2006, 09:00 PM
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)