How may I lookup and return a value between two numbers

[HGJ]

Greetings;

I'm the advancement chairman for a Boy Scout troop in Texas.

I've assembled the rankings of merit badge classes that the Scouts i
our troop are interested in taking this summer at camp. Although n
Scout will likely take more than five badges / activities, I've aske
each to select his top 15 preferences from a group of around 8
offerings - to overcome the possibility of scheduling conflicts. Th
questionaire I distributed grouped the names of these offerings in thre
seperate columns, with columns to the right of each to record thei
preference.

My dataset is integers consisting of the numbers 1 through 15 inclusiv
(e.g. 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15). Each o
these integers will occur only once, but could occur in any cell i
columns A, C & E in the array A1:E28.

Any one of up to three discrete members of the set could occur in ever
row of the 28-row-deep array...(e.g. there might be zero, one, two o
three members of the set in columns A, C, & E in each of the 28 rows i
the array, but no value will be repeated (I've already scrubbed it.)

I need a means to:

a. copy the data in the cell(s) from the three columns in which a membe
of the set occurs (A, C & E) to another (set of 1-3) column(s) i
necessary - to the right of the array,

b. sort this data into ascending order & - most importantly,

c. copy the name of the badge which appears in the cell to the left o
the copied value.

I can do all this manually for the ~20 or so Scouts for whom I hav
collected data, but I thought I'd see if the process can b
automated...and more importantly, how I can structure my surve
instrument next year so as to facilitate the data collection.

Thank yo

 

[HGJ]

Thanks, here's the link:

http://www.4shared.com/folder/fX4lBDxt/_online.html

Dear *HGJ*, Good Morning.

Your explanation was good but I´m a little confused on how to arrang
data on the worksheet.
Have you got a starter worksheet where we can work on it?
It will be easier.

Arrange data as you desire.
Create a little example with the waited results.
Don´t worry about formulas now.
Just type results.
The most important is to understand your real necessity to help you.

Save your model at a free site, e.g. www.4shared.com and put link here.

It will be a pleasure to help you on your task

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[Mazzaropi]

Thanks, here's the link:
http://www.4shared.com/folder/fX4lBDxt/_online.html

Dear HGJ, Good Evening.

The a,b,c options of your explanation are OK now.

Tell me what is the next step in this Boy Scout Project.

Take a look at it and tell me if it worked for you.

I left some initial formulas at bottom part of worksheet to easier th
understanding process. They can be erased, of course

+-------------------------------------------------------------------
|Filename: 29-04-2012-ExcelBanter_Boy_Scout_Project_1-0.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=351
+-------------------------------------------------------------------