Hi
I am trying to create a formula to list all the accounts for a list of people (people's names are in column A, accounts are in column B). The problem is that each person may have more than one account (or none at all).
So far I have managed to create the following array formula to return all the accounts for one person:
=IF(ISERROR(INDEX(A:B,SMALL(IF(A:B="Mr Jones",ROW(A:B)),ROW(1:1)),2)),"",INDEX(A:B,SMALL(IF(A:B="Mr Jones",ROW(A:B)),ROW(1:1)),2))
This will return all the accounts for Mr Jones until there are none left (at which point the cell will remain blank). I cannot work out how to write the formula so that instead of returning a blank cell it will instead search for the next person in the list (and then the next person etc...)
I think it will be some kind of nested formula within a formula but cannot get it to work.
Any ideas?
I am trying to create a formula to list all the accounts for a list of people (people's names are in column A, accounts are in column B). The problem is that each person may have more than one account (or none at all).
So far I have managed to create the following array formula to return all the accounts for one person:
=IF(ISERROR(INDEX(A:B,SMALL(IF(A:B="Mr Jones",ROW(A:B)),ROW(1:1)),2)),"",INDEX(A:B,SMALL(IF(A:B="Mr Jones",ROW(A:B)),ROW(1:1)),2))
This will return all the accounts for Mr Jones until there are none left (at which point the cell will remain blank). I cannot work out how to write the formula so that instead of returning a blank cell it will instead search for the next person in the list (and then the next person etc...)
I think it will be some kind of nested formula within a formula but cannot get it to work.
Any ideas?
