List from a Table

[XKruodo]

Hi,

Suppose i have some data across 2 columns starting A2 and B2. Owner of car,
and Car Model respectively.

Column A may contain repeated entries (Owner can have more than 1 car of
different companies AND can also have 2 same cars. ) B also contains
repeated entries.

abc ford
def ford
xyz nissan
xyz suzuki
efg honda
abc toyota


I want to enter in C1 name of the owner and get list of cars he has across
C2:C5000. Same in D1, name of car and get list of owners across D25000.

 

[Lars-Åke Aspelin]

Hi,

Suppose i have some data across 2 columns starting A2 and B2. Owner of car,
and Car Model respectively.

Column A may contain repeated entries (Owner can have more than 1 car of
different companies AND can also have 2 same cars. ) B also contains
repeated entries.

abc ford
def ford
xyz nissan
xyz suzuki
efg honda
abc toyota


I want to enter in C1 name of the owner and get list of cars he has across
C2:C5000. Same in D1, name of car and get list of owners across D25000.

Try this formula in cell C2:

=IF(ROW()<COUNTIF(A$2:A$10000,C$1)+2,INDEX(B$2:B$10000,SMALL(IF(A$2:A$10000=C$1,ROW(A$2:A$10000)-1),ROW()-1)),"")

Note: This is an array formula that has to be confirmed with
CTRL+SHIFT+ENTER rather than just ENTER.

Copy the formula down as far as needed.

For the D column, use the same formula. Just replace A with B and B
with A everywhere.

Hope this helps / Lars-Åke