List from a Table

  • Thread starter Thread starter XKruodo
  • Start date Start date
X

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 D2:D5000.
 
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 D2:D5000.
Click to expand...

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
 
Back
Top