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





PostPosted: 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





PostPosted: 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





PostPosted: 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