Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
putting data in order(?)
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 -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
List of marked items (originally "putting data in order(?)")
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. Rutgers_Excels wrote in message ... 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 the 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 in column C? For instance, if cells A3 and A7 had checks in them, the 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
putting a column of names into alphabetical order | Excel Discussion (Misc queries) | |||
Putting subtotals in $ descending order | Excel Worksheet Functions | |||
How do I undo putting a column in alphabetical order? | Excel Worksheet Functions | |||
Putting columns back in alpha order | Excel Worksheet Functions | |||
Putting pages in alpha order? | Excel Worksheet Functions |