About Us

Ron Rosenfeld

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

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
HOw do i find and sort out data repeats for specific data set	JRDePaul	Excel Discussion (Misc queries)	0	February 26th 07 11:55 PM
Pls. reply Sort Data and copy to next coulmn when sort order chang	shital shah	Excel Programming	1	August 19th 05 02:51 PM
Sort Data and copy to next coulmn when sort data changes	shital shah	Excel Programming	0	August 18th 05 02:55 PM
Sort Data and copy to next coulmn when sort data changes	shital shah	Excel Programming	0	August 18th 05 06:48 AM
How can I find and sort specific data within a column?	bpreas - ExcelForums.com	Excel Discussion (Misc queries)	3	August 2nd 05 07:11 PM

Menu

About Us