find, sort, and copy data
 

Hi, everybody,
I am desperately looking for a solution to do the following:
I have a list of students with courses they are taking. Unfortunately this
list (which is imported from another program) comes with all the courses
jumbled.
What I need is to automatically sort the courses in each line according to a
pre-defined order.
Example:
student A e1 c5 u7 a7
student B u7 e1 - c5
student C e9 u7 t8 -
should be turned into
student A a7 c5 e1 u7
student B - c5 e1 u7
student C t8 e9 - u7
The order of courses is not alphabetical, it depends on the timetable.
The first column should (for example) only contain courses t8, a7, u2
the second colum
c5, e9, k6.

find, sort, and copy data
 

On Sun, 19 Mar 2006 13:21:28 -0800, Hank Camarero <Hank
wrote:

Hi, everybody,
I am desperately looking for a solution to do the following:
I have a list of students with courses they are taking. Unfortunately this
list (which is imported from another program) comes with all the courses
jumbled.
What I need is to automatically sort the courses in each line according to a
pre-defined order.
Example:
student A e1 c5 u7 a7
student B u7 e1 - c5
student C e9 u7 t8 -
should be turned into
student A a7 c5 e1 u7
student B - c5 e1 u7
student C t8 e9 - u7
The order of courses is not alphabetical, it depends on the timetable.
The first column should (for example) only contain courses t8, a7, u2
the second colum
c5, e9, k6.


You need to set up a table someplace of what courses are allowed in which
columns.

I have assumed that a student could not have two courses that go in the same
column. e.g. a student could not have both t8 and a7.

So, given a table J1:M4

Col 1 Col2 Col3 Col4
t8 c5 e1 u7
a7 e9
u2 k6


Given your original data in A1:E3

Given your rearranged data in A6:E8

B6:
=INDEX($B1:$E1,,MATCH(TRUE,ISNUMBER(
MATCH($B1:$E1,J$2:J$4,FALSE)),FALSE))

entered as an **array** formula. In order to do that, after typing or pasting
in the formula, you must hold down <ctrl<shift while hitting <enter. Excel
will place braces {...} around the formula.

Then copy/drag across to E6; and copy/drag B6:E6 down to B8:E8

Obviously you will be making changes in the cell references. When you do so,
it is important to keep the mixed absolute/relative referencing intact, or else
when you copy/drag the references will not adjust properly.

The formula will give #N/A errors where you want blanks. One suggestion is to
use conditional formatting to "white-out" the NA error messages. You could
select the range B6:E8
Format/Conditional Formatting
Formula Is: =ISNA(B6)
Format Set font color to the same as the background (e.g. white)
<OK<OK

Or you could write a more complex formula:

=IF(ISNA(MATCH(TRUE,ISNUMBER(
MATCH($B1:$E1,J$2:J$4,FALSE)),
FALSE)),"",INDEX($B1:$E1,,MATCH(
TRUE,ISNUMBER(MATCH($B1:$E1,
J$2:J$4,FALSE)),FALSE)))

also entered as an **array** formula.


--ron