SUMPRODUCT ISNUMBER SEARCH to return summed values of single/multiple adjacent cells

Firstly, this is my first post. I have been a lurker a couple of months though and find answers here invaluable. I want to put the masters to the test myself now with what I see as a tricky piece of business with Excel.

So here is my urgent Sunday brainteaser for you Excel genii...

The data I have consists of company name; date; name of shareholder; percentage of shares held for each of top 10 shareholders along the row.

In other words each row has variables:

Company ID; date; SH1; SH1%; SH2; SH2%; SH3; SH3% etc etc etc up to SH10%

I have a separate list of shareholder names I want to separate out into a binary variable (1/0) & a summation of the percentages for that firm at that time.

In other words, if John Doe, Jack Snow and Jane Schmoe are the shareholders I want to separate out then I will do the following:

Please Login or Register  to view this content.

...This seems to do the trick.

I can add more logical arguments to this to cover all the shareholder names I want to separate out. That part is no longer a big problem, thanks to this forum.

So here's my more tricky question to the masters:

Let's say my formula brings back a "1" - i.e. it finds either John or Jane as top 10 shareholders in ACME company, or alternative, it may have found John alone, or John, Jane and Jack or any such combination together in any of the positions 1 through to 10.

Now, instead of bring back a 1 or a 0, I want to do something different as follows.

If the formula finds only John Doe, or it finds Jack Snow, or it finds Jane Schmoe as top 10 shareholders, then I want their corresponding percentage holdings to be returned to the cell.

So for example, if the above code returns "1" into W2 because John Doe has 20% holding in the company, I want X2 to return "20%", which is going to be next to the cell in which John Doe appears, either as SH1, SH2, ... , SH10.

However, if both John Doe AND Jane Schmoe AND/OR Jack Snow appear in the top 10 for that row, I want X2 to return the sum of each of the cells NEXT TO (ie corresponding to) their shareholding. So if John has 10% and is SH4, Jane has 9% and is SH6 and Jack has 7% and is SH8, I want SH%4 SH%6 and SH%8 to be summed and returned to X2.

In exactly the same way, if the same row showed John was in fact SH1 with 60%; Jane was SH5 with 30% and Jack was SH10 with 2%, I would want the formula to return 92% to X2 (the sum of the shareholdings only of those 3 shareholders).

I hope that makes sense and I really need your help with this.

Best wishes,

Sam (UK North).

PS. I've now attached a sample of what I mean by the above, 2 posts down.

Sam,

That's a lot of information to visualise mentally. Could you post a sample workbook with dummy data?
It will make it far easier for someone here to help out.

Spencer.

Thanks for the quick response, Spencer, working on the sample workbook now...
It is attached to this post.

I am sure there are many ways to do this, I am looking for the simplest and more than 1 brain tends to be better than 1 in these things I've found - especially when I am no where near as expert as many here.

Please note, the dataset I am working with contains around 40,000 rows, and the search will require around 169 OR clauses/argument to fish out the shareholder names, not just 3.

UPDATE: I have also noticed that I cannot do the

Please Login or Register  to view this content.

for more than 31 names - and I have around 170. The only way around this I know would be to split the 170 into 6 separate groups and run the formula down 6 columns, then use an

Please Login or Register  to view this content.

. If you can do better, please do let me know anyone who is out there...

Sam,

Could you explain the use of column W to me a little?
Is it used purely to show if one or more named person is a the top 10 shareholder only or are the 1's and 0's used for something specific elsewhere too?

Sorry for the delay.

Formulas now added to column X.
Rather than hard code the names of interest into the formula, I've added them to a named range on Sheet2 called NameArray. This approach will make it far easier when working with many names and you could make NameArray a dynamic range so the range length automatically adjusts if/when you have to add more names.

The formula checks if the names of the 10 shareholders listed on each row appear in NameArray and if they do it sums their adjacent values.

The bonus of this is you don't need column W any longer.

Let me know if you need any of that explaining.

Hope it's what you needed.

Spencer.

Thanks very much indeed. Already starred you and will check this out and feeback.

update: OK, so the formula and array method looks great. I will try it with the real data today or this week.

Follow up question:

In some cases, a shareholder appears, but in a different form, so I do not want them to be counted.

For example.

ACMECompany SH1:John Doe Sh1per 10% SH2 Jane Doe & Associates 0.8%

In this case, I do not want Jane Doe and Associates to be counted, as they are not an individual shareholder.

So in other words, how could I ensure that if a cell contains say, "Jane Doe & Associates" and another contains say "John Doe Parent Holdings Ltd" are not counted whereas cells such as "John Doe - person"; "John Doe" and "John Doe - J. Doe." are counted, or a "but not" clause.

Thanks again Spencer, awesome work.

If I've understood your question correctly, this shouldn't be anything to worry about. Using your example above, unless "Jane Doe & Associates" is listed in the NameArray it will not be included in the total, even though Jane Doe is included in NameArray.

So if NameArray contains only individual shareholders rather than 'group' shareholders then you will have no problems.

But I feel that's possibly too easy an answer so I may have misunderstood your question.

So what you are saying is the NameArray cells must be an exact match to the shareholder name cells.
That is an issue because a real example might be:

United States Bank

but it also might be

United States Bank Corporation
United States Bk
United States Banking

So I would use United States in the name array to pick up the variations.

But if I did not want to pick up...

United States Airlines
United States Airlines and Airways
United States Airline Banking Services Subsidiaries Ltd.

In other words, if the cell contains the NameArray but also contains another word or piece of info, I don't want it.

The practical side of this is that with my data I might have a company, say XYZ company
And it has a shareholder I am interested in, say John Doe X, John Doe Y, John Doe Z (e.g. John Doe Limited, John Doe Ltd, John Doe Enterprises)
But there is also another form of shareholder like John Doe Partnership with America Bank; John Doe Limited - Uniglow Joint Venture; John Doe & Associates Banking Trust.

Do you get my drift here?
I need enough flexibility to pick out all the shareholders I want, but not to pick up the ones which include the NameArray word, but I do not want them.

The named range (NameArray) should ONLY include the names you ARE interested in.

i.e. you include:
John Doe Limited
John Doe Ltd
John Doe Enterprises

because you are interested in those, but do not include:

John Doe Partnership with America Bank
John Doe Limited - Uniglow Joint Venture
John Doe & Associates Banking Trust

in that list of names becasue you're not interested in those then it should work.

Am I missing the point completely here? *scratched head*

Is there a reason the data comes to you with so many variations of the same thing? i.e. why John Doe Banking in one place but John Doe BK in another?

I think the easiest solution is curing the problem at the stem rather than trying to work around it further down the line.

In my line of work we often deal with workbooks that are hundreds of thousands of rows of data, and millions of cells. This leaves much room for "data quality issues". Each time we feed new data in, it has to go through a rigorous cleanse first to limit the sort of issues I'm sure you'll come up against with this. Without knowing far more about the ins and outs of your data capture and manipulation process it's hard to suggest a suitable workaround.

Well I can see two simple options. The simplicity level of the second one would very much depend on the layout of the raw data you receive.

1) Get the company providing you with the data to cleanse it for you prior to sending.
2) Have a table with all the possible variations listed against their correct names and use VLOOKUP or INDEX/MATCH to correct the data yourselves prior to feeding into your workbook.

These are only suggestions of course as I have no idea what you receive, how often, how much there is, what the general state of it is.
We use several data providers for my work and we've tasked them all with cleansing exactly these issues prior to sending us. Amazing what a few carefully chosen lines in a contract will acheive when it comes to SLAs!