Microsoft exel 2013

Please find atatched the sample file.Is there a way to make the data in sheet1 to look like sheet2 in the file atatched.
maproductivityentered-1.xls
Star79Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mlmccCommented:
Are you looking to do this strictly in Excel or can a reporting tool be used?

You included Crystal and SSRS in the topics.

It can be done fairly easily in Crystal.

mlmcc
0
C. Blaise MitsutamaFounder/Principal | Technical Writing | Editing | Training | Design | Presentation | StartupsCommented:
Star79,

You can use a pivot table to achieve the formatting in Sheet2 of your original file. Below are the steps I used and attached is an Excel file that contains your original Sheet1 and Sheet2 as well as the Solution worksheet and pivot table. Let me know if you have any questions.

Step 1:       Use Data / Text to Columns to split StepDate into date and time.

Step 2:    Add a header title to the Time column.

Step 3:    Put your cursor in cell A1. Insert / Pivot Table to a new worksheet.

Step 4:    Add StepData to Columns in the pivot table.

Step 5:    Add Time to Rows in the pivot table.

Step 6:    Add New Orders to Values in the pivot table.

Step 7:    Add Refill Orders to Values in the pivot table.

Step 8:    Scroll to columns BL and BM and Select the column headers, Total Sum of New Orders and Total Sum of Refill Orders. Open the right-click menu and select Remove Grand Total.

Step 9:    Scroll to row 30. Open the right-click menu and select Remove Grand Total.

Step 10:  Scroll up and select rows 2 and 3. On the Ribbon, select the Format option in the Cells group. Then choose Visibility / Hide & Unhide / Hide Rows.

Step 11:  In the Pivot Table Fields panel, go to the Values area and open the dropdown menu for Sum of New Orders. Select Value Field Settings and change the Custom Name to New.

Step 12:  Use the same steps for the Sum of Refill Orders and change the Custom Name to Refills.

Step 13:  Select columns B through BK. On the Ribbon, use the Center option in the Alignment group to center the column titles and data.

Step 14:  Select cells B4 and C4 as a group. On the Ribbon, select the Format option in the Cells group. Select Format Cells.

Step 15:  In the Format Cells window, select the Alignment tab. In the Horizontal dropdown menu, select Center Across Selection. Be sure that both cells remain selected.

Step 16:  On the Ribbon, click the small arrow in the lower righthand corner of the Number group. This opens the Format Cells window. Select Date and the desired Type.

Step 17:  With both cells B4 and C4 selected, on the Ribbon, doubleclick the Format Painter in the Clipboard group. You should see a small paintbrush appear attached to your mouse cursor. Click your mouse on cell D4, F4, H4, etc. to cell BJ4. To turn the Format Painter off, on the Ribbon, click the Format Painter.

Step 18:  Select cell B6 through BK29. On the Ribbon in the Font group, click the dropdown arrow for Borders. Select More Borders. The Format Cells window will open. Select the desired border style and the Outline and Inside icons. Click OK.

C. Blaise Mitsutama ("Blaise")
maproductivityentered-1.xls
0
Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
One of the 3rd-party Crystal Reports Desktop Schedulers listed at http://kenhamady.com/bookmarks.html can automate the process of exporting a Crystal report to Excel, generating a highly formatted excel pivot table, hiding the source data sheet, emailing, etc.
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

Star79Author Commented:
I appreciate all comments..but mlmcc Iam curious how can it be done in crystal?
Thanks.
0
mlmccCommented:
I wasn't sure if you wanted a reporting tool solution so I didn't take the time to develop one.  This took about 10 minutes

Using your spreadsheet as input I created a report showing 2 cross tabs.
One shows daily orders per hour.  The other shows weekly orders per hour

mlmcc
MAProductivity.rpt
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Star79Author Commented:
Hello mlmcc,
How can I view the rpt file.When I save its saving as html file.
Thanks.
0
mlmccCommented:
Right click it
Click SAVE TARGET  AS
Change the extension to RPT

If you already have it saved, change the extension to RPT

mlmcc
0
Star79Author Commented:
Hello mlmcc,
I havent worked much on the crosstab.
How can I add the heading new and refill to the cross tab.
MAProductivityEntered-test.rpt
0
mlmccCommented:
Try this

Right click the cross tab in the upper left corner
Click SUMMARIZED FIELD LABELS --> SHOW SUMMARIZED FIELD LABELS
Right click the labels in turn and change the label to what you want (NEW/REFILL)

mlmcc
MAProductivityEntered-test.rpt
0
Star79Author Commented:
In the same report can I add a parameter to select the day of week say Monday and it will give the data for all the mondays of the selected data
0
mlmccCommented:
You can use the DayofWeek function

DayofWeek({ourDateField}) = crMonday

mlmcc
0
Star79Author Commented:
I added the parameter for the Day of the week.For some reason when i run the report for July1 to July23 and pick Monday.Its giving me the data for all sundays in that date range..Please let me know how to handle this
MAProductivityEntered-test1.rpt
0
Star79Author Commented:
And can I have the day of week as multiple values..How should the selection formula look like
0
mlmccCommented:
Use the Crystal constants or you have to set the first day of the week

Crystal defaults to Sunday as the first day of the week.

To use Monday as the first day of the week
DayofWeek({vwWorkFlowStatsRxsAllWFStepsHour.StepDate},crMonday) = {?Day of Week}

Change the parameter to allow multiple values.  The same selection will work

mlmcc
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Spreadsheets

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.