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

Unique #	Acct	Cusip	Direction	Broker Accruals	PIMCO Accruals
695CCHUSIUS4	695	CCHUSIUS4	IP	1738.51	1733.52
1023CCHUSIUS4	1023	CCHUSIUS4	IP	1109.51	1076.08
3199CCHUSIUS4	3199	CCHUSIUS4	IP	1099.89	CHECK Acct#
1203CCHUSIUS4	1203	CCHUSIUS4	IP	772.23	773.85
2755CCHUSIUS4	2755	CCHUSIUS4	IP	417.71	417.66
96CCHUSIUS4	96	CCHUSIUS4	IP	194.31	194.57
2462CCHUSIUS4	2462	CCHUSIUS4	IP	181.3	181.55
3684CCHUSIUS4	3684	CCHUSIUS4	IP	119.94	120
7893CCHUSIUS4	7893	CCHUSIUS4	IP	85.5	CHECK Acct#
935CCHUSIUS4	935	CCHUSIUS4	IP	80.13	80.13

<tbody>
</tbody>

Expected results

PIMCO Accruals	Unique #
CHECK Acct#	3199CCHUSIUS4
CHECK Acct#	7893CCHUSIUS4

<tbody>
</tbody>

 

[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!