finding duplicates and concatenate

[PEDRO JANUARIO]

Hi, i'm using excel 2008 for mac, so i can't run any VBA or macros, just formulas!

can any one help me??
i have this cells listed below

1B	1C	1D	1E	1F
DRAWING	DRAWING	DRAWING	DRAWING	DRAWING
ANA	ANA	PEDRO	MARIA	ANA
4.0.5	4.0.6	4.0.7	4.0.8	4.0.5

<!--StartFragment--> <colgroup><col width="65" span="5"> </colgroup><tbody>
<!--EndFragment--></tbody>

and i want the result to bloke this

ANA is for example the name that i want to find

and i want to get this sort.

DRAWING
1B / 1C / 1F
4.0.5 / 4.0.6

<colgroup><col width="65"></colgroup><tbody>
</tbody>

THANKS
PEDRO

​

 

[iliace]

My first suggestion would be to normalize the data. In this case, it looks like you need to transpose it.

Next, filter on the ANA column.

Finally, use Remove Duplicates or similar functionality to achieve your result.

 

[PEDRO JANUARIO]

well i'm doing a very complex schedule, i have 150 classes, 5 days, 9 classes schedules by day, with 160 professors, and 50 classrooms...
around 20000 entries

ORIGINAL						EXPECTED RESULT
MIARQ 1B	MIARQ 1C	MIARQ 1D	MIARQ 1E	MIARQ 1F		ANA	PEDRO	MARIA
DRAWING	DRAWING	DRAWING	PAINTING	DRAWING		DRAWING	DRAWING	PAINTING
ANA / PEDRO	ANA	PEDRO	MARIA	ANA		MIARQ 1B / MIARQ 1C / MIARQ 1F	MIARQ 1B / MIARQ 1D	MIARQ 1E
4.0.5	4.0.6	4.0.7	4.0.8	4.0.5		4.0.5 / 4,0,6	4.0.5 / 4.0.7	4.0.8

<tbody>
</tbody>

at the moment i have this formulas

for the classname =IF(ISERROR(INDEX('MATRIZ TURMA'!$D$5:$EY$158;$A7;MATCH(CONCATENATE("*";D$5;"*");'MATRIZ TURMA'!$D8:$EY8;0)));"";INDEX('MATRIZ TURMA'!$D$5:$EY$158;$A7;MATCH(CONCATENATE("*";D$5;"*");'MATRIZ TURMA'!$D8:$EY8;0)))
for the class itself =IF(ISERROR(INDEX('MATRIZ TURMA'!$D$5:$EY$158;$A8;MATCH(CONCATENATE("*";D$5;"*");'MATRIZ TURMA'!$D8:$EY8;0)));"";INDEX('MATRIZ TURMA'!$D$5:$EY$158;$A8;MATCH(CONCATENATE("*";D$5;"*");'MATRIZ TURMA'!$D8:$EY8;0)))
for the classroom =IF(ISERROR(INDEX('MATRIZ TURMA'!$D$5:$EY$158;$A9;MATCH(CONCATENATE("*";D$5;"*");'MATRIZ TURMA'!$D8:$EY8;0)));"";INDEX('MATRIZ TURMA'!$D$5:$EY$158;$A9;MATCH(CONCATENATE("*";D$5;"*");'MATRIZ TURMA'!$D8:$EY8;0)))

but this formulas only applied the the MARIA case shown above. what i really need if wen i have duplicates..., tha it the ANA and PEDRO cases

​