Calling All Excel Gods

[SYSMAN_MK]

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.

[krisd32]

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

[plexer]

Can you not use:

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

Ben

[Pete10141748]

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

[SYSMAN_MK]

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.

[apeo]

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...

[Iain]

If you just want simple totals, take a look at the sumif function.

Iain.

[clareq]

How about a pivot table? Usernames in one column, sum of print usage in the second.

[SYSMAN_MK]

Here is the example data.

How do you setup a pivot table then?

[CESIL]

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

[apeo]

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...

[srochford]

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.

[apeo]

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.

[Edu-IT]

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?

[SYNACK]

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.