Help merging, filtering and sorting complex data

[emilyshaw93]

Hi all,

I have a list of data as follows:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]NAME[/TD]
[TD]REFERENCES[/TD]
[TD][/TD]
[TD]NAME (Sorted)[/TD]
[TD]REFERENCES (Sorted)[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]John
[/TD]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD="class: xl63, width: 118"]36, 87, JR, 11, 26[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD]John[/TD]
[TD]JR, 4, 11, 26, 36, 85, 87, 96[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Emily[/TD]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD="class: xl65, width: 118"]63, ES, 14, 1, 35[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD]Emily [/TD]
[TD]ES, 1, 14, 35, 63[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Kate[/TD]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD="class: xl65, width: 118"]KM, 73, 25, 84, 13[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD]Kate[/TD]
[TD]KM, 13, 25, 62, 73, 84, 94[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]John[/TD]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD="class: xl65, width: 118"]85, 96, 4[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Kate[/TD]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD="class: xl65, width: 118"]94, 62, 25, 13[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


What I want to do is get the data in column B to sort into column E automatically. I want multiple data sets for John, for example, to combine, sort in ascending order, and remove duplicates (as shown in E above), automatically.

I have set up column d to sort names and remove duplicates within the list in column A already - I need a formula to do a similar thing for putting the data in column B into column E.

Any help much appreciated!

Emily

 

[Macropod]

Duplicate of http://www.mrexcel.com/forum/excel-questions/905151-sorting-complex-data-excel-help.html
Please read our policy on Cross-Posting in rule #12: http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html

Thread closed.