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

[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
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,224,558
Messages
6,179,512
Members
452,919
Latest member
krismmmitch

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top