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

 

[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

Transaction ref	Entity	Counterparty	Currency	PAY / REC	Alphabetical order	Buy / Sell	Buyer
D10517028	ABC LTD	ABC Inc	EUR	REC	First	Buy	ABC LTD
D10517028	ABC LTD	ABC Inc	USD	PAY	Second	Sell
D20517028	ABC LTD	ABC Inc	EUR	PAY	First	Sell
D20517028	ABC LTD	ABC Inc	USD	REC	Second	Sell
D10517029	ABC Inc	ABC LTD	EUR	PAY	First	Sell
D10517029	ABC Inc	ABC LTD	USD	REC	Second	Sell
D20517029	ABC Inc	ABC LTD	EUR	REC	First	Buy	ABC Inc
D20517029	ABC Inc	ABC LTD	USD	PAY	Second	Sell

<tbody>
</tbody>

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