Monthly Activity Tracking Table

[Avale]

Hello,

I'm trying to create a sheet in an excel document that compares data between two separate tables (A raw-data table copy-pasted from our reporting software, and a master-part-list) in order to display which parts have a report entry for a given day of the month, and which were missed and didn't get reported on for a given day of the month.Optimally, this would be displayed with the dates on top (column headers) and the part numbers from the master list on the left (row headers) with the intersections reading something like "Pass" or "Fail" or even better applying a number value that shows how many times on that day that the part was reported.

The issue i'm running into is that I can't figure out how to get the pivot table to understand the data in a way where it displays like this. What it wants to do instead is count the total number of part report entries, not based on their individual part #'s but rather counting ALL reports, and then apply those down the entire column for that date.

So it shows:
Day 1 Day 2
Part# 1: 99 Part# 1: 102
Part# 2: 99 Part# 2: 102
Part# 3: 99 Part# 3: 102

- Instead of doing what I want it to do and counting each part# entry based on part#. I have limited experience with pivot tables, we just recently updated from Office 2007 to Office 2023 so i'm trying to make use of the capabilities, i'm just stuck.

Any help would be appreciated, thank you.

[AliGW]

Welcome to the forum.

There is no version 11 of Excel - what do you have? Please update your profile.

There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

[Avale]

Hello,

Thank you, and I appologize. I believe i've corrected the version.

Attached is a sheet with some data and an example of what i'm looking for from the output.

Thank you.

[JeteMc]

As to the pivot table:
1. Drag Part # to the Rows area and also to the Values area
2. Drag Date to the Columns area
3. Use the following conditional formatting rules applied to all cells showing 'count of part #' values
Red: Cell Value < 1
Light Green: Cell Value = 1
Dark Green: Cell Value > 1
As to the second output:
1. Use the master parts list
2. Add a column populated using: =COUNTIFS(RawDataFromPlex[Part '#],[@[Part '#]],RawDataFromPlex[Date],K$24-1)=0
3. Filter the Needing Report column to display only TRUE
Let us know if you have any questions.