How may I lookup and return a value between two numbers
 

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 in our troop are interested in taking this summer at camp. Although no Scout will likely take more than five badges / activities, I've asked each to select his top 15 preferences from a group of around 80 offerings - to overcome the possibility of scheduling conflicts. The questionaire I distributed grouped the names of these offerings in three seperate columns, with columns to the right of each to record their preference.

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

Any one of up to three discrete members of the set could occur in every row of the 28-row-deep array...(e.g. there might be zero, one, two or three members of the set in columns A, C, & E in each of the 28 rows in 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 member of the set occurs (A, C & E) to another (set of 1-3) column(s) if 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 of the copied value.

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

Thank you

Dear HGJ, Good Morning.

Your explanation was good but I´m a little confused on how to arrange 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.

Thanks, here's the link:

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

HELP from BRAZIL <<<<<

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 the understanding process. They can be erased, of course.