Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Single formula to 'sort' a list onto a second sheet?
I have a sheet1 where users will enter an unknown number of items in column
A, and a rating from 1-10 /or/ the letter "M" in column B. On sheet2, I want to (re)show those items and their ratings in the following order, based on their rating: all 'M' first, then in descending order, and have this list automatically update/recalculate when any item is added or changed on the Sheet1. Item X M Item C M Item K M Item J 10 Item W 10 Item A 9 Item F 8 Item Y 8 Item N 8 etc. I know how to do this in VBA, but I'm trying to complete this workbook without any macros because some users have their macro security set on high by default and I'd like this to work for everyone without asking them to change their security settings. I'm sure there is a way to do this with just formulas (lookups, match, etc.), but I'm struggling trying to find a combination that will work, since each row shouldn't repeat data that is already showing above, and should find the next highest item to bring over. Helpful hints please? Thanks, Keith |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Single formula to 'sort' a list onto a second sheet?
Assuming that A2:B10 contains the data, try the following formula, which
needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER... D2, copied down and over to Column E: =IF(ROWS(D$2:D2)<=COUNTA($B$2:$B$10),INDEX(A$2:A$1 0,MATCH(LARGE(IF($B$2:$ B$10="M",100,$B$2:$B$10)-ROW($B$2:$B$10)/10^5,ROWS(D$2:D2)),IF($B$2:$B$10 ="M",100,$B$2:$B$10)-ROW($B$2:$B$10)/10^5,0)),"") Since the range for your data will be changing, you can either... 1. convert the data into a list (Data List Create List), if you have Excel 2003 or later, or 2. use dynamic named ranges Hope this helps! In article , "KR" wrote: I have a sheet1 where users will enter an unknown number of items in column A, and a rating from 1-10 /or/ the letter "M" in column B. On sheet2, I want to (re)show those items and their ratings in the following order, based on their rating: all 'M' first, then in descending order, and have this list automatically update/recalculate when any item is added or changed on the Sheet1. Item X M Item C M Item K M Item J 10 Item W 10 Item A 9 Item F 8 Item Y 8 Item N 8 etc. I know how to do this in VBA, but I'm trying to complete this workbook without any macros because some users have their macro security set on high by default and I'd like this to work for everyone without asking them to change their security settings. I'm sure there is a way to do this with just formulas (lookups, match, etc.), but I'm struggling trying to find a combination that will work, since each row shouldn't repeat data that is already showing above, and should find the next highest item to bring over. Helpful hints please? Thanks, Keith |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Single formula to 'sort' a list onto a second sheet?
Wow, I never would have figured that out- I'm not sure I understand it even
now! I'll give it a try, and also tease it apart to try to understand how it works. Many thanks!! Keith "Domenic" wrote in message ... Assuming that A2:B10 contains the data, try the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER... D2, copied down and over to Column E: =IF(ROWS(D$2:D2)<=COUNTA($B$2:$B$10),INDEX(A$2:A$1 0,MATCH(LARGE(IF($B$2:$ B$10="M",100,$B$2:$B$10)-ROW($B$2:$B$10)/10^5,ROWS(D$2:D2)),IF($B$2:$B$10 ="M",100,$B$2:$B$10)-ROW($B$2:$B$10)/10^5,0)),"") Since the range for your data will be changing, you can either... 1. convert the data into a list (Data List Create List), if you have Excel 2003 or later, or 2. use dynamic named ranges Hope this helps! In article , "KR" wrote: I have a sheet1 where users will enter an unknown number of items in column A, and a rating from 1-10 /or/ the letter "M" in column B. On sheet2, I want to (re)show those items and their ratings in the following order, based on their rating: all 'M' first, then in descending order, and have this list automatically update/recalculate when any item is added or changed on the Sheet1. Item X M Item C M Item K M Item J 10 Item W 10 Item A 9 Item F 8 Item Y 8 Item N 8 etc. I know how to do this in VBA, but I'm trying to complete this workbook without any macros because some users have their macro security set on high by default and I'd like this to work for everyone without asking them to change their security settings. I'm sure there is a way to do this with just formulas (lookups, match, etc.), but I'm struggling trying to find a combination that will work, since each row shouldn't repeat data that is already showing above, and should find the next highest item to bring over. Helpful hints please? Thanks, Keith |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding and compiling list of cells containing data... | Excel Worksheet Functions | |||
Build a single order sheet from a multiple page material list. | Excel Discussion (Misc queries) | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
multiple select from the drop down list in excel. list in one sheet and drop down in | Excel Discussion (Misc queries) | |||
Formula checking multiple worksheets | Excel Worksheet Functions |