putting data in order(?)

R

Rutgers_Excels

I have 3 columns: A B and C

Column C is a range named list that is used in a combo box

Column B has a list of data from B1:B12

In column A, a person would put the letter "x" when they wanted th
data from column B to be in the combo box (in other words, column C).

My question is, is there a code I can use to put the data in order i
column C? For instance, if cells A3 and A7 had checks in them, th
data in cells B3 and B7 would show up in cells C1 and C2 respectively.
Then, if the "x" was deleted from A3, B7 would then be in cell C1.

Any help would be appreciated
 
K

Kristen Lindholm

Here is a solution using an array formula:

1. Create a name for B1:B12 called "Data"; create a name for A1:A12
called "Chosen"
2. Copy the following formula to the clipboard (but exclude the curly
brackets at beginning and end).

{=IF(ISERR(
SMALL(IF((Chosen="X") *
(MATCH(Data,Data,0)=ROW(INDIRECT("1:"&ROWS(Data)))),
MATCH(Data,Data,0),""),
ROW(INDIRECT("1:"&ROWS(Data))))),"",
INDEX(Data,
SMALL(IF((Chosen="X") *
(MATCH(Data,Data,0)=ROW(INDIRECT("1:"&ROWS(Data)))),
MATCH(Data,Data,0),""),
ROW(INDIRECT("1:"&ROWS(Data))))))}

3. Select cells C1:C12
4. Press F2 to edit formulas for cells C1:C12
5. Press Ctrl-V to paste in text of formula
6. Press Ctrl-Shift-Enter to save the array formula to cells C1:C12
7. The curly brackets will appear around the formula in the formula
bar, indicating that it is an array formula

The result will be a list of items in C1:C12 from B1:B12 where A1:A12
equals X, i.e., non-matching items are not listed. The items in C will
appear in the same order as they appeared in B1:B12. If there are any
blank cells in B1:B12, then all of C1:C12 will be #N/A. Don't have
duplicate items in B1:B12.

This will work for the ListFillRange of a Combo Box control -- but
there will always be 12 items listed. If 3 items were marked with X,
then those 3 will show in the drop-down, followed by 9 blank items.

Not a perfect solution, but hopefully it will work for you. A VBA
alternative would probably involve the Worksheet_Calculate event, in
order to detect the user typing in the Xs.

The formula is based on the "Returning a list of unique items in a
range" array formula from John Walkenbach's excellent book "Microsoft
Excel 2000 Formulas", plus the section on "Summing based on a
condition". There are editions for other Excel versions too. If you're
not familiar with array formulas, I highly recommend the book.
 
Top