INDEX(MATCH) or something like that

[strangedenial]

Hi all,

I know it is so easy to do but I guess I can not ask the right question to dear friend Google so I can not find the right answer. So here I am.

What I want to do is below, please help.

Thanks in advance!

Clients	Service 1	Service 2	Service 3
AAA	Enlglish	Spanish
BBB	Italian	English	Spanish
CCC	Spanish
DDD	English	Italian
EEE	Spanish	English
FFF	Italian
GGG	English

<tbody>
</tbody>

that was the clients and services table, and the result I want is :

Results 1		Results 2
CLIENTS FOR ENGLISH		CLIENTS FOR SPANISH
AAA		AAA
BBB		BBB
DDD		CCC
EEE		EEE
GGG

<tbody>
</tbody>

I hope showing was more understandable than explaining in words.


Thanks for any help

 

[Andrew Poulsom]

Welcome to MrExcel.

Formula in F2 copied down and across:

Excel 2010
	A	B	C	D	E	F	G
1	Clients	Service 1	Service 2	Service 3		English	Spanish
2	AAA	English	Spanish			AAA	AAA
3	BBB	Italian	English	Spanish		BBB	BBB
4	CCC	Spanish				DDD	CCC
5	DDD	English	Italian			EEE	EEE
6	EEE	Spanish	English			GGG
7	FFF	Italian
8	GGG	English
Sheet1

Cell Formulas
Range	Formula
F2	{=IFERROR(INDEX($A$2:$A$8,SMALL(IF(ISNUMBER(SEARCH(F$1,$B$2:$D$8)),ROW($B$2:$D$8)-ROW($B$2)+1),ROWS(F$2:F2))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[strangedenial]

Thanks a lot, perfect!

 

[strangedenial]

Ok one more detail I fotgot;

I have some clients who take enligsh for 2 persons, so for them Service 1 and Service 2 show both english.
So this formula writes that client's name twice.

How not to duplicate the same client if they have 2 or more same services ?

Thanks!

 

[squirrel31]

This formula should highlight the row/rows you don't need (Providing you do it in Conditional Formatting). Then all you would need to do is filter by colour and remove any duplicated records.

=COUNTIF($A$2:A2,A2)>1

 

[Andrew Poulsom]

How about?

=IFERROR(INDEX($A$2:$A$8,SMALL(IF((ISNUMBER(SEARCH(F$1,$B$2:$B$8))+ISNUMBER(SEARCH(F$1,$C$2:$C$8))+ISNUMBER(SEARCH(F$1,$D$2:$D$8))),ROW($B$2:$D$8)-ROW($B$2)+1),ROWS(F$2:F2))),"")

confirmed with Ctrl+Shift+Enter.

 

[strangedenial]

Andrew Poulsom - You must be the Excel god I've been seeking for! ))

Thanks a lot!
Perfect!