separate and sort a concatenated list

palaeontology

Active Member
Joined
May 12, 2017
Messages
375
Office Version
  1. 2016
Platform
  1. Windows
In cell BX65 I have a concatenated list of maths textbook chapter units that a student is weak on ...
1600533178844.png

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 ....
1600533709423.png

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
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,405
Office Version
  1. 365
Platform
  1. Windows
If it is being concatenated in excel then why not just grab the original un-concatenated list from the source?
 

palaeontology

Active Member
Joined
May 12, 2017
Messages
375
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Dec 30, 2008
Messages
12,405
Office Version
  1. 365
Platform
  1. Windows
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

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499

ADVERTISEMENT

use XL2BB to post representative source example and expected result
 

palaeontology

Active Member
Joined
May 12, 2017
Messages
375
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Dec 30, 2008
Messages
12,405
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

* 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

Active Member
Joined
May 12, 2017
Messages
375
Office Version
  1. 2016
Platform
  1. Windows
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 ....
1600693970735.png

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
APAQARASATAUAVAWAX
652935 2731 2627 2733 2732 2731 2830 2627 2626 2625 26242735 2733 2835
Data (by STUDENT)
Cell Formulas
RangeFormula
AP65:AX65AP65=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

Well-known Member
Joined
Dec 30, 2008
Messages
12,405
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,009
Messages
5,622,145
Members
415,881
Latest member
tasic89

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
Top