A Solution

• Jenny has a problem that she has multiple cells she would like to join into one.
  • The problem is they are not in order
  • 
• Start with this workbook
• This might be easier in the future
  • There is a sort function,
  • but only in office 365.
• Small might work
  • But that only works on numbers
• So I found a method on line.
  • This is somewhat obtuse
  • But let's break it down.
• First we need to find a way to order the data.
  • An array function will work here.
  • The trick is to count values smaller than the current value.
  • Of course you need an array to do this.
  • But
  • First Convert the "words" to be numbers
    • In f1, count the number of values in a1:d1 less than or equal to a1
    • Copy this over to g1, h1 and i1
    • Copy this down.
    • 
    • This works for any values
  • Next, use small to put these in order
    • This is an array function
    • Remember column(1:4) will return the array {1,2,3,4}
    • Highlight K1:N1
    • 
    • Remember the ctrl-alt-enter
    • Copy this down
    • 
  • What we know
    • The second column tells us that
      • B = 2
      • A = 1
      • D = 4
      • C = 3
      • 
    • The third column tells us that the order is 1234
    • So if we can find the index of 1 in the second column and match that to the letter in the first column we are golden.
  • Find the position of each letter
    • We want the position (match) of a number in K:M in F:G
    • This should be an array computation
    • 
    • 
    • In the picture
      • 
      • The 1 in column k, can be found in the fourth position in F:1
      • So the item in the forth position of A:D should be first.
      • Or look at Q:S 4123, A:D BCDA
        • The A in position 4, goes first
        • The B in position 1 goes second
        • The C in position 3 goes third
        • The D in position 3 goes fourth
  • Finally an index will do
    • The range is A:D, the indexes are the array P:S
    • As an array, in U:X
    • 
    • 
  • Now just join the text.
    • A concatenate and substitute will do
    • 
    •