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

ID Currency Value Buyer 503 CAD REC Buyer 503 USD PAY 503 CAD PAY 503 USD REC 504 CAD PAY 504 USD REC 504 CAD REC Buyer 504 USD PAY <tbody> </tbody>

<tbody>
</tbody>

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):

Input
Ref Number	Buyer ID
D10517422	G123456
D10517422
D20517422
D20517422	T1123456
D10517423	G123456
D10517423
Output
Ref Number	Buyer ID
D10517422	G123456
D20517422	T123456
D10517423	G123456

<colgroup><col><col></colgroup><tbody>
</tbody>

 

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