return multiple values horizontally while removing duplicates

**kingi** · 04-24-2013, 04:26 AM

Hello,

I'm attaching a sample of what I'm looking for:
excelforum.xlsx

I will try to explain -
Every company showed few times in the table,
each time it mentions 2 countries.

Now I built a new table, which have the name of the companies.

I'm looking for a formula that bring me the different countries of each company horizontally,
but skip countries that mentioned twice (or more).

For me it's really tough, so maybe someone here could help me. I'll really appreciate it.
Thanks in advance!

**kingi** · 04-24-2013, 09:45 AM

Someone?

**kingi** · 04-24-2013, 10:31 AM

So i decided to move to ONE column in my original table, so I have this formula that works perfectly: (ignore the cells numbers)

=INDEX($D$2:$D$23415, SMALL(IF($I$4=$A$2:$A$23415, ROW($A$2:$A$23415)-ROW($A$2)+1), COLUMN(A1)))

The only question is: How do I remove duplicates?

**AZ-XL** · 04-24-2013, 03:55 PM

Hi
I think its diffucult question. I have answer but its indirect and inefficient. If this kind of solution satisfies you it can be improved.
Uploading file to analyze.

Hope excel experts will have better solutions for your example.