Good morning Community –
I have a challenge that I have been grappling with for a few days that I have yet to crack and wanted to solicit some ideas from all of you.
What I am trying to do is for each Record ID, answer some questions about the Base Entity. A base entity is any entity at tier 0 and all the units above it is, in some way, its owners. A base entity will have a Regarded Owner(s), a Regarded CFC(s), First Tier CFC(s) and a US Shareholder(s).
Also, the below represents some of the more complex scenarios, most of the work is a lot more linear (except for the top-tier CFC) then this, but these are troubling me the most.
Any time you see Tiers listed more than once, that represents a split-ownership. So, in Scenario 1:
Scenario 1 | |||||
RecordID | TIER | GTN | CY_ENTITY TYPE | ||
21 | 6 | T-TNC | US | <--USSH | |
21 | 5 | NL881 | CFC | <-- First Level CFC | |
21 | 4 | NL123 | FDE | ||
21 | 3 | AU700 | CFC | ||
21 | 3 | T-TNH | US | <--USSH | |
21 | 2 | AU700A | CFC | <-- Regarded CFC & First Level CFC | |
21 | 1 | AU700B | CFP | <-- Regarded Owner | |
21 | 0 | AU07390 | FDE | <-- Base Entity (always Tier 0) | |
Scenario 2 | |||||
RecordID | TIER | GTN | CY_ENTITY TYPE | ||
193 | 5 | BSR | US | <--USSH | |
193 | 5 | IPG | US | <--USSH | |
193 | 4 | CI414 | CFC | <--First Tier CFC | |
193 | 4 | IPG | US | <--USSH | |
193 | 3 | BSR | US | <--USSH | |
193 | 3 | CI414C | CFC | <--First Tier CFC | |
193 | 3 | T-TNC | US | <--USSH | |
193 | 2 | BSR | US | <--USSH | |
193 | 2 | CO580 | CFC | <--First Tier CFC | |
193 | 2 | IPG | US | <--USSH | |
193 | 1 | CO658 | CFC | <-- Regarded Owner, Regarded CFC, and First Tier CFC | |
193 | 0 | VN658 | BRANCH | <-- Base Entity | |
Questions I Need to Answer: | |||||
1) Who is the Base Entities Regarded Owner? | |||||
2) Who are the Base Entities Regarded CFC? | |||||
3) Who Are the Base Entities First Level CFC? 4) Who are the US Shareholder? | |||||
Rules | |||||
Regarded Owner is a CFP or CFC that is closest in the structure to the base entity. (AU700B is the regarded owner | |||||
Regarded CFC is a CFC that is closest to the Base Entity. (AU700A is the regarded CFC) | |||||
A First Level CFC is a CFC who's direct Owner is a US company (AU700A and NL881 are first level CFC's) | |||||
A US Company is a US Shareholder (USSH) |
Challenge
I was thinking some sort of iterative macro, but not sure how to look up and down the entire structure. Not just one above or one below. Maybe there is a way using an iterative macro with a Multi-Row formula tool, but not sure about that either.
At the end of the day, I would like to see in a new column (it could be a seperate column for each question) which entity satisfies the answer to one of my questions.
Thanks,
Seth
Solved! Go to Solution.