separate and sort a concatenated list

[palaeontology]

In cell BX65 I have a concatenated list of maths textbook chapter units that a student is weak on ...

This particular student is weak on 14 chapter units, but other students might have a smaller or longer list than this one (tehir lists would also be found in column BX)

What I would like is a formula (preferably not VB code) that does several things ...

* starting in cell BZ65 and spreading out to the right (ie: CA65, CB65, CC65, etc etc) un-concatenate this list into separate cells (ie: each chapter unit in the list gets its own cell)
* make the list unique (that is ... remove duplicates) ... in this example, chapter unit 4I appears twice ... I would only want it to appear once
* sort the list into ascending order .. ie: it should end up looking like the list below ....

I can un-concatenate the list using an inefficient and VERY long series of 'mid(find(len' calculations, but I am hopeless at incorporating the other two facets into a single formula (the 'unique' bit, and the 'ascending order' bit)

Is this all possible in formula form, or would I need to go to vb, which I prefer to avoid ... remember, the concatenated list won't always be 14 chapter units long .. some students might only be weak on a few units, while others might be weak on 20 or more units)

As this will be used by other teachers who are not overly Excel-savvy, I'd prefer to have this task automated by formula, therefore avoiding the need for them to press a series of key-presses from the ribbon.

Any assistance would be greatly appreciated,

very kind regards,

Chris

 

[jasonb75]

If it is being concatenated in excel then why not just grab the original un-concatenated list from the source?

 

[palaeontology]

Because, in the example given above, the 14 elements of the concatenated list didn't come from 14 separate individual cells, but about 9 .... Ie: some cells that feed into the concatenated list in BX65 contain one Textbook Chapter unit (eg: 1A), but some of the 'feeder cells' contain more than one chapter unit (eg: 4K 4L 3A).

 

[jasonb75]

some of the 'feeder cells' contain more than one chapter unit (eg: 4K 4L 3A).

When the sheet was designed that might have seemed like a good idea but I expect it is going to be problematic now.

Are chapters always 1 or 2 digits followed by a letter? If there is no consistent pattern then getting the sort order that you want might not be possible.

Even with a pattern it is not going to be easy, with normal sort order using the examples provided 13F would come before 1A. If you had a chapter 20A then that would be sorted between 1A and 2A.

Are all chapters delimited by spaces in the string?

 

[sandy666]

use XL2BB to post representative source example and expected result

 

[palaeontology]

Unfortunately, chapters range from 1 to 13

However, all chapter units will be either 2 or 3-digits long ...
* eg: 2-digit long ... 4F
* eg: 3-digit long ... 13D

is it possible to have a formula that does the following (note: the 3rd dot is different from my original 3rd dot) ....

* starting in cell BZ65 and spreading out to the right (ie: CA65, CB65, CC65, etc etc) un-concatenate this list into separate cells (ie: each chapter unit in the list gets its own cell)
* make the list unique (that is ... remove duplicates) ... in this example, chapter unit 4I appears twice ... I would only want it to appear once
* sort the list into ascending order by first making two separate lists that are each sorted ....
* the first list would separate (and sort) just those entries that are 2-digits long
* the second list would separate (and sort) just those entries that are 3-digits long

 

[jasonb75]

* sort the list into ascending order by first making two separate lists that are each sorted ....
* the first list would separate (and sort) just those entries that are 2-digits long
* the second list would separate (and sort) just those entries that are 3-digits long

That is increasing the impossibility factor tenfold.

The methods that I've tried so far have not been anywhere close to what you need, splitting is possible but sorting and removing duplicates with older versions of excel is not (if it was just numbers then it would be easy, but the letter suffixes require different approaches). If you had office 365 with the new dynamic array functions then it might be possible, I'm not familiar enough with them to say for certain.

I think that the only way that you will be able to do what you need will be with vba, if you can use that as an option then I'll look at it in more detail.

 

[palaeontology]

ok, you gave me a great idea.

I've converted each of my chapter units into 4-digit number codes using the following vlookup tables ....

So the student's personal list of chapter units they are weak in is now seen as 4-digit numbers .....

Yr 11 Unit 1&2 Exam Areas of weakness.xlsx
	AP	AQ	AR	AS	AT	AU	AV	AW	AX
65	2935	2731	2627	2733 2732	2731 2830	2627	2626 2625 2624	2735 2733	2835
Data (by STUDENT)

Cell Formulas
Range	Formula
AP65:AX65	AP65	=IFERROR(INDEX(Lookups!$B$4:$I$37,MATCH(H65,Lookups!$B$4:$B$37,0),8),"")

so, are we now able to construct a formula that can do three things with this list ...

* starting in cell BZ65 and spreading out to the right (ie: CA65, CB65, CC65, etc etc) un-concatenate this list into separate cells (ie: each 4-digit number in the list gets its own cell)
* make the list unique (that is ... remove duplicates) ... in this example, number 2733 appears twice ... I would only want it to appear once
* sort the list into descending order

If so, I can then convert the newly ordered list back into their original format (eg: 4F)

Very kind regards,

Chris

 

[jasonb75]

Are you still concatenating the list above into BX65?

The formula will either need all of the numbers in one cell or each individual number in a separate cell. Having them splattered across the sheet as in your example above will still not work.

Perhaps a better way to look at it might be the source of the numbers in AP65:AX65, if you're converting them from 1A, 2A, etc to those numbers then they must be in individual cells at the source, which would probably be a better source for the sorting formula than what you're trying to work with.