Reconcile huge data set with multiple criteria filters

[blublublub]

Hi, i'm having trouble reconciling the following data sets. Basically I have 2 sheets and needs reconciling. I have done a pivot table to make reconciliation easier but run into trouble when i add more criterias.

Filter criteria as follows:
1st sheet: Filter Y (N/Y), I (I/E) - 2nd sheet: Filter Y (N/Y)

Sum Values through all time period (D1-D7) of each individual asset and portfolio
e.g. Asset (AAA), PF (1) = Value (57) should match with the same sum on sheet 2.

The number of portfolios and asset classes are alot.
I dont need an exact answer... just need ideas on how to approach or simplify this.... thanks in advance




1st sheet

[TABLE="width: 384"]
<colgroup><col width="64" span="6" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Portfolio2[/TD]
[TD="width: 64"]Asset2[/TD]
[TD="width: 64"]Time2[/TD]
[TD="width: 64"]Value2[/TD]
[TD="width: 64"]N/Y[/TD]
[TD="width: 64"]I/E[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]AAA[/TD]
[TD]D1[/TD]
[TD="align: right"]23[/TD]
[TD]Y[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]AAA[/TD]
[TD]D3[/TD]
[TD="align: right"]32[/TD]
[TD]Y[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]AAA[/TD]
[TD]D7[/TD]
[TD="align: right"]2[/TD]
[TD]Y[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]AAA[/TD]
[TD]D2[/TD]
[TD="align: right"]52[/TD]
[TD]Y[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]AAA[/TD]
[TD]D3[/TD]
[TD="align: right"]5[/TD]
[TD]N[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]AAA[/TD]
[TD]D7[/TD]
[TD="align: right"]2[/TD]
[TD]Y[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]AAA[/TD]
[TD]D7[/TD]
[TD="align: right"]1230[/TD]
[TD]Y[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]AAA[/TD]
[TD]D1[/TD]
[TD="align: right"]23[/TD]
[TD]N[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]AAA[/TD]
[TD]D2[/TD]
[TD="align: right"]2[/TD]
[TD]Y[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD]AAA[/TD]
[TD]D1[/TD]
[TD="align: right"]2[/TD]
[TD]Y[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD]AAA[/TD]
[TD]D1[/TD]
[TD="align: right"]31[/TD]
[TD]Y[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]CCC[/TD]
[TD]D1[/TD]
[TD="align: right"]4[/TD]
[TD]Y[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]CCC[/TD]
[TD]D2[/TD]
[TD="align: right"]24[/TD]
[TD]Y[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]CCC[/TD]
[TD]D1[/TD]
[TD="align: right"]46[/TD]
[TD]Y[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]CCC[/TD]
[TD]D7[/TD]
[TD="align: right"]9[/TD]
[TD]Y[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]DDD[/TD]
[TD]D1[/TD]
[TD="align: right"]12[/TD]
[TD]Y[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]DDD[/TD]
[TD]D7[/TD]
[TD="align: right"]2[/TD]
[TD]Y[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]DDD[/TD]
[TD]D1[/TD]
[TD="align: right"]34[/TD]
[TD]N[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]DDD[/TD]
[TD]D7[/TD]
[TD="align: right"]4[/TD]
[TD]Y[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

2nd sheet

[TABLE="width: 320"]
<colgroup><col width="64" span="5" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Portfolio[/TD]
[TD="width: 64"]Asset[/TD]
[TD="width: 64"]Time[/TD]
[TD="width: 64"]Value[/TD]
[TD="width: 64"]Y/N[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]AAA[/TD]
[TD]D1[/TD]
[TD="align: right"]23[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]AAA[/TD]
[TD]D3[/TD]
[TD="align: right"]32[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]AAA[/TD]
[TD]D7[/TD]
[TD="align: right"]2[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]AAA[/TD]
[TD]D2[/TD]
[TD="align: right"]52[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]AAA[/TD]
[TD]D3[/TD]
[TD="align: right"]5[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]AAA[/TD]
[TD]D7[/TD]
[TD="align: right"]2[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]AAA[/TD]
[TD]D7[/TD]
[TD="align: right"]123[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]AAA[/TD]
[TD]D1[/TD]
[TD="align: right"]23[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]AAA[/TD]
[TD]D2[/TD]
[TD="align: right"]2[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]CCC[/TD]
[TD]D1[/TD]
[TD="align: right"]4[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]CCC[/TD]
[TD]D2[/TD]
[TD="align: right"]24[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]CCC[/TD]
[TD]D1[/TD]
[TD="align: right"]46[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]CCC[/TD]
[TD]D7[/TD]
[TD="align: right"]9[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]DDD[/TD]
[TD]D1[/TD]
[TD="align: right"]12[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]DDD[/TD]
[TD]D7[/TD]
[TD="align: right"]2[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]DDD[/TD]
[TD]D1[/TD]
[TD="align: right"]34[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]DDD[/TD]
[TD]D7[/TD]
[TD="align: right"]4[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]