Dashboard view for validation of true false

[jlopj]

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.

 

[awoohaw]

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.

 

[jlopj]

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

	a	b	c	d	e	f	g
1	id	rate	name	report rate	report name	check rate	check name
2	1	20	james	10	jake	FALSE =B2=D2	FALSE =C2=E2
3	2	20	john	20	jame	TRUE	FALSE
4	3	30	jill	30	jill	TRUE	TRUE
5	4	30	jake	40	john	FALSE	FALSE

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 rate	2 =countif(F:F,FALSE)
check name	3

Thanks for your attention and contribution

 

[jlopj]

Bump

 

[jlopj]

.

 

[awoohaw]

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
	A	B	C	D	E	F	G	H	I	J	K
1	id	rate	name	report rate	report name	check rate	check name
2	1	20	james	10	jake	FALSE	FALSE			10	jake
3	2	20	john	20	jame	TRUE	FALSE			20	jame
4	3	30	jill	30	jill	TRUE	TRUE			30	jill
5	4	30	jake	40	john	FALSE	FALSE			40	john
6
7	check rate	All
8	check name	All
9
10	id	rate	report rate	name	report name
11	1	20	10	james	jake
12	2	20	20	john	jame
13	3	30	30	jill	jill
14	4	30	40	jake	john
15
16
17
18
Sheet20

Cell Formulas
Range	Formula
D2:E5	D2	=J2
F2:G5	F2	=B2=D2

 

[jlopj]

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?

 

[awoohaw]

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:

Check Rates FALSE, Names ALL

Check Rates All, Names False:

All False: