Dashboard view for validation of true false

jlopj

New Member
Joined
Sep 10, 2013
Messages
10
Context...
I'm working with a very large file but I will simplify it with the following:
I have static data in columns a through d (static datal and look up formulas in columns H through H fetching data from a report (report data). The static and report data should reconcile one for one. Columns I through L contain a true or false statement to validate the first two sets of columns respectively.

Objective...
My goal is to investigate unmatched records in related columns. E.g., the rows of data in column E and column A that are not the same

I'd like to accomplish this with a summarized table view with a bulk count where records are unmatched. I know I can do this using count if however I was hoping to use a pivot table to facilitate my next goal

Furthermore considering this is across several columns I like to drill down and see the unmatched data from the paired columns.
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
would you be kind enough to help the forum help you and post a mini workbook using the xl2bb add in (link below)? If you cannot instal the xl2bb then please post a table of the data (it won't have the calcuations/formulas but it would be a help.

but as in initial stab of a suggestion. do your true false comparisons, and possibly even do conditional formatting (different color for each edit check).
Then TURN on autofilter and filter with the TRUE/FALSE columns.
 
Upvote 0
Material Data:
Columns A through D is static data that I will be entering
Columns D and E are look ups to a report which should reconcile to what I entered
Columns F and G are doing a reconciliation validation as seen in the true false formula

abcdefg
1idratenamereport ratereport namecheck ratecheck name
2120james10jakeFALSE
Excel Formula:
=B2=D2
FALSE
Excel Formula:
=C2=E2
3220john20jameTRUEFALSE
4330jill30jillTRUETRUE
5430jake40johnFALSEFALSE


OBJECTIVE:
I want to create a dashboard view that will summarize the unmatched data across all the "check" columns"
I can do this with formulas BUT I would be most helpful if I could drill down into the count and see the population

So I am hoping to create a pivot table with a design below:

FALSE COUNT
check rate2
Excel Formula:
=countif(F:F,FALSE)
check name3

Thanks for your attention and contribution
 
Last edited:
Upvote 0
Here is a pivot table. I'm not sure how much infor comes over with copy/paste. The pivot table has 2 filters that you can use to select which data you want to see.

mr excel questions 15.xlsm
ABCDEFGHIJK
1idratenamereport ratereport namecheck ratecheck name
2120james10jakeFALSEFALSE10jake
3220john20jameTRUEFALSE20jame
4330jill30jillTRUETRUE30jill
5430jake40johnFALSEFALSE40john
6
7check rateAll
8check nameAll
9
10idratereport ratenamereport name
1112010jamesjake
1222020johnjame
1333030jilljill
1443040jakejohn
15
16
17
18
Sheet20
Cell Formulas
RangeFormula
D2:E5D2=J2
F2:G5F2=B2=D2
 
Upvote 0
Can you help me understand where your pivot is summarizing the false values across the check columns?

My work around was to create a pivot table for each check column with the respective check column filtered to check. However I have many over 15 columns.

Is there a way to link on the data from those tables or a table to just represent the net number of unmatched data for each check column in a single pivot table?
 
Upvote 0
it is not. This is showing all. And the pivot report, as it is designed does not summarizing. It is just a way for you to filter your data with the two filters in cells b7 and b8 (they are part of the pivot table). the xl2bb add in does not copy pivot tables (or I have not figured out how to do so). so you can't really tell those cells are part of the pivot. I'll send screen shots....

with all selected:
1679351899555.png


Check Rates FALSE, Names ALL

1679352015072.png



Check Rates All, Names False:

1679352108097.png



All False:
1679352154615.png
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top