Hi friends,
I am working on a project where I would need below raw data to show expected results as shown below.
Basically, it is an outlier report where only "Check Accts" will be flagged. Thanks!!!
I am looking for the formulae without {} brackets, it maybe combination of Index Match function
Raw data
[TABLE="width: 530"]
<tbody>[TR]
[TD]Unique #[/TD]
[TD]Acct[/TD]
[TD]Cusip[/TD]
[TD]Direction[/TD]
[TD]Broker Accruals[/TD]
[TD]PIMCO Accruals[/TD]
[/TR]
[TR]
[TD]695CCHUSIUS4[/TD]
[TD]695[/TD]
[TD]CCHUSIUS4[/TD]
[TD]IP[/TD]
[TD]1738.51[/TD]
[TD="align: right"]1733.52[/TD]
[/TR]
[TR]
[TD]1023CCHUSIUS4[/TD]
[TD]1023[/TD]
[TD]CCHUSIUS4[/TD]
[TD]IP[/TD]
[TD]1109.51[/TD]
[TD="align: right"]1076.08[/TD]
[/TR]
[TR]
[TD]3199CCHUSIUS4[/TD]
[TD]3199[/TD]
[TD]CCHUSIUS4[/TD]
[TD]IP[/TD]
[TD]1099.89[/TD]
[TD]CHECK Acct#[/TD]
[/TR]
[TR]
[TD]1203CCHUSIUS4[/TD]
[TD]1203[/TD]
[TD]CCHUSIUS4[/TD]
[TD]IP[/TD]
[TD]772.23[/TD]
[TD="align: right"]773.85[/TD]
[/TR]
[TR]
[TD]2755CCHUSIUS4[/TD]
[TD]2755[/TD]
[TD]CCHUSIUS4[/TD]
[TD]IP[/TD]
[TD]417.71[/TD]
[TD="align: right"]417.66[/TD]
[/TR]
[TR]
[TD]96CCHUSIUS4[/TD]
[TD]96[/TD]
[TD]CCHUSIUS4[/TD]
[TD]IP[/TD]
[TD]194.31[/TD]
[TD="align: right"]194.57[/TD]
[/TR]
[TR]
[TD]2462CCHUSIUS4[/TD]
[TD]2462[/TD]
[TD]CCHUSIUS4[/TD]
[TD]IP[/TD]
[TD]181.3[/TD]
[TD="align: right"]181.55[/TD]
[/TR]
[TR]
[TD]3684CCHUSIUS4[/TD]
[TD]3684[/TD]
[TD]CCHUSIUS4[/TD]
[TD]IP[/TD]
[TD]119.94[/TD]
[TD="align: right"]120[/TD]
[/TR]
[TR]
[TD]7893CCHUSIUS4[/TD]
[TD]7893[/TD]
[TD]CCHUSIUS4[/TD]
[TD]IP[/TD]
[TD]85.5[/TD]
[TD]CHECK Acct#[/TD]
[/TR]
[TR]
[TD]935CCHUSIUS4[/TD]
[TD]935[/TD]
[TD]CCHUSIUS4[/TD]
[TD]IP[/TD]
[TD]80.13[/TD]
[TD="align: right"]80.13[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col></colgroup>[/TABLE]
Expected results
[TABLE="width: 209"]
<tbody>[TR]
[TD]PIMCO Accruals[/TD]
[TD]Unique #[/TD]
[/TR]
[TR]
[TD]CHECK Acct#[/TD]
[TD]3199CCHUSIUS4[/TD]
[/TR]
[TR]
[TD]CHECK Acct#[/TD]
[TD]7893CCHUSIUS4[/TD]
[/TR]
</tbody><colgroup><col><col></colgroup>[/TABLE]
I am working on a project where I would need below raw data to show expected results as shown below.
Basically, it is an outlier report where only "Check Accts" will be flagged. Thanks!!!
I am looking for the formulae without {} brackets, it maybe combination of Index Match function
Raw data
[TABLE="width: 530"]
<tbody>[TR]
[TD]Unique #[/TD]
[TD]Acct[/TD]
[TD]Cusip[/TD]
[TD]Direction[/TD]
[TD]Broker Accruals[/TD]
[TD]PIMCO Accruals[/TD]
[/TR]
[TR]
[TD]695CCHUSIUS4[/TD]
[TD]695[/TD]
[TD]CCHUSIUS4[/TD]
[TD]IP[/TD]
[TD]1738.51[/TD]
[TD="align: right"]1733.52[/TD]
[/TR]
[TR]
[TD]1023CCHUSIUS4[/TD]
[TD]1023[/TD]
[TD]CCHUSIUS4[/TD]
[TD]IP[/TD]
[TD]1109.51[/TD]
[TD="align: right"]1076.08[/TD]
[/TR]
[TR]
[TD]3199CCHUSIUS4[/TD]
[TD]3199[/TD]
[TD]CCHUSIUS4[/TD]
[TD]IP[/TD]
[TD]1099.89[/TD]
[TD]CHECK Acct#[/TD]
[/TR]
[TR]
[TD]1203CCHUSIUS4[/TD]
[TD]1203[/TD]
[TD]CCHUSIUS4[/TD]
[TD]IP[/TD]
[TD]772.23[/TD]
[TD="align: right"]773.85[/TD]
[/TR]
[TR]
[TD]2755CCHUSIUS4[/TD]
[TD]2755[/TD]
[TD]CCHUSIUS4[/TD]
[TD]IP[/TD]
[TD]417.71[/TD]
[TD="align: right"]417.66[/TD]
[/TR]
[TR]
[TD]96CCHUSIUS4[/TD]
[TD]96[/TD]
[TD]CCHUSIUS4[/TD]
[TD]IP[/TD]
[TD]194.31[/TD]
[TD="align: right"]194.57[/TD]
[/TR]
[TR]
[TD]2462CCHUSIUS4[/TD]
[TD]2462[/TD]
[TD]CCHUSIUS4[/TD]
[TD]IP[/TD]
[TD]181.3[/TD]
[TD="align: right"]181.55[/TD]
[/TR]
[TR]
[TD]3684CCHUSIUS4[/TD]
[TD]3684[/TD]
[TD]CCHUSIUS4[/TD]
[TD]IP[/TD]
[TD]119.94[/TD]
[TD="align: right"]120[/TD]
[/TR]
[TR]
[TD]7893CCHUSIUS4[/TD]
[TD]7893[/TD]
[TD]CCHUSIUS4[/TD]
[TD]IP[/TD]
[TD]85.5[/TD]
[TD]CHECK Acct#[/TD]
[/TR]
[TR]
[TD]935CCHUSIUS4[/TD]
[TD]935[/TD]
[TD]CCHUSIUS4[/TD]
[TD]IP[/TD]
[TD]80.13[/TD]
[TD="align: right"]80.13[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col></colgroup>[/TABLE]
Expected results
[TABLE="width: 209"]
<tbody>[TR]
[TD]PIMCO Accruals[/TD]
[TD]Unique #[/TD]
[/TR]
[TR]
[TD]CHECK Acct#[/TD]
[TD]3199CCHUSIUS4[/TD]
[/TR]
[TR]
[TD]CHECK Acct#[/TD]
[TD]7893CCHUSIUS4[/TD]
[/TR]
</tbody><colgroup><col><col></colgroup>[/TABLE]