Formula which looks across Column for and with same value then applies formula

[tomcavs]

Hi,

I require a function which looks at a ID in a certain column and only compares values across respective rows for that ID:

1) Function looks at cells containing the same ID
2) Looks at which currency is first alphabetically and then if PAY or REC to determine the Buyer

Issue I am having is restricting the formula to just check across Rows where the ID is the same when dragging down the column

[TABLE="width: 225"]
<tbody>[TR]
[TD][TABLE="width: 289"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Currency[/TD]
[TD]Value[/TD]
[TD]Buyer[/TD]
[/TR]
[TR]
[TD]503[/TD]
[TD]CAD[/TD]
[TD]REC[/TD]
[TD]Buyer[/TD]
[/TR]
[TR]
[TD]503[/TD]
[TD]USD[/TD]
[TD]PAY[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]503[/TD]
[TD]CAD[/TD]
[TD]PAY[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]503[/TD]
[TD]USD[/TD]
[TD]REC[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]504[/TD]
[TD]CAD[/TD]
[TD]PAY[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]504[/TD]
[TD]USD[/TD]
[TD]REC[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]504[/TD]
[TD]CAD[/TD]
[TD]REC[/TD]
[TD]Buyer[/TD]
[/TR]
[TR]
[TD]504[/TD]
[TD]USD[/TD]
[TD]PAY[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Thanks,
Tom

 

[tomcavs]

sorry ignore above...

Formula looks good. except the Buy / Sell is an output field. I need to formula to look at Currency vs. PAY / REC.

I need to ascertain the buyer and the buyer is the Entity which receives (REC) the currency that is first alphabetically.

 

[Aladin Akyurek]

sorry ignore above...

Formula looks good. except the Buy / Sell is an output field. I need to formula to look at Currency vs. PAY / REC.

I need to ascertain the buyer and the buyer is the Entity which receives (REC) the currency that is first alphabetically.

You mean the condition must be REC, not Buy?

 

[tomcavs]

Yes

 

[Aladin Akyurek]

You mean the condition must be REC, not Buy?

Yes

Control+shift+enter and copy down:

=IF($E2="REC",INDEX($B$2:$B$9,MATCH(MIN(IF($A$2:$A$9=A2,IF($E$2:$E$9="REC",CODE(LEFT(UPPER($D$2:$D$9))),""),"")),IF($A$2:$A$9=A2,IF($E$2:$E$9="REC",CODE(LEFT(UPPER($D$2:$D$9))),""),""),0)),"")

 

[tomcavs]

One more additional query, how can I vlookup so that blanks are ignored in a list of transaction numbers (dupes removed):

[TABLE="width: 377"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Input[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Ref Number[/TD]
[TD]Buyer ID[/TD]
[/TR]
[TR]
[TD]D10517422[/TD]
[TD]G123456[/TD]
[/TR]
[TR]
[TD]D10517422[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]D20517422[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]D20517422[/TD]
[TD]T1123456[/TD]
[/TR]
[TR]
[TD]D10517423[/TD]
[TD]G123456[/TD]
[/TR]
[TR]
[TD]D10517423[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Output[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Ref Number[/TD]
[TD]Buyer ID[/TD]
[/TR]
[TR]
[TD]D10517422[/TD]
[TD]G123456[/TD]
[/TR]
[TR]
[TD]D20517422[/TD]
[TD]T123456[/TD]
[/TR]
[TR]
[TD]D10517423[/TD]
[TD]G123456[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

Book1
	A	B	C	D	E	F
1	Input				Output
2	Ref Number	Buyer ID			Ref Number	Buyer ID
3	D10517422	G123456			D10517422	G123456
4	D10517422				D20517422	T1123456
5	D20517422				D10517423	G123456
6	D20517422	T1123456
7	D10517423	G123456
8	D10517423
Sheet1

In F3 control+shift+enter, not just enter, and copy down:

=INDEX($B$3:$B$8,SMALL(IF($A$3:$A$8=E3,IF($B$3:$B$8<>"",ROW($B$3:$B$8)-ROW($B$3)+1)),COUNTIFS($E$3:E3,E3)))