hierarchy formula

Sorry to bother you guys yet again

but i am a newbie to excel , and iv e got majority of the books but it doesn’t help me in what i am trying to achieve

i am trying to create a summary where i analyse all the info particular “corpid (column A), chain id (column B),External ID (column d), on sheet1 , and determine at the highest level do we have an email address

i don’t know if this can be done in excel , however i am hoping it can be

I am probably not describing this too well but, hopefully this will make sense

column A, row 2-9 are the same company, it is linked to a chain, and it has individual outlets, however there isn’t any email address provided

column B row 10-15, there isn’t a corporate account, however there is a chain, and outlets and we have an email address ( in this instance the highest level we have the email address is an chain)


column B row 16-18, there isn’t a corporate account, however there is a chain, and outlets and we don’t have an email address ( in this instance the highest level is the chain but we don’t have an email address )

i am trying to determine, what is the highest level and do we have an email address for them

Hi,

Might it not be better to extract a unique list of email addresses and then pick the highest level.
See the new sheet 2 I've attached.

Regards

Hi,

I'm not sure whether you're saying that the result I gave you is what you want or not.

You seemed to be saying that you wanted the email address associated with the highest level. I turned the logic around and said OK, let's use all the email addresses and find the highest level in each column associated with that email address

If this is not what you want then manually create and give some specific examples of the results you expect to see in a new sheet.

Regards

You could take Richard's approach one step further and use Text to Columns to split out the name and the domain.

See the attached sample workbook, based on Richard's example.

Regards, TMS

Hi,

Essentially yes.

1479 records, 861 rows with email addresses of which 629 were unique records.
Of the 629 unique emails, 11 were at the CORPID level, 78 at the CHAINID level and all 629 were to be found at ACCOUNTID and EXTERNALID

Regards