Single formula to 'sort' a list onto a second sheet?
Author Message
Kulmalla

 Posted: Thu May 18 16:03:12 CDT 2006 Top
 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 Excel353

Domenic

 Posted: Thu May 18 16:03:12 CDT 2006 Top
 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\$10,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! > 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

KR

 Posted: Fri May 19 08:08:39 CDT 2006 Top
 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 > 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\$10,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! > > > > 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