Multiple Conditions and Multiple Worksheets

S

sfdan

New Member
Joined
Sep 10, 2012
Messages
4
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!!

[TABLE="width: 616"]
<tbody>[TR]
[TD="colspan: 2"]Worksheet 1[/TD]
[TD][/TD]
[TD="colspan: 2"]Worksheet 2[/TD]
[TD][/TD]
[TD="colspan: 3"]Worksheet 3[/TD]
[/TR]
[TR]
[TD]Restriction[/TD]
[TD]XYZ[/TD]
[TD][/TD]
[TD]Sedol[/TD]
[TD]XYZ Weight[/TD]
[TD][/TD]
[TD]Sedol[/TD]
[TD]Restriction[/TD]
[TD]XYZ Restricted[/TD]
[/TR]
[TR]
[TD]Tobacco[/TD]
[TD]True/False[/TD]
[TD][/TD]
[TD]B17MMZ4[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD]B17MMZ4[/TD]
[TD]N. Ireland[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]N. Ireland[/TD]
[TD]True/False[/TD]
[TD][/TD]
[TD]4057808[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD]4057808[/TD]
[TD]N. Ireland[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Iran[/TD]
[TD]True/False[/TD]
[TD][/TD]
[TD]B39GHT7[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD]B39GHT7[/TD]
[TD]Tobacco[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6378217[/TD]
[TD="align: right"]-[/TD]
[TD][/TD]
[TD]6378217[/TD]
[TD]Sudan[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6100186[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD]6100186[/TD]
[TD]Sudan[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]B298VS3[/TD]
[TD="align: right"]-[/TD]
[TD][/TD]
[TD]B298VS3[/TD]
[TD]Sudan[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]6557997[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD="align: right"]6557997[/TD]
[TD]Sudan/Iran[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]B3W5NN7[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD]B3W5NN7[/TD]
[TD]Sudan[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Sort by date Sort by votes
sfdan said:
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!!

[TABLE="width: 616"]
<tbody>[TR]
[TD="colspan: 2"]Worksheet 1[/TD]
[TD][/TD]
[TD="colspan: 2"]Worksheet 2[/TD]
[TD][/TD]
[TD="colspan: 3"]Worksheet 3[/TD]
[/TR]
[TR]
[TD]Restriction[/TD]
[TD]XYZ[/TD]
[TD][/TD]
[TD]Sedol[/TD]
[TD]XYZ Weight[/TD]
[TD][/TD]
[TD]Sedol[/TD]
[TD]Restriction[/TD]
[TD]XYZ Restricted[/TD]
[/TR]
[TR]
[TD]Tobacco[/TD]
[TD]True/False[/TD]
[TD][/TD]
[TD]B17MMZ4[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD]B17MMZ4[/TD]
[TD]N. Ireland[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]N. Ireland[/TD]
[TD]True/False[/TD]
[TD][/TD]
[TD]4057808[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD]4057808[/TD]
[TD]N. Ireland[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Iran[/TD]
[TD]True/False[/TD]
[TD][/TD]
[TD]B39GHT7[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD]B39GHT7[/TD]
[TD]Tobacco[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6378217[/TD]
[TD="align: right"]-[/TD]
[TD][/TD]
[TD]6378217[/TD]
[TD]Sudan[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6100186[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD]6100186[/TD]
[TD]Sudan[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]B298VS3[/TD]
[TD="align: right"]-[/TD]
[TD][/TD]
[TD]B298VS3[/TD]
[TD]Sudan[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]6557997[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD="align: right"]6557997[/TD]
[TD]Sudan/Iran[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]B3W5NN7[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD]B3W5NN7[/TD]
[TD]Sudan[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

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
 
Upvote 0
Robert Mika said:
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?
Click to expand...

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...
 
Upvote 0
sfdan said:
Yes, it should be TRUE in Worksheet 1.

4) the Restriction (tobacco, Sudan) must match the summary in Worksheet 1.
Click to expand...
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?
 
Upvote 0
Robert Mika said:
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?
Click to expand...

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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

V
  • Solved
Need VBA to split data into multiple sheets paste formulas, formatting AND data validation
Replies
2
Views
265
vanessavalentino83
V
A
  • Solved
Need help with worksheet/conditional formatting/macros please
Replies
7
Views
111
Aphten
A
N
  • Solved
Excel 2016 VBA - Pull data from multiple sheets to One with specificities
Replies
3
Views
659
mumps
mumps
D
  • Question Question
Print Multiple invoices in one PDF File
Replies
1
Views
433
John_w
J
B
  • Solved
VBA to check if worksheet exists
Replies
8
Views
523
bdt
B

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top