Multiple Conditions and Multiple Worksheets

[sfdan]

Hello,

I trying to figure out a formula in Worksheet 1 that will compare the holdings in Worksheet 2 to the restrictions in Worksheet 3. I've included a table below, but please note the following:

• The restrictions are identified by the sedol.
• The XYZ Weight in Worksheet 2 indicates the security is held, maybe have zeros or blanks.
• The Restriction a described in Worksheet 3 may contain the description in Worksheet 1, but may not be exact. (i.e. Iran should include Sudan/Iran)
• The XYZ Restricted in Worksheet 3 is only a true/false indication: 1 = true
• The ending result in Worksheet 1 can be True/False

I would post what I've come up with so far, but it not even close to working correctly...any help would be appreciated. My head is hurting on this one!!

Worksheet 1		Worksheet 2		Worksheet 3
Restriction	XYZ		Sedol	XYZ Weight		Sedol	Restriction	XYZ Restricted
Tobacco	True/False		B17MMZ4	1		B17MMZ4	N. Ireland
N. Ireland	True/False		4057808	2		4057808	N. Ireland
Iran	True/False		B39GHT7	3		B39GHT7	Tobacco	1
			6378217	-		6378217	Sudan
			6100186	5		6100186	Sudan
			B298VS3	-		B298VS3	Sudan
			6557997	7		6557997	Sudan/Iran	1
			B3W5NN7	8		B3W5NN7	Sudan

<tbody>
</tbody>

 

[Robert Mika]

Hello,

I trying to figure out a formula in Worksheet 1 that will compare the holdings in Worksheet 2 to the restrictions in Worksheet 3. I've included a table below, but please note the following:

• The restrictions are identified by the sedol.
• The XYZ Weight in Worksheet 2 indicates the security is held, maybe have zeros or blanks.
• The Restriction a described in Worksheet 3 may contain the description in Worksheet 1, but may not be exact. (i.e. Iran should include Sudan/Iran)
• The XYZ Restricted in Worksheet 3 is only a true/false indication: 1 = true
• The ending result in Worksheet 1 can be True/False

I would post what I've come up with so far, but it not even close to working correctly...any help would be appreciated. My head is hurting on this one!!

Worksheet 1		Worksheet 2		Worksheet 3
Restriction	XYZ		Sedol	XYZ Weight		Sedol	Restriction	XYZ Restricted
Tobacco	True/False		B17MMZ4	1		B17MMZ4	N. Ireland
N. Ireland	True/False		4057808	2		4057808	N. Ireland
Iran	True/False		B39GHT7	3		B39GHT7	Tobacco	1
			6378217	-		6378217	Sudan
			6100186	5		6100186	Sudan
			B298VS3	-		B298VS3	Sudan
			6557997	7		6557997	Sudan/Iran	1
			B3W5NN7	8		B3W5NN7	Sudan

<tbody>
</tbody>

if I take this correctly.
If there is match to in Sheet 3(Tobacco) and this return value has a match in Sheet 2(B39GHT7) the result in Sheet1 is True/False?
Are there any other condition?
Like Sudan -its meets the same condtion but is not on your return Sheet 1

 

[sfdan]

If there is match to in Sheet 3(Tobacco) and this return value has a match in Sheet 2(B39GHT7) the result in Sheet1 is True/False?

Yes, it should be TRUE in Worksheet 1.

The conditions for "TRUE" include: 1) the weight should be greater than 0; 2) the XYZ restricted must be equal to 1; 3) The Sedols must match 4) the Restriction (tobacco, Sudan) must match the summary in Worksheet 1. It would be also be great if the formula could recognize that Iran includes Sudan/Iran...

 

[Robert Mika]

Yes, it should be TRUE in Worksheet 1.

4) the Restriction (tobacco, Sudan) must match the summary in Worksheet 1.

What's summary?
And if your lookuop was Sudan you have got Sudan with no Restricstion and Sudan/Iran with Restriction.
What's the expect result?

 

[sfdan]

What's summary?
And if your lookuop was Sudan you have got Sudan with no Restricstion and Sudan/Iran with Restriction.
What's the expect result?

Sorry, the summary would be Worksheet 1: TRUE/FALSE. The objective of that worksheet is to indicate if any of the restricted SEDOLs in Worksheet 3 are held by XYZ, as indicated in Worksheet 2. Worksheet 1 would summarize the results by catagory (i.e. Sudan, Iran, Tobacco, etc.).

Obviously, the workbook is much larger that shown above, I was trying simplify it somewhat for this exercise.

 

[sfdan]

Bump