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]

 

[shg]

Why not just autofilter column F?

 

[Hiten_pan]

At the moment we are using filters but I want this to be automated. Let me know if you can help.

 

[Hiten_pan]

Are you able to assist?

 

[sheetspread]

The only single formula I know to do this requires ctrl-shift-enter, otherwise you need a helper column.

 

[sheetspread]

This CSE formula is well known:

Excel 2010
	A	B	C	D	E	F	G	H	I	J
1	Unique #	Acct	Cusip	Direction	Broker Accruals	PIMCO Accruals			PIMCO Accruals	Unique #
2	695CCHUSIUS4	695	CCHUSIUS4	IP	1738.51	1733.52			CHECK Acct#	3199CCHUSIUS4
3	1023CCHUSIUS4	1023	CCHUSIUS4	IP	1109.51	1076.08			CHECK Acct#	7893CCHUSIUS4
4	3199CCHUSIUS4	3199	CCHUSIUS4	IP	1099.89	CHECK Acct#
5	1203CCHUSIUS4	1203	CCHUSIUS4	IP	772.23	773.85
6	2755CCHUSIUS4	2755	CCHUSIUS4	IP	417.71	417.66
7	96CCHUSIUS4	96	CCHUSIUS4	IP	194.31	194.57
8	2462CCHUSIUS4	2462	CCHUSIUS4	IP	181.3	181.55
9	3684CCHUSIUS4	3684	CCHUSIUS4	IP	119.94	120
10	7893CCHUSIUS4	7893	CCHUSIUS4	IP	85.5	CHECK Acct#
11	935CCHUSIUS4	935	CCHUSIUS4	IP	80.13	80.13
Sheet1

Cell Formulas
Range	Formula
J2	{=INDEX($A$1:$A$11,SMALL(IF($F$2:$F$11="CHECK Acct#",ROW($F$2:$F$11)),ROW(A1)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

But you can have this instead:

Excel 2010
	A	B	C	D	E	F	G	H	I	J
1	Unique #	Acct	Cusip	Direction	Broker Accruals	PIMCO Accruals			PIMCO Accruals	Unique #
2	695CCHUSIUS4	695	CCHUSIUS4	IP	1738.51	1733.52	0		CHECK Acct#	3199CCHUSIUS4
3	1023CCHUSIUS4	1023	CCHUSIUS4	IP	1109.51	1076.08	0		CHECK Acct#	7893CCHUSIUS4
4	3199CCHUSIUS4	3199	CCHUSIUS4	IP	1099.89	CHECK Acct#	1
5	1203CCHUSIUS4	1203	CCHUSIUS4	IP	772.23	773.85	0
6	2755CCHUSIUS4	2755	CCHUSIUS4	IP	417.71	417.66	0
7	96CCHUSIUS4	96	CCHUSIUS4	IP	194.31	194.57	0
8	2462CCHUSIUS4	2462	CCHUSIUS4	IP	181.3	181.55	0
9	3684CCHUSIUS4	3684	CCHUSIUS4	IP	119.94	120	0
10	7893CCHUSIUS4	7893	CCHUSIUS4	IP	85.5	CHECK Acct#	2
11	935CCHUSIUS4	935	CCHUSIUS4	IP	80.13	80.13	0
Sheet1

Cell Formulas
Range	Formula
G2	=IF(F2="CHECK Acct#",COUNTIF($F$2:F2,"CHECK Acct#"),0)
J2	=INDEX($A$2:$A$11,MATCH(ROW(A1),$G$2:$G$11,0))

 

[markmzz]

Hi!

Try the formula below (with Sheetspread's layout) in J2 and copy down:

=INDEX(A$2:A$11,MATCH(0,INDEX(COUNTIF(J$1:J1,A$2:A$11)+(F$2:F$11<>I2),),0))

Markmzz

 

[sheetspread]

Hi!

Try the formula below (with Sheetspread's layout) in J2 and copy down:

=INDEX(A$2:A$11,MATCH(0,INDEX(COUNTIF(J$1:J1,A$2:A$11)+(F$2:F$11<>I2),),0))

Markmzz

Nice!

 

[KevCarter]

I really like the examples given by SheetSpread and Markmzz above. I'm learning and wanted to go through the exercise. Another example not requiring CSE thanks to Mike Girvin's videos:

Also using Sheetspreads layout in J2 and copy down:

[COLOR=#000000][FONT=&quot]=IFERROR(INDEX[COLOR=#006107]([/COLOR][COLOR=#0057d6]$A$2:$A$11[/COLOR],AGGREGATE[COLOR=#ab30d6]([/COLOR]15,6,[COLOR=#a54a29]([/COLOR]ROW[COLOR=#33af4a]([/COLOR][COLOR=#0057d6]$A$2:$A$11[/COLOR][COLOR=#33af4a])[/COLOR]-ROW[COLOR=#33af4a]([/COLOR][COLOR=#006107]$A$2[/COLOR][COLOR=#33af4a])[/COLOR]+1[COLOR=#a54a29])[/COLOR]/[COLOR=#a54a29]([/COLOR][COLOR=#ab30d6]$F$2:$F$11[/COLOR]="CHECK Acct#"[COLOR=#a54a29])[/COLOR],ROWS[COLOR=#a54a29](J$2:J2)[/COLOR][COLOR=#ab30d6])[/COLOR][COLOR=#006107])[/COLOR],"")[/FONT][/COLOR]

 

[sheetspread]

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