finding duplicates and concatenate

PEDRO JANUARIO

New Member
Joined
Dec 28, 2013
Messages
2
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

1B1C1D1E1F
DRAWINGDRAWINGDRAWINGDRAWINGDRAWING
ANAANAPEDROMARIAANA
4.0.54.0.64.0.74.0.84.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

Well-known Member
Joined
Jan 15, 2008
Messages
3,541
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

New Member
Joined
Dec 28, 2013
Messages
2
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


ORIGINALEXPECTED RESULT
MIARQ 1BMIARQ 1CMIARQ 1DMIARQ 1EMIARQ 1FANAPEDROMARIA
DRAWINGDRAWINGDRAWINGPAINTINGDRAWINGDRAWINGDRAWINGPAINTING
ANA / PEDROANAPEDROMARIAANAMIARQ 1B / MIARQ 1C / MIARQ 1FMIARQ 1B / MIARQ 1DMIARQ 1E
4.0.54.0.64.0.74.0.84.0.54.0.5 / 4,0,64.0.5 / 4.0.74.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
 

Forum statistics

Threads
1,085,494
Messages
5,383,995
Members
401,871
Latest member
allemandi

Some videos you may like

This Week's Hot Topics

Top