Flag values in a list of pairs?

**unipsychologist** · 01-30-2015, 11:43 PM

Hi, I'm trying to flag particular values within a long list of pairs. Using the example below, the formula should evaluate which value each pair is the smallest, and return a text comment to it's right (such as the word "retain"). The larger value may remain blank. Note, I don't want to evaluate across pairs (for example, "a" to "b"), only within pairs ("a" to "a'). And I need to achieve this using a formula, with no help from filtering or sorting.

PAIR: VALUE: FLAG:
a 1 "retain"
a 2
b 3
b 1 "retain"
c 3
c 2 "retain"

Any help is greatly appreciated!
~Noob

**:) Sixthsense :)** · 01-30-2015, 11:57 PM

	A	B	C
1	PAIR:	VALUE:	FLAG:
2	a	1	Retain
3	a	2
4	b	3
5	b	1	Retain
6	c	3
7	c	2	Retain

	A	B	C
1	PAIR:	VALUE:	FLAG:
2	a	1	=IF(MIN(IF(($A$2:$A$7=A2),$B$2:$B$7,9^9))=B2,"Retain","")
3	a	2	=IF(MIN(IF(($A$2:$A$7=A3),$B$2:$B$7,9^9))=B3,"Retain","")
4	b	3	=IF(MIN(IF(($A$2:$A$7=A4),$B$2:$B$7,9^9))=B4,"Retain","")
5	b	1	=IF(MIN(IF(($A$2:$A$7=A5),$B$2:$B$7,9^9))=B5,"Retain","")
6	c	3	=IF(MIN(IF(($A$2:$A$7=A6),$B$2:$B$7,9^9))=B6,"Retain","")
7	c	2	=IF(MIN(IF(($A$2:$A$7=A7),$B$2:$B$7,9^9))=B7,"Retain","")

Column C formula is an Array Formula-Requires CTRL+SHIFT+ENTER

Array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

**unipsychologist** · 01-31-2015, 12:46 AM

I must be misunderstanding something about your method because I couldn't replicate it successfully, but fortunately all my pairs are in order, so I was able to solve it this way:
=IF(CHOOSE(MOD(ROW(A1),2)+1, B2 < B1,B2 < B3),"retain","")

Thanks so much for your help anyway!