Lookup formula help...

[BigDelGooner]

I've had a look at this for a while and can't find a solution PLEASE HELP!

I have a list of securities with unique IDs, for example...

ID Sec_Name
123-00 Security ABC
456-00 Security DEF
789-00 Security GHI

On a seperate sheet I have a list of groups. These groups list all securities they are invested in (which can be identified by the unique ID on the securities list).

Group ID Sec_Name
GBP_1 123-00 Security ABC
GBP_2 456-00 Security DEF
USD_1 123-00 Security ABC
EUR_1 123-00 Security ABC


On a seperate summary sheet I am looking to group together these groups by currency and mark with a '*' if a security is held within any of these sub-groups. For example the summary table, based on the information above, will look something like this...

ID Sec_Name GBP EUR USD
123-00 Security ABC * * *
456-00 Security DEF *
789-00 Security GHI

Any ideas guys?

 

[Andrew Poulsom]

Try:

=IF(SUMPRODUCT(--(Sheet2!$B$2:$B$5=$A2),--(LEFT(Sheet2!$A$2:$A$5,LEN(C$1))=C$1)),"*","")

 

[BigDelGooner]

Hi Andrew

Thank you for your response. I'm not sure I quite follow it though...

- What do the hypens represent?
- If the security data is on sheet1 and group data is on sheet2, how does this fit into the portfolio?

Sorry for being a bit slow on this and thank you for your help!

 

[Andrew Poulsom]

The formula seems to fit your data arrangement. Did you try it in C2 on Sheet1 copied down and across? The double minuses coerce TRUE/FALSE into 1/0 so that they can be multiplied by SUMPRODUCT. For more on this use of SUMPRODUCT see:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[BigDelGooner]

Hi Andrew

Got the formula sorted but sheet now running very slowly, the calculation seems to be taking ages. The difference between my sheet and the example I have above is that there are around 200 securities and 20 groups.

Do you have any alternative solutions? Do you think it would be quicker if I populated these cells using VBA on sheet activation instead formulas?

 

[Andrew Poulsom]

What formula are you using and in what cells? I applied it to over 200 rows and 3 columns and it wasn't noticeably slow.

 

[BigDelGooner]

Andrew - forgive me, I am being a plank.

We have only recently changed over to Excel 2010 which now has over 1m rows...so I need to get out of the habit of selecting an entire column for lookups (which I used to do in Excel 2003).

Thanks again for all your help.