Getting Detailed Pivot Results when some row data is identical

[holmansworld]

Hi All,

I hope there is some hope for me. I even think this might be easy, but I'm
literally spending hours trying to figure it out.

When I run my pivot, I have data that has the same row information, but the
Pivot only shows one row, it will now show the row seperatly.

My Data is as follows:

Report Date Time User Elapsed Data Seconds
Report A 11/3 8AM User 5
Report A 11/3 8AM User 5
Report A 11/3 4PM User 7
Report A 11/3 4PM User 10

While two rows show that Report A on 11/3 Ran at 11AM for a user, and the
time it took was 5 Seconds, the pivot will not show two rows of data, it will
only show one. I need two rows of data, because I'm running another forumla
along side the pivot that will give me a count of how many times Report A was
run. So right next to the Pivot I need to show a total count for Report A of
4. Because my Pivot is only showing one row of data for the 8AM run, I cannot
get my count correct. The Total for the AM run is correct at a total of 10
Seconds, but I need to see two Rows. I'm running over over twenty or so
reports, so my real data looks like Reports A through Reports Z, with
periodic instance where one report was run at the same time for the same
duration. Each time and only when this happens, my count that is running
along side my pivot table is off. I have have to run the count along side the
pivot as part of my project.

My Pivot looks like this:

Sum of Elapsed Data Seconds
Report Date Time Elapsed Data Seconds User Total
Report A 11/3 4PM 7 User 7
10 User 10
8AM 5 User 10

Any assistance at all and I would VERY GRATEFUL!

Thank You!

Holman's World Inc (R)

 

[Ron Rosenfeld]

Hi All,

I hope there is some hope for me. I even think this might be easy, but I'm
literally spending hours trying to figure it out.

When I run my pivot, I have data that has the same row information, but the
Pivot only shows one row, it will now show the row seperatly.

My Data is as follows:

Report Date Time User Elapsed Data Seconds
Report A 11/3 8AM User 5
Report A 11/3 8AM User 5
Report A 11/3 4PM User 7
Report A 11/3 4PM User 10

While two rows show that Report A on 11/3 Ran at 11AM for a user, and the
time it took was 5 Seconds, the pivot will not show two rows of data, it will
only show one. I need two rows of data, because I'm running another forumla
along side the pivot that will give me a count of how many times Report A was
run. So right next to the Pivot I need to show a total count for Report A of
4. Because my Pivot is only showing one row of data for the 8AM run, I cannot
get my count correct. The Total for the AM run is correct at a total of 10
Seconds, but I need to see two Rows. I'm running over over twenty or so
reports, so my real data looks like Reports A through Reports Z, with
periodic instance where one report was run at the same time for the same
duration. Each time and only when this happens, my count that is running
along side my pivot table is off. I have have to run the count along side the
pivot as part of my project.

My Pivot looks like this:

Sum of Elapsed Data Seconds
Report Date Time Elapsed Data Seconds User Total
Report A 11/3 4PM 7 User 7
10 User 10
8AM 5 User 10

Any assistance at all and I would VERY GRATEFUL!

Thank You!

Holman's World Inc (R)

Drag
Report to Rows
Date to Columns
Time to Columns
Elapsed Data Seconds to Value (or Data area)
Elapsed Data Seconds to Value area (yes a *second* time)

Change one of the Elapsed Data Seconds to Count

Set the Pivot options to give Grand Total by Rows.

Rename fields to your taste

Looks like this:

===========================================================
Date/Time
3-Nov Grand Total
Reports 4PM 8AM
Report A
Total Time (secs) 17 10 27
Count 2 2 4

==========================================================
--ron

 

[Shane Devenshire]

Hi,

I'm not clear on what you want. First, I don't see any 11 AM data. Second,
the function of a pivot table is to summarize data, if you don't want it to
combine like items (summarize the data), then why use a pivot table at all?

If you just want a count of how many times a given report was run you can
create a formula such as

=COUNTIF(A1:A100,"Report A")

or enter "Report A" in M1 and
=COUNTIF(A$1:A$100,M1)
in an adjacent cell.

No need for a pivot table at all.

Consider this, if you want to see a row everytime a report is run, well, you
already have that in the data source, 1 row for every report run. In other
words the pivot table is beautiful but functionless for your purpose.

 

[holmansworld]

Thanks Shane.

I apologize this was a typo. The report time 8 AM.

My Example was small, but I actually have around five hundred lines of data,
some have Report A a few time, other lines have Report B, etc. There are
about 100 unique reports covered in about five hundred lines.

I thought of the Pivot in terms of the requirement to both Chart the
Individual Report and also provide along side the Pivot a Statics Box to say
how many times the reports were run.

The Challenge is the occasions when the same report is run at the same time
by the same user and trying to keep an accurate count. I will try your
formulas.

The data is coming out of a BI Tool and exported to MS EXCEL where I need to
run create a Chart and a Pivot. The prior project person was actually running
about 100 seperate tabs for each report and running a macro to do this. The
challenge comes earch reporting period when the data changes and the macro
may need to be updated.

Using the Pivot, I wanted to avoid 95 tabs horizontally across the workbook
and focus on the Pivot Vertical visual.

Thanks again.

 

[holmansworld]

Thanks Ron.

Will need to see how the the count will come out when I a full report
listing of about 100 reports spanning 500 lines of data and if I can via th
pivot select any on of the reports (A-Z, et,c) at any given time and get he
Count Formula for my Statistics Value Box Row One. I'll sloa have a Stat
using the Forumula (MAX) as well another row using (STDEV).