Ranking - Not Sorting Properly.

I previously revived a formula (Thanks Max) to Sort and Rank my Stores by the 
Strongest Store to the Lowest Store. It did exactly what it was supposed to 
do with my sample layout I made with just Four stores and scores. But when I 
added all 14 stores to the list (each store as a rank like 2.45 or 3.56 or 
11.75) it will not sort them properly.

I am using 

=IF(ISNA(RANK(Q7,Q7:Q20,1)),"",INDEX(O7:O20,RANK(Q7,Q7:Q20,1)))

for the ranking system. It works when the stores Ranks are SINGLE digit 
numbers, but when they go into the tenths or hundredths the formula can't 
sort the Stores in the correct order.


Example (This is all on the same sheet)

___This is what we will see when we print the sheet out.___
___The RANK formula is inside the cells with the stores names___
___It is not ranking them properly___

B--------C--
RANK___FOOD COST
		
1_______Hwy 105
2_______Lumberton 96
3_______Palestine
4_______Lumberton 69
5_______Mauriceville
6_______Baytown
7_______M L K
8_______Walden
9_______Kountze
10______Hugo
11______M L K
12______Loop 256
13______Athens
14______Silsbee

-------------------------------------------

__The stores are permanent and are sorted alphabetically__
__The Total is added together from 12 other sheets representing JAN-DEC - I 
am using =SUMIF(JAN!D7:D20,O7,JAN!B7:B20) to grab each section of code__
__The Rank is divided by whatever Month we have currently finished. At the 
moment I have a cell off to the side that I will put 4 in to represent April 
and the Rank will divide the Total by 4 to give me an average rank for the 
store (=P7/R3)__

O-------------P------R
Food Cost____Total__Rank
		
Athens_______21___5.25
Baytown______30___7.50
Hugo_________44	___11.00
Hwy 105______25	___6.25
Kountze_______32___8.00
Loop 256______17___4.25
Lumberton 69___39___9.75
Lumberton 96___48___12.00
Mauriceville____23___5.75
M L K__________18___4.50
Mont Belvieu____39___9.75
Palestine	______24___6.00
Silsbee________13___3.25
Walden________47___11.75


-------------------------

What I have written down is exactly what my sheet is showing me. I am 
dumbfounded on how to correct the formula to show the stores in the order 
they are supposed to be in. It should look like this..

1_______Silsbee
2_______Loop 256
3_______M L K
4_______Athens
5_______Mauriceville
6_______Palestine
7_______Hwy 105
8_______Baytown
9_______Kountze
10______Lumberton 69 * 9.75
11______Mont Belvieu * 9.75
12______Hugo
13______Walden
14______Lumberton 96

Also.... I noticed when two stores have the same Rank.. Like Lumberton 69 
and Mont Belvieu with 9.75, it will show only one store in both the 10 and 11 
spot. Whichever store is first in the Alphabetic list is the one it puts on 
both spots.


Thanks for reading through the mess. Any suggestions would be very helpful.

0
Utf
5/22/2010 1:36:05 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

16 Replies
1092 Views

Similar Articles

[PageSpeed] 24

I don't understand how that formula was going to work, could you
provide a reference to the other thread?

Attached (to this post at thecodecage.com - there's usually a link
lower down this message) is a workbook as I guess your sheet is set up.
It results in the order you want. It doesn't highlight equal scores.
595


+-------------------------------------------------------------------+
|Filename: 2010-05-22_203101.png                                    |
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=595|
+-------------------------------------------------------------------+

-- 
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?u=558
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=204484

http://www.thecodecage.com/forumz

0
p45cal
5/22/2010 7:35:27 PM
On 22 Mai, 14:36, Matlock <Matl...@discussions.microsoft.com> wrote:
> I previously revived a formula (Thanks Max) to Sort and Rank my Stores by=
 the
> Strongest Store to the Lowest Store. It did exactly what it was supposed =
to
> do with my sample layout I made with just Four stores and scores. But whe=
n I
> added all 14 stores to the list (each store as a rank like 2.45 or 3.56 o=
r
> 11.75) it will not sort them properly.
>
> I am using
>
> =3DIF(ISNA(RANK(Q7,Q7:Q20,1)),"",INDEX(O7:O20,RANK(Q7,Q7:Q20,1)))
>
> for the ranking system. It works when the stores Ranks are SINGLE digit
> numbers, but when they go into the tenths or hundredths the formula can't
> sort the Stores in the correct order.
>
> Example (This is all on the same sheet)
>
> ___This is what we will see when we print the sheet out.___
> ___The RANK formula is inside the cells with the stores names___
> ___It is not ranking them properly___
>
> B--------C--
> RANK___FOOD COST
>
> 1_______Hwy 105
> 2_______Lumberton 96
> 3_______Palestine
> 4_______Lumberton 69
> 5_______Mauriceville
> 6_______Baytown
> 7_______M L K
> 8_______Walden
> 9_______Kountze
> 10______Hugo
> 11______M L K
> 12______Loop 256
> 13______Athens
> 14______Silsbee
>
> -------------------------------------------
>
> __The stores are permanent and are sorted alphabetically__
> __The Total is added together from 12 other sheets representing JAN-DEC -=
 I
> am using =3DSUMIF(JAN!D7:D20,O7,JAN!B7:B20) to grab each section of code_=
_
> __The Rank is divided by whatever Month we have currently finished. At th=
e
> moment I have a cell off to the side that I will put 4 in to represent Ap=
ril
> and the Rank will divide the Total by 4 to give me an average rank for th=
e
> store (=3DP7/R3)__
>
> O-------------P------R
> Food Cost____Total__Rank
>
> Athens_______21___5.25
> Baytown______30___7.50
> Hugo_________44 ___11.00
> Hwy 105______25 ___6.25
> Kountze_______32___8.00
> Loop 256______17___4.25
> Lumberton 69___39___9.75
> Lumberton 96___48___12.00
> Mauriceville____23___5.75
> M L K__________18___4.50
> Mont Belvieu____39___9.75
> Palestine =A0 =A0 =A0 ______24___6.00
> Silsbee________13___3.25
> Walden________47___11.75
>
> -------------------------
>
> What I have written down is exactly what my sheet is showing me. I am
> dumbfounded on how to correct the formula to show the stores in the order
> they are supposed to be in. It should look like this..
>
> 1_______Silsbee
> 2_______Loop 256
> 3_______M L K
> 4_______Athens
> 5_______Mauriceville
> 6_______Palestine
> 7_______Hwy 105
> 8_______Baytown
> 9_______Kountze
> 10______Lumberton 69 * 9.75
> 11______Mont Belvieu * 9.75
> 12______Hugo
> 13______Walden
> 14______Lumberton 96
>
> Also.... I noticed when two stores have the same Rank.. Like Lumberton 69
> and Mont Belvieu with 9.75, it will show only one store in both the 10 an=
d 11
> spot. Whichever store is first in the Alphabetic list is the one it puts =
on
> both spots.
>
> Thanks for reading through the mess. Any suggestions would be very helpfu=
l.

Hello,

You can sort with worksheet functions with:
http://sulprobil.com/html/sorting.html

Regards,
Bernd
0
Bernd
5/22/2010 8:16:28 PM
The problem you face is because of tied scores. The earlier, simple 
expression doesn't handle ties, an event which I had thought/presumed would 
not happen with your depiction of sample scores in D2 down. 

Here's a simple way to handle it with tiebreakers
You have your scores in D2 down, store names in C2 down
Put in E2: =IF(D2="","",D2+ROW()/10^10)
Copy down to cover the max expected extent of data in col D, say down to 
D200. Minimize/hide col E. This is the tiebreaker col.

Then drop this in B2:
=IF(ROWS($1:1)>COUNT(E:E),"",INDEX(C:C,MATCH(SMALL(E:E,ROWS($1:1)),E:E,0)))
Copy down to B200. Col B will now return the full ascending list of store 
names, sorted by the scores in col D. Stores with tied scores, if any, will 
appear in the same relative order that they appear within the source data.
-- 
Max
Singapore
--- 
"Matlock" wrote:
> I previously revived a formula (Thanks Max) to Sort and Rank my Stores by the 
> Strongest Store to the Lowest Store. It did exactly what it was supposed to 
> do with my sample layout I made with just Four stores and scores. But when I 
> added all 14 stores to the list (each store as a rank like 2.45 or 3.56 or 
> 11.75) it will not sort them properly.
> 
> I am using 
> 
> =IF(ISNA(RANK(Q7,Q7:Q20,1)),"",INDEX(O7:O20,RANK(Q7,Q7:Q20,1)))
> 
> for the ranking system. It works when the stores Ranks are SINGLE digit 
> numbers, but when they go into the tenths or hundredths the formula can't 
> sort the Stores in the correct order.
> 
> 
> Example (This is all on the same sheet)
> 
> ___This is what we will see when we print the sheet out.___
> ___The RANK formula is inside the cells with the stores names___
> ___It is not ranking them properly___
> 
> B--------C--
> RANK___FOOD COST
> 		
> 1_______Hwy 105
> 2_______Lumberton 96
> 3_______Palestine
> 4_______Lumberton 69
> 5_______Mauriceville
> 6_______Baytown
> 7_______M L K
> 8_______Walden
> 9_______Kountze
> 10______Hugo
> 11______M L K
> 12______Loop 256
> 13______Athens
> 14______Silsbee
> 
> -------------------------------------------
> 
> __The stores are permanent and are sorted alphabetically__
> __The Total is added together from 12 other sheets representing JAN-DEC - I 
> am using =SUMIF(JAN!D7:D20,O7,JAN!B7:B20) to grab each section of code__
> __The Rank is divided by whatever Month we have currently finished. At the 
> moment I have a cell off to the side that I will put 4 in to represent April 
> and the Rank will divide the Total by 4 to give me an average rank for the 
> store (=P7/R3)__
> 
> O-------------P------R
> Food Cost____Total__Rank
> 		
> Athens_______21___5.25
> Baytown______30___7.50
> Hugo_________44	___11.00
> Hwy 105______25	___6.25
> Kountze_______32___8.00
> Loop 256______17___4.25
> Lumberton 69___39___9.75
> Lumberton 96___48___12.00
> Mauriceville____23___5.75
> M L K__________18___4.50
> Mont Belvieu____39___9.75
> Palestine	______24___6.00
> Silsbee________13___3.25
> Walden________47___11.75
> 
> 
> -------------------------
> 
> What I have written down is exactly what my sheet is showing me. I am 
> dumbfounded on how to correct the formula to show the stores in the order 
> they are supposed to be in. It should look like this..
> 
> 1_______Silsbee
> 2_______Loop 256
> 3_______M L K
> 4_______Athens
> 5_______Mauriceville
> 6_______Palestine
> 7_______Hwy 105
> 8_______Baytown
> 9_______Kountze
> 10______Lumberton 69 * 9.75
> 11______Mont Belvieu * 9.75
> 12______Hugo
> 13______Walden
> 14______Lumberton 96
> 
> Also.... I noticed when two stores have the same Rank.. Like Lumberton 69 
> and Mont Belvieu with 9.75, it will show only one store in both the 10 and 11 
> spot. Whichever store is first in the Alphabetic list is the one it puts on 
> both spots.
> 
> 
> Thanks for reading through the mess. Any suggestions would be very helpful.
> 
0
Utf
5/22/2010 9:59:01 PM
Max, 

I'm getting there. Slowly. lol

Alright, I've added 

=IF(Q7="","",Q7+ROW()/10^10)

to the sheet. as well as 

=IF(ROWS($1:1)>COUNT(E:E),"",INDEX(C:C,MATCH(SMALL(E:E,ROWS($1:1)),E:E,0)))

I am not sure if I am inserting the cell codes into the correct spots.

D7:D20 has the cells where I will be inserting the code (=IF(rows....)
O7:O20 Has the actual name of the stores in ABC order.
Q7:Q20 Has the actual ranks after the division. 
R7:R20 Has the cells where I will be inserting the code (=IF(Q7="","",....)

Do I need to keep the previous code

=IF(ISNA(RANK(Q7,Q7:Q20,1)),"",INDEX(O7:O20,RANK(Q7,Q7:Q20,1)))

or discard it?

On the new code (=IF(ROWS($1:1)>...) I am not sure which cells go where.
0
Utf
5/22/2010 11:51:01 PM
Discard the previous formula (the one using RANK)
Now here, you have the criteria formula in R7:R20
In D7:
=IF(ROWS($1:1)>COUNT($R$7:$R$20),"",INDEX($O$7:$O$20,MATCH(SMALL($R$7:$R$20,ROWS($1:1)),$R$7:$R$20,0)))
Copy down to D20 to return the auto full ascending sort
-- 
Max
Singapore
--- 
0
Utf
5/23/2010 2:16:01 AM
Added it down from D7:D20. It is listing Silsbee as 1st - which is correct. 
But Silsbee is listed all the way down to D20.

Where should be $1:1s be corrisponding to? in the formula?

=IF(ROWS($1:1)>COUNT($R$7:$R$20),"",INDEX($O$7:$O$20,MATCH(SMALL($R$7:$R$20,ROWS($1:1)),$R$7:$R$20,0)))

0
Utf
5/23/2010 2:51:01 AM
I'm not sure what is still tripping you up over there. It should have worked 
just as well for you. Check again that you have done this ..
In R2: =IF(D7="","",D7+ROW()/10^10)
R2 is copied down to R20

In D2:
=IF(ROWS($1:1)>COUNT($R$7:$R$20),"",INDEX($O$7:$O$20,MATCH(SMALL($R$7:$R$20,ROWS($1:1)),$R$7:$R$20,0)))
D2 is copied down to D20

Ensure that calc mode is set to auto. ROWS($1:1) is just an incrementer 
which returns the series: 1, 2, 3, .. when you copy it down from the start 
cell.
-- 
Max
Singapore
--- 
"Matlock" wrote:
> Added it down from D7:D20. It is listing Silsbee as 1st - which is correct. 
> But Silsbee is listed all the way down to D20.
> 
> Where should be $1:1s be corrisponding to? in the formula?
> 
> =IF(ROWS($1:1)>COUNT($R$7:$R$20),"",INDEX($O$7:$O$20,MATCH(SMALL($R$7:$R$20,ROWS($1:1)),$R$7:$R$20,0)))
> 
0
Utf
5/23/2010 6:46:01 AM
Sorry, this part in the earlier should read as (following your set-up):
In R2: =IF(Q7="","",Q7+ROW()/10^10)
R2 is copied down to R20
-- 
Max
Singapore
--- 
0
Utf
5/23/2010 7:02:01 AM
I am not sure where I messed the formula at or what I did wrong, but your 
Formulas worked flawlessly. Thank you very much, Max. I am definitely putting 
you on the source information for the excel book for thanks. 

I'll be back when I need help with other formulas!

Thanks!
0
Utf
5/24/2010 2:33:04 AM
Thank you very much, Max. The formulas are working perfecly now. Exactly what 
I needed. Thanks, again!
0
Utf
5/24/2010 2:36:04 AM
Oops, just realized ... R2 should read as R7. my eyes are no longer as sharp 
as the mind still is, humble apologies

In R7: =IF(Q7="","",Q7+ROW()/10^10)
R7 is copied down to R20

The above takes the scores in Q7:Q20, which could contain ties, or even 
multiple ties, and for any tied scores, it'll create fractionally different 
scores to differentiate these. 

It does this through the use of this part:  Q7+ROW()/10^10
where the part:  +ROW()/10^10 
adds a very small number which will be different small number for each row 
as you copy down by virtue of the ROW()

These different scores created in R7:R20 are then used as the base to 
extract whatever is required (eg the names in O7:O20) via the 
index(colO,match(small(colR,incrementer),colR,0)) expression 
-- 
Max
Singapore
--- 

0
Utf
5/24/2010 2:42:54 AM
Glad to hear that you finally got it going there ...
(do hit the YES button once more for the road!) 

> I'll be back when I need help with other formulas
Ahh, but I'd suggest that you post in this MS forum henceforth:
http://answers.microsoft.com/en-us/office/default.aspx#tab=4

And if you have vba queries, you can post in this MS vba forum:
http://social.msdn.microsoft.com/Forums/en-US/isvvba/threads
  
MS has posted a msg that all xl newsgroups (like this one) will be closed in 
end May 2010. The above forums are amongst those directed by MS for ng 
posters to go to, and is MS' way forward for queries wef Jun 2010. All the 
best to you and farewell ..

-- 
Max
Singapore
--- 
0
Utf
5/24/2010 6:20:01 AM
Doctor, Doctor, I've noticed that people seem to be ignoring me...

Next!


-- 
p45cal


"p45cal" wrote:

> 
> I don't understand how that formula was going to work, could you
> provide a reference to the other thread?
> 
> Attached (to this post at thecodecage.com - there's usually a link
> lower down this message) is a workbook as I guess your sheet is set up.
> It results in the order you want. It doesn't highlight equal scores.
> 595
> 
> 
> +-------------------------------------------------------------------+
> |Filename: 2010-05-22_203101.png                                    |
> |Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=595|
> +-------------------------------------------------------------------+
> 
> -- 
> p45cal
> 
> *p45cal*
> ------------------------------------------------------------------------
> p45cal's Profile: http://www.thecodecage.com/forumz/member.php?u=558
> View this thread: http://www.thecodecage.com/forumz/showthread.php?t=204484
> 
> http://www.thecodecage.com/forumz
> 
> .
> 
0
Utf
5/24/2010 7:29:01 AM
"p45cal" wrote:
> Doctor, Doctor, I've noticed that people seem to be ignoring me...

No, not really .. as regards your Q to the OP
> I don't understand how that formula was going to work, 
> could you provide a reference to the other thread?

Here is a link to the OP's earlier thread, as requested:
http://tinyurl.com/27qcpt3

and here is my 1st reply to the OP's latest issue (in the other branch):

The problem you face is because of tied scores. The earlier, simple 
expression doesn't handle ties, an event which I had thought/presumed would 
not happen with your depiction of sample scores in D2 down. 

Here's a simple way to handle it with tiebreakers
You have your scores in D2 down, store names in C2 down
Put in E2: =IF(D2="","",D2+ROW()/10^10)
Copy down to cover the max expected extent of data in col D, say down to 
D200. Minimize/hide col E. This is the tiebreaker col.

Then drop this in B2:
=IF(ROWS($1:1)>COUNT(E:E),"",INDEX(C:C,MATCH(SMALL(E:E,ROWS($1:1)),E:E,0)))
Copy down to B200. Col B will now return the full ascending list of store 
names, sorted by the scores in col D. Stores with tied scores, if any, will 
appear in the same relative order that they appear within the source data.

-- 
Max
Singapore
---
0
Utf
5/24/2010 8:07:01 AM
yes, but I supplied a solution and it wasn't even looked at - still
hasn't been.



Max;730168 Wrote: 
> 
"p45cal" wrote:
> > Doctor, Doctor, I've noticed that people seem to be ignoring me...
> 
> No, not really .. as regards your Q to the OP
> > I don't understand how that formula was going to work,
> > could you provide a reference to the other thread?
> 
> Here is a link to the OP's earlier thread, as requested:
> http://tinyurl.com/27qcpt3
> 
> and here is my 1st reply to the OP's latest issue (in the other
branch):
> 
> The problem you face is because of tied scores. The earlier, simple
> expression doesn't handle ties, an event which I had thought/presumed
would
> not happen with your depiction of sample scores in D2 down.
> 
> Here's a simple way to handle it with tiebreakers
> You have your scores in D2 down, store names in C2 down
> Put in E2: =IF(D2="","",D2+ROW()/10^10)
> Copy down to cover the max expected extent of data in col D, say down
to
> D200. Minimize/hide col E. This is the tiebreaker col.
> 
> Then drop this in B2:
> =IF(ROWS($1:1)>COUNT(E:E),"",INDEX(C:C,MATCH(SMALL(E:E,ROWS($1:1)),E:E,0)))
> Copy down to B200. Col B will now return the full ascending list of
store
> names, sorted by the scores in col D. Stores with tied scores, if any,
will
> appear in the same relative order that they appear within the source
data.
> 
> --
> Max
> Singapore
> ---


-- 
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?u=558
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=204484

http://www.thecodecage.com/forumz

0
p45cal
5/24/2010 8:25:57 AM
> yes, but I supplied a solution and it wasn't even looked at - still
> hasn't been

I'm afraid that has to be answered by the OP. Don't be discouraged however, 
this sort of thing happens all the time in forums. Given the fortune of 
receiving a plethora of responses to their queries, some OPs do check-out 
each response's value and even reply to each individual responder while 
others well, do not practice it to this level of diligence. And some OPs 
don't even bother replying at all. 
0
Utf
5/24/2010 9:56:01 AM
Reply:

Similar Artilces:

sorting columns with more than 2 text entries
Data>Text to Columns>Next works well if all entries have a "single" first and last name, eg, "Joe Smith". However, if there is a "Mary Joe Smith" listed, how do I put only the last text entry into a new column? I am downloading payment information from a PayPal account and want to sort by last name for our lunches. Unfortunatley the "name" information (first, last, etc...) is contained in a single cell. -- JAB Hi JAB! Use this formula to extract the 1st name in cell A1: =LEFT(A1,FIND(" ",A1,1)) Use this formula to extract the l...

List a ranking
I'm a newbie at this, so help is appreciated. I have an Excel sheet like this: Team A .600 =rank(b1,b1:b3) Team B .750 =rank(b2,b1:b3) Team C .300 =rank(b3,b1:b3) I want to get another cell to list how far they are from the #1 rank. So, cell d1 should be equal to .150 and d3 equal to .450. The percentages will change frequently, so it needs to figure out what is #1. I know I could just do =b2-b1 and b2-b3, but only because I can see the b2 is the highest percentage. Am I making sense? Is there a way to do this? TIA. BR =MAX($B$1:$B$3)-B1 etc. -- HTH RP ...

Rank within Categories
I am trying to work out a way to rank within categories. Column E has different categories. Column i has the data. Column am working on i want to give the ranking where each data point falls within its own category. i have tried to hash something out but am stuck...here is my feeble attempt =IF($E5="SE",IF($E$5:$E$40="SE",IF(I5>0,RANK(I5,$I$5:$I$40,0),"N/ A"), ...... ..... signfies a string of IFs. the Next one being IF($E5="C".... I have also tried an array.....that doesnt seem to work =IF(OR(E6={"C","SE","NE","...

How to sort time data?
I have hours worth of second by second data and need to sort it, but I only need to see every 10 seconds. Is there an easier way than just either deleting hundreds of rows or selecting them (copy and paste)? I am sure you could also write a macro, but I am too familiar with those either. Thank you, I don't fully understand what you mean about "every 10 seconds", but here is the general truth about times: Times are numbers. If you can do something with a number, you can do it with time, even if it doesn't make sense: AVERAGE, STANDARD DEVIATION, MIN, MAX, ROUND, DIVID...

Sort by date [XSLT]
Is there a way to use <xsl:sort> to sort by date. Sample xml doc I want to perform sorting on --------------------------------------------- <Parent> <child> <date>10/10/2003</date> <displayText>The text that should be displayed</displayText> </child> <child> <date>9/10/2001</date> <displayText>The text that should be displayed</displayText> </child> <child> <date>1/10/2002</date> <displayText>The text that should be dis...

PIvot Table Sorting Differently in Excel 2003 than Earlier Versions
I am wondering if anyone else is having a similar problem or concern and can provide some documentation or insight into this problem. The problem occurs in Excel 2003 as the program seems to act differently under the same conditions than it does in earlier versions of Excel (97, 2000 and 2002). The problem is best illustrated by using two pivot tables, one based on the other, as in my example below. I have a pivot table with three items inserted into the row section of the layout and one item in the data section which is the subtotal of the line item. Let's say the three items are the...

Sorting the register
I have been a Quicken user for about ten years, and have decided to give Money a whirl. One of the Quicken features that I really like the was the ability to sort the register based on that cleared status of any transaction. This view sorted the transactions first by reconciled transactions, followed by cleared transactions, followed by un-cleared transactions. This made it very simple to balance my checkbook to the banks online balance. Is there a way to do this in money 2004? Does anyone out there that has used both Quicken and Money have any comments about one product as it comp...

Adding the AND properly
I have a code that I had some help putting together. I would like to modify it to allow me to search 2 fields and not just the one its searches now. Brief overview is that it currently searches for a WORD in a field called [Definition]. I would also like it to look in a field called [Cat Codes] if a number code (like 99200) is entered. Code being used now: Private Sub Text49_AfterUpdate() Me.[Cat Codes subform].Form.Filter = "Definition Like ""*" & Me.Text49 & "*""" Me.[Cat Codes subform].Form.FilterOn = True End Sub -- Work is sometim...

rank
I would like to know how to get around duplicate issues when using th rank functio -- Message posted from http://www.ExcelForum.com Hi see: http://www.cpearson.com/excel/rank.htm and http://www.xldynamic.com/source/xld.RANK.html -- Regards Frank Kabel Frankfurt, Germany > I would like to know how to get around duplicate issues when using the > rank function > > > --- > Message posted from http://www.ExcelForum.com/ ...

Sort Expression
I have the following expression in a query. I would like to sort the account # ascending however when I choose ascending in the grid it groups all the 1's , then the 2,' and so on. I would like it to show in numerical order. Any ideas. Expr1: [Account#] & ", " & [LastName] & " , " & [FirstName] & ", " & [City] Also at the moment the customer is found by their account#. Is there a way to utilize this to search by partial name or would I need to set up a seperate query. TIA On Tue, 17 Jul 2007 08:58:22 -0700, myxmaster@hotmai...

grouping cells to sort by only one column
Hi, New to this so please bear with me. I have a spread sheet where I am listing names in column A and othe info relating to that name in columns B and C in the two rows below th name. I then start a different name in the next row down with the info in t two rows below that, and so forth. I want to be able to add more names in the future and be able to sor all of this by column A only. The problem I am having is the info i the other columns moves. I need it to somehow attatch and stay put wit it's corisponding name without moving. In other words how do I make everything from A1 to ...

Sort list
I have a table which I need to sort, e.g.: 2000 2039B 2026 2167 2239 2242 When I run Sort, I get 2000 2026 2167 2239 2242 2039B Why does 2039B come at the bottom rather than between 2026 and 2167? "janey" <janey@newsgroup.microsoft.com> wrote in message news:8BCDF8EE-1A64-40B4-8BB9-4924287784C2@microsoft.com... >I have a table which I need to sort, e.g.: > > 2000 > 2039B > 2026 > 2167 > 2239 > 2242 > > When I run Sort, I get > 2000 > 2026 > 2167 > 2239 > 2242 > 2039B > > Why does 2039B come at the bo...

Clarify Sorting
I've recently begun to Copy and paste-special values among other things numbers (integers); such numbers are best treated as text << so I format as Text afterwards; nonetheless when I sort on the field it isn't clean - frankly, its just not sorting... Are there guaranteed steps to reach the data sort stage "in good shape"? Tks in Advance... Formatting a cell as Text after it contains a numeric entry isn't enough. You could reenter each of them manually (yechhhh!) or you could use a helper column filled with formulas: =a1&"" =text(a1,"000000...

Sort as "reference column"
Column A has a random sorted list of text values, column D also consists of same values but not in the way sorted as column A. What I want is to sort Column D just like Column A but with the adjacent columns to the right (Column E,F also) should sort together with column D. -- kingjeremy ------------------------------------------------------------------------ kingjeremy's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26444 View this thread: http://www.excelforum.com/showthread.php?threadid=474882 Hi One way would be to use helper columns. Start by adding a ...

subtotal rank
I'm working with a filtered list and I'm looking for a rank formula that works like the subtotal formula does. So when I filter the list it ranks by the particular filter. Thanks in advance BW --- Message posted from http://www.ExcelForum.com/ ...

Ranking in Excel
Hi All I need to rank numbers entered in column A in ascending order while recognising ties but not skipping ranking numbers. The numbers can range from 1 to 1,000 but there are only 40 rows of data in column A. Any help would be much appreciated. Kind Regards Celticshadow To do this with worksheet functions, you need to sort your list in your desired (Ascending) order. If your numbers start in A2, then in B2, enter a 1, and in B3, enter the formula =IF(A3=A2,B2,B2+1) and copy down. IF your numbers cannot be sorted, then you would either need another helper column or VBA. -- HTH, ...

Rank
I would like to know how to get around duplicates when using ran -- Message posted from http://www.ExcelForum.com Hi see your other posts please don't multipost -- Regards Frank Kabel Frankfurt, Germany > I would like to know how to get around duplicates when using rank > > > --- > Message posted from http://www.ExcelForum.com/ ...

Distribution list
Is it possible to sort the names in a distribution list by last name? Thanks, John Pinback John Pinback <pinback1752@yahoo.com> wrote: > Is it possible to sort the names in a distribution list by last name? The DL "Name" field reflects the "Display As" field of the added contacts. If you specify the Display As fields the way you want (i.e., "Last, First"), then the Name field in the DL will be alphabetized by last name. You can resort either ascending or descending by clicking the Name column header. When you are in the process of choosing the...

Sorting by colors
I have a matrix of data in excel where they have highlighted lines with colors. Is there any to sort this database by colors ? There are some folks doing such a consuming time job !! Visit www.cpearson.com, Chip has lots of stuff on colour. -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Hecwill" <Hecwill@discussions.microsoft.com> wrote in message news:605D660F-4B1D-4150-81BA-0460778DECBC@microsoft.com... >I have a matrix of data in excel where they have highlighted lines with > colors. Is there any to sort this database by colors ? There a...

Row_Number() Sorting
This is a multi-part message in MIME format. ------=_NextPart_000_001E_01CACCD3.B2FCD400 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I am a little confused as to what Row_Number is doing based on the = execution plan. If I have a table: CREATE TABLE T (PK INT IDENTITY, A INT, B INT, C INT, D INT) CREATE UNIQUE CLUSTERED INDEX TPK ON T(PK) INSERT T VALUES (0, 1, 8, 3) INSERT T VALUES (0, 3, 6, 5) INSERT T VALUES (0, 5, 4, 0) INSERT T VALUES (0, 7, 2, 8) INSERT T VALUES (0, 9, 0, 2) INSERT T VALUES (1, 0, 9, 9) I...

Currency not working properly
Hello; I am living in Canada and when I create a invoice it changes the amount. For instance, I have created a product for $12.00. When I put it in the invoice it comes up as $15.28, the U.S. equivelent. I have told MS Money to use Canadian dollars as the base. I have also gone into my computer and made sure in the regional settings it was set to Canada, Canadian English, etc. Running Windows XP Pro Service Pack 2 and MS Money 2005. Any help would be greatly appreciated. Shann ...

Sort on a calculated field
When I run this query, I am presented with a parameter input box asking for tblGrievances.Year (unless I remove it from the ORDER BY clause). How can I sort on the calculated field and the GrievNumber field? Many thanks, Rip SELECT tblGrievances.*, DatePart("yyyy", tblGrievances.Yr) AS Year FROM tblGrievances ORDER BY tblGrievances.Year, tblGrievances.GrievNumber SELECT tblGrievances.* , Year(tblGrievances.Yr) AS [Year] FROM tblGrievances ORDER BY Year(tblGrievances.Yr), tblGrievances.GrievNumber Year is a bad name for a column. Year is a function that ret...

Second Level Sort
Office Excel 2007 Second level sort not rendering I have a name address etc wksht where the street number is one column and the street name is another column I set the first sort on street name and the second level sort to street # . The street name sorts fine but the street numbers don't order correctly so that instead of getting 123 xyz st then 125 xyz st and 345 xyz st, the numerical part of the address is not sequential. Run two separate sorts: first sort the numbers then sort the names. -- Jim Cone Portland, Oregon USA http://www.contextures.com/excel-sort-addin.html .. .. ...

Error Message While Sorting
When I try to sort I am getting the following error message. This operation requires the merged cells to be identically sized. Any suggestions -- arku ----------------------------------------------------------------------- arkum's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1648 View this thread: http://www.excelforum.com/showthread.php?threadid=31493 Hi arkum Remove the merged cells and never use merged cells again. Always trouble when you use this. Select the cells and use Ctrl-1 to go to the Alignment tab to change this -- Regards Ron de Bruin http...

Inserting cells then ranking
Hi, I am trying to create a ‘to-do job’ spreadsheet that will automatically insert new ‘tasks’ according to their priority. For example, on the sheet I currently have 10 tasks - entered manually. However, the more tasks I have, the longer I spend re-assigning rank order numbers. I would like the sheet to: • insert a new task e.g., Pick up TV. let’s say, into position number 5. This would result in “Sweep Drive” moving down to number 6 and all the other jobs would subsequently be moved down one cell. • Allow me to keep adding jobs if necessary. I think I may need an ‘Update ...