Solved Display many accounts linked to one portfolio on one sheet

Microsoft Office excel 2007
September 28, 2012 at 11:15:38
Specs: Windows 7
Hello all,

EDIT: This is a "Look up one value and return multiple corresponding values" problem that requires an array formula. See Mike's post below for the array formula that worked for me.

Original post:
I need help with a formula that will display multiple accounts linked to a single client portfolio on Sheet1, using the client portfolio code as the reference. The exported data lives on Sheet2, and has the format below. There are hundreds of Portfolios and multiple Accounts per Portfolio.

        Portfolio    Accounts
        SMITHA        1111
        SMITHA        1112
        SMITHA        1113
        JONESA       1115
        JONESB       1120

On Sheet1, I need to be able to list a client portfolio (SMITHA for example), and have all the accounts related to SMITHA show up in separate rows under that portfolio.

        SMITHA
          1111
          1112
          1113

Using Vlookup only grabs the first account number, which is a problem as some client portfolios have as many as 9 accounts associated with it.

What code can I use on Sheet1 that will look in Sheet2 (the data sheet) at Column A for all rows with a certain client portfolio (SMITHA), and then return all related accounts in Column B on Sheet1?

Or is there code I can use on Sheet2 (the data sheet) that will look for all similar client portfolios, and then list the associated account numbers in new columns, so I can then use Vlookup on Sheet1 and have it pull a new column_index_num for each account there is?

Thank you very much!


See More: Display many accounts linked to one portfolio on one sheet

Report •


#1
September 28, 2012 at 11:54:58
✔ Best Answer
It's a bit long & uses an Array formula:

http://office.microsoft.com/en-us/e...

MIKE

http://www.skeptic.com/


Report •

#2
September 28, 2012 at 13:14:33
Thank you so much Mike, I would have never figured that out on my own.

I also didn't know exactly what to call my problem either, but it's a "Look up one value and return multiple corresponding values" problem that requires an array formula to solve. I'll edit my subject (if I can) to help others in the future.

Also, once you have a formula pasted into a cell, you need to hit CTRL+SHIFT+ENTER in order for the array formula to work. Took me a couple minutes to figure that out.

Check out the link that Mike posted for a step by step guide by Ashish Mathur.

With some careful editing of the formula from Ashish, I am able to pull exactly what I was looking for.

Thanks again Mike!


Report •

Related Solutions


Ask Question