Index Match function to find outliers

[Hiten_pan]

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="class: cms_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>[/TABLE]


Expected results
[TABLE="class: cms_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>[/TABLE]

 

[KevCarter]

Also nice. I do think people get a little crazy trying to avoid ctrl-shift-enter though!

Agree. However, in my real world projects, I go with something like your helper column example whenever possible. In my opinion, much easier to look at later and remember what's happening if I need to update or change. It's still fun to learn different ideas like Mark's cool formula above.

Cheers!

 

[markmzz]

Nice!

Thank you.

Markmzz

 

[Hiten_pan]

Sheetspread & KavCarter, you guys are genius, the formula is just mind blowing. too good. I was using {} formulae which was very complex and file gets hanged. Thank god guys, the formulae youall prvided really worked for me.

 

[sheetspread]

Sheetspread & KavCarter, you guys are genius

Markmzz is the genius (but I'm happy to help all the same )

 

[KevCarter]

Sheetspread & KavCarter, you guys are genius, the formula is just mind blowing. too good. I was using {} formulae which was very complex and file gets hanged. Thank god guys, the formulae youall prvided really worked for me.

Thank you, I'm just really good at copy/paste. We can learn so much from Girvin, Jelen, and everybody on this forum like Mark and SheetSpread!!!

 

[markmzz]

Markmzz is the genius (but I'm happy to help all the same )

Hi Sheetspread!

We're all learning here on the board.

The most import thing is that the user's problem was solved.

Markmzz