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

 

[Aladin Akyurek]

What is the output which must obtain given this sample?

 

[tomcavs]

For each ID I need to ascertain who is the BUYER for each group of ID's, the condition is BUYER = REC & CCY first alphabetically.

I built a list of alphabetical currencies and numbered them. The key is comparing both REC/PAYs and the CCY's but just restricting the function to that ID as I drag down.

 

[Aladin Akyurek]

Care to post the output for this sample for the conditions you specify? I guess the answer/the output is obvious to you, but it's not necessarily so for others, hence the request. It's not clear for example whether you want the buyer name or the currency...

Book1
	A	B	C	D	E	F	G
1	ID	Currency	Value	Buyer		503	tom
2	503	CAD	REC	tom		504	dan
3	503	USD	PAY
4	503	CAD	PAY
5	503	USD	REC
6	504	CAD	PAY
7	504	USD	REC
8	504	CAD	REC	dan
9	504	USD	PAY
Sheet1

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

=INDEX($D$2:$D$9,MATCH(F1,IF($C$2:$C$9="REC",IF(1-($D$2:$D$9=""),$A$2:$A$9)),0))

 

[Nishant94]

Is this what you are looking for:

Book1
	A	B	C	D
1	ID	Currency	Value	Buyer
2	503	CAD	REC	Buyer
3	503	USD	PAY
4	503	CAD	PAY
5	503	USD	REC
6	504	CAD	PAY
7	504	USD	REC
8	504	CAD	REC	Buyer
9	504	USD	PAY
10
Sheet6

Cell Formulas
Range	Formula
D2	{=IF((1-COUNTIFS(A$1:A1,A2,D$1:D1,"Buyer"))*(C2="REC"),IF(CHAR(MIN(IF(A$1:A2=A2,CODE(MID(B$1:B2,1,1)))))=MID(B2,1,1),"Buyer",""),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[tomcavs]

Sorry, thanks for help.

The output field is BUYER. We need know who the buyer is by ascertaining who receives the currency which is first alphabetically for each trade.

Need to restrict the lookup to each ID. Hope this clarifies?

 

[Aladin Akyurek]

Sorry, thanks for help.

The output field is BUYER. We need know who the buyer is by ascertaining who receives the currency which is first alphabetically for each trade.

Need to restrict the lookup to each ID. Hope this clarifies?

No it doesn't. Whay don't you try to post the output which is associated with the sample you posted?

 

[tomcavs]

Ok, one last try...

The below are FX swap transactions, to determine the buyer (Buying Entity) I require a formula that looks individually at each transaction reference (4 below) and ascertain the Buyer by:

Looking at each transaction number
decipher which currency across the transaction number is first alphabetically, (currently done by a separate numbered list)
Then determine who is receiving that currency.
For the corresponding line the Entity column will be the Buyer

i.e. the Buyer is the Entity which receives the currency that is first alphabetically. Require a drag down rule to decipher

[TABLE="width: 662"]
<tbody>[TR]
[TD]Transaction ref[/TD]
[TD]Entity[/TD]
[TD]Counterparty[/TD]
[TD]Currency[/TD]
[TD]PAY / REC[/TD]
[TD]Alphabetical order[/TD]
[TD]Buy / Sell[/TD]
[TD]Buyer[/TD]
[/TR]
[TR]
[TD]D10517028[/TD]
[TD]ABC LTD[/TD]
[TD]ABC Inc[/TD]
[TD]EUR[/TD]
[TD]REC[/TD]
[TD]First[/TD]
[TD]Buy[/TD]
[TD]ABC LTD[/TD]
[/TR]
[TR]
[TD]D10517028[/TD]
[TD]ABC LTD[/TD]
[TD]ABC Inc[/TD]
[TD]USD[/TD]
[TD]PAY[/TD]
[TD]Second[/TD]
[TD]Sell[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]D20517028[/TD]
[TD]ABC LTD[/TD]
[TD]ABC Inc[/TD]
[TD]EUR[/TD]
[TD]PAY[/TD]
[TD]First[/TD]
[TD]Sell[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]D20517028[/TD]
[TD]ABC LTD[/TD]
[TD]ABC Inc[/TD]
[TD]USD[/TD]
[TD]REC[/TD]
[TD]Second[/TD]
[TD]Sell[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]D10517029[/TD]
[TD]ABC Inc[/TD]
[TD]ABC LTD[/TD]
[TD]EUR[/TD]
[TD]PAY[/TD]
[TD]First[/TD]
[TD]Sell[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]D10517029[/TD]
[TD]ABC Inc[/TD]
[TD]ABC LTD[/TD]
[TD]USD[/TD]
[TD]REC[/TD]
[TD]Second[/TD]
[TD]Sell[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]D20517029[/TD]
[TD]ABC Inc[/TD]
[TD]ABC LTD[/TD]
[TD]EUR[/TD]
[TD]REC[/TD]
[TD]First[/TD]
[TD]Buy[/TD]
[TD]ABC Inc[/TD]
[/TR]
[TR]
[TD]D20517029[/TD]
[TD]ABC Inc[/TD]
[TD]ABC LTD[/TD]
[TD]USD[/TD]
[TD]PAY[/TD]
[TD]Second[/TD]
[TD]Sell[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thanks,
Tom

 

[Aladin Akyurek]

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

=IF($G2="buy",INDEX($B$2:$B$9,MATCH(MIN(IF($A$2:$A$9=A2,IF($G$2:$G$9="buy",CODE(LEFT(UPPER($D$2:$D$9))),""),"")),IF($A$2:$A$9=A2,IF($G$2:$G$9="buy",CODE(LEFT(UPPER($D$2:$D$9))),""),""),0)),"")

 

[tomcavs]

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

Buyer is the Entity which receives the currency that is first alphabetically. Require a drag down rule to decipher