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

[TABLE="width: 325"]
<!--StartFragment--> <colgroup><col width="65" span="5"> </colgroup><tbody>[TR]
[TD="class: xl24, width: 65"]1B[/TD]
[TD="class: xl24, width: 65"]1C[/TD]
[TD="class: xl24, width: 65"]1D[/TD]
[TD="class: xl24, width: 65"]1E[/TD]
[TD="class: xl24, width: 65"]1F[/TD]
[/TR]
[TR]
[TD="class: xl24"] [/TD]
[TD="class: xl24"] [/TD]
[TD="class: xl24"] [/TD]
[TD="class: xl24"] [/TD]
[TD="class: xl24"] [/TD]
[/TR]
[TR]
[TD="class: xl24"]DRAWING[/TD]
[TD="class: xl24"]DRAWING[/TD]
[TD="class: xl24"]DRAWING[/TD]
[TD="class: xl24"]DRAWING[/TD]
[TD="class: xl24"]DRAWING[/TD]
[/TR]
[TR]
[TD="class: xl24"]ANA[/TD]
[TD="class: xl24"]ANA[/TD]
[TD="class: xl24"]PEDRO[/TD]
[TD="class: xl24"]MARIA[/TD]
[TD="class: xl24"]ANA[/TD]
[/TR]
[TR]
[TD="class: xl24"]4.0.5[/TD]
[TD="class: xl24"]4.0.6[/TD]
[TD="class: xl24"]4.0.7[/TD]
[TD="class: xl24"]4.0.8[/TD]
[TD="class: xl24"]4.0.5 [/TD]
[/TR]
<!--EndFragment--></tbody>[/TABLE]

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

[TABLE="width: 65"]
<colgroup><col width="65"></colgroup><tbody>[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
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


[TABLE="width: 749"]
<tbody>[TR]
[TD="class: xl24, width: 87"]ORIGINAL[/TD]
[TD="class: xl24, width: 87"][/TD]
[TD="class: xl24, width: 87"][/TD]
[TD="class: xl24, width: 87"][/TD]
[TD="class: xl24, width: 87"][/TD]
[TD="class: xl24, width: 65"][/TD]
[TD="class: xl24, width: 83"]EXPECTED RESULT[/TD]
[TD="class: xl24, width: 83"][/TD]
[TD="class: xl24, width: 83"][/TD]
[/TR]
[TR]
[TD="class: xl24, width: 87"][/TD]
[TD="class: xl24, width: 87"][/TD]
[TD="class: xl24, width: 87"][/TD]
[TD="class: xl24, width: 87"][/TD]
[TD="class: xl24, width: 87"][/TD]
[TD="class: xl24, width: 65"][/TD]
[TD="class: xl24, width: 83"][/TD]
[TD="class: xl24, width: 83"][/TD]
[TD="class: xl24, width: 83"][/TD]
[/TR]
[TR]
[TD="class: xl25, width: 87"]MIARQ 1B[/TD]
[TD="class: xl25, width: 87"]MIARQ 1C[/TD]
[TD="class: xl25, width: 87"]MIARQ 1D[/TD]
[TD="class: xl25, width: 87"]MIARQ 1E[/TD]
[TD="class: xl25, width: 87"]MIARQ 1F[/TD]
[TD="class: xl24, width: 65"][/TD]
[TD="class: xl25, width: 83"]ANA[/TD]
[TD="class: xl25, width: 83"]PEDRO[/TD]
[TD="class: xl25, width: 83"]MARIA[/TD]
[/TR]
[TR]
[TD="class: xl25, width: 87"][/TD]
[TD="class: xl25, width: 87"][/TD]
[TD="class: xl25, width: 87"][/TD]
[TD="class: xl25, width: 87"][/TD]
[TD="class: xl25, width: 87"][/TD]
[TD="class: xl24, width: 65"][/TD]
[TD="class: xl25, width: 83"][/TD]
[TD="class: xl25, width: 83"][/TD]
[TD="class: xl25, width: 83"][/TD]
[/TR]
[TR]
[TD="class: xl25, width: 87"]DRAWING[/TD]
[TD="class: xl25, width: 87"]DRAWING[/TD]
[TD="class: xl25, width: 87"]DRAWING[/TD]
[TD="class: xl25, width: 87"]PAINTING[/TD]
[TD="class: xl25, width: 87"]DRAWING[/TD]
[TD="class: xl24, width: 65"][/TD]
[TD="class: xl25, width: 83"]DRAWING[/TD]
[TD="class: xl25, width: 83"]DRAWING[/TD]
[TD="class: xl25, width: 83"]PAINTING[/TD]
[/TR]
[TR]
[TD="class: xl25, width: 87"]ANA / PEDRO[/TD]
[TD="class: xl25, width: 87"]ANA[/TD]
[TD="class: xl25, width: 87"]PEDRO[/TD]
[TD="class: xl25, width: 87"]MARIA[/TD]
[TD="class: xl25, width: 87"]ANA[/TD]
[TD="class: xl24, width: 65"][/TD]
[TD="class: xl25, width: 83"]MIARQ 1B / MIARQ 1C / MIARQ 1F[/TD]
[TD="class: xl25, width: 83"]MIARQ 1B / MIARQ 1D[/TD]
[TD="class: xl25, width: 83"]MIARQ 1E[/TD]
[/TR]
[TR]
[TD="class: xl25, width: 87"]4.0.5[/TD]
[TD="class: xl25, width: 87"]4.0.6[/TD]
[TD="class: xl25, width: 87"]4.0.7[/TD]
[TD="class: xl25, width: 87"]4.0.8[/TD]
[TD="class: xl25, width: 87"]4.0.5[/TD]
[TD="class: xl24, width: 65"][/TD]
[TD="class: xl25, width: 83"]4.0.5 / 4,0,6[/TD]
[TD="class: xl25, width: 83"]4.0.5 / 4.0.7[/TD]
[TD="class: xl25, width: 83"]4.0.8[/TD]
[/TR]
</tbody>[/TABLE]




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

​