Hello,
I would be very appreciative of Excel expert's help.
I tried several formulas to count the values in two columns that do not match but they do not work:
=SUMPRODUCT(--(DataTable!A2:A150<>""),--(DataTable!A2:A150='Pivot Table'!A2:A150))
=COUNTA(DataTable!A:A)-SUM(IF(IFERROR(MATCH('Pivot Table'!$A$2:$A$65,DataTable!$A$2:$A$145,0),0)>0,1,0))-1
=SUM(($A$2:$A$150<>"")*1,(DataTable!$A$2:$A$150=$A$2:$A$150)*1)
Both columns are located on two different spreadsheets with an actual pivot table on Pivot Table tab. Pivot Table sheet contains 64 records which all are also listed in Data Table sheet. So the formula should return 80 records that are not matched. I need to account for blank cells because every week the pivot table and a data table will have a different number of rows. So I was thinking to set a maximum range, lets say 50000 rows.
The sample of data is attached.
SUMPRODUCT.xlsx
Bookmarks