Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel Formula that mimics AZ auto sort 3

Status
Not open for further replies.
NickSleeman

NickSleeman

Technical User
Sep 5, 2003
7
GB
Hello,

can someone help please. I have some reports to run which include graphs. These graphs need to show the top 5 categories. I have a list of categories and their associated values, but I would like the graph to refresh the top 5 without me having to press the AZ sort button on the table (I can't use a macro). E.g.:

Category Value

Car 6
Apple 7
Door 11
Cat 3
Pen 7

I can get the values to auto sort by using the LARGE formula. However, I am at a loss how to change the order of the categories with their associated values. All I get is the categories ranked alphabetically, not linked to the values.

Thanks,


Nick
 
Sort by date Sort by votes
Hi,

2 new columns
cat val

Cal formula
Code: 
=IF(F2=F1,INDEX(A3:$A$6,MATCH(F2,$B$4:$B$6,0),1),INDEX(Category,MATCH(F2,Value,0),1))

Val formula
Code: 
=LARGE(Value,ROW()-1)
assuming that
Category is in COL A with data beginning in A2
Value is in COL B with data beginning in B2
Cat is in COL E
Val is in COL F

:)

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884
 
Upvote 0 Downvote
Thanks Skip. However, I am finding that when I have duplicate values (such as apple and pen in the above example) I only get one category shown twice. Is there anyway to overcome this?

Thanks again,

Nick
 
Upvote 0 Downvote
sorry, I copied the wrong formula
Code: 
=IF(F2=F1,INDEX(A2:$A$6,MATCH(F2,B2:$B$6,0),1),INDEX(Category,MATCH(F2,Value,0),1))


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884
 
Upvote 0 Downvote
Thanks again, however, even with the correct range I am getting duplicates in column E if the values in column F are the same. I have been messing around with an offset formula as well as index/match formulas.

=OFFSET(LARGE(value,Row()-1),0,-1)

for column E, but I am not quite there, as you can see. Any ideas with this or the index/match formula?

Cheers,

Nick
 
Upvote 0 Downvote
Here is a setup that you can experiment with. It's a bit of a kludge, but I think it does what you want:
[blue]
Code: 
A1: 'Category
B1: 'Value
C1: 'Unique Value
D1: 'Rank
E1: 'Top Categories
F1: 'Top Values
G1: 'Top Categories (Encoded)
H1: 'Sorted Codes
I1: 'Top Categories (Sorted)
J1: 'Corresponding Values
A2: 'Car
A3: 'Dog
A4: 'Door
A5: 'Cat
A6: 'Pen
A7: 'Apple
A8: 'Dog
A9: 'Mouse
B2: 6
B3: 2
B4: 11
B5: 3
B6: 7
B7: 7
B8: 1
B9: 2
D2: 1
D3: 2
D4: 3
D5: 4
D6: 5
C2: =B2+ROW(B2)/100000
  (copy C2 into C3:C9)
E2: =INDEX($A$2:$A$9,MATCH(F2,$C$2:$C$9,0),1)
F2: =LARGE($C$2:$C$9,D2)
G2: =1000000*(100+CODE(E2))+1000*CODE(MID(E2,2,1))+CODE(MID(E2,3,1))
H2: =SMALL($G$2:$G$6,D2)
I2: =INDEX($E$2:$E$6,MATCH(H2,$G$2:$G$6,0),1)
J2: =ROUND(VLOOKUP(I2,$E$2:$F$6,2,0),0)
  (copy E2:J2 into E3:E6)
[/color]
If you set it up as indicated, you should be able to change numbers in column B and immediately see the new top 5 sorted by category in columns I and J.

Note that if the real categories are not unique within the first three characters, you will have to extend the formula in column G.

Note also that if your values are not integers, you will have to tweak the rounding formulas and maybe increase the divisor in column C (used to make values unique).


 
Upvote 0 Downvote

Skip, I was going to suggest that it might be too cold and you had temporary brain freeze (like last summer when it was too hot), but I just looked at the weather map and it looks like you will have to find another excuse! [LOL]

 
Upvote 0 Downvote
geezzzz,

yer right -- cain't use thet wun!

How 'bout,

"The dog ate my brain!"

or

"When I opened the door, there she was in the altogether and I plum forgot whatever it was................."

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884
 
Upvote 0 Downvote
leveraging on Zathras' excellent solution...
Code: 
A1: Category     - named range
B1: Value        - named range
C1: UniqueValue  - named range
D1: OrderedUniqueValue
E1: RankedCategory
F1: RankedValue

C2: =$B2+(COUNTA(Category)-ROW())/10000
D2: =LARGE(UniqueValue,ROW()-1)
E2: =INDEX(Category,MATCH($D2,UniqueValue,0),1)
F2: =INDEX(Value,MATCH($D2,UniqueValue,0),1)
I used =$B2+(COUNTA(Category)-ROW())/10000 for UniqueValue to order in descending sequence.

...

and attempting to reclaim my brain ;-)

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884
 
Upvote 0 Downvote
I must be missing something. Those formulas gave me this:
Code: 
  RankedCategory  RankedValue
Code: 
    Door              11 
    Pen               7 
    Apple             7 
    Car               6 
    Cat               3
Whereas I think the correct display should be
Code: 
  Top Categories Corresponding
   (Sorted)         Values
Code: 
    Apple              7
    Car                6
    Cat                3
    Door               11
    Pen                7
I think the original spec was for the categories to be sorted (not the values)?

Also, by using ROW()-1 as the argument for the LARGE function, there is an unstated assumption that the first data row will be row 2. If the user inserts some additional row(s) at the top (e.g. for a title or some instructions), the formulas will break. -- Not the best practice.

 
Upvote 0 Downvote
If the intent is category ranked alphabetically, I missread the requirement. :cool:

I Name the ranges using a Worksheet_Change that fires a macro
Code: 
    Application.DisplayAlerts = False
    [A1].CurrentRegion.CreateNames _
        Top:=True, Left:=False, Bottom:=False, Right:=False
    Application.DisplayAlerts = True


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884
 
Upvote 0 Downvote
Spot on skip the requirement was to have the highest value at the top with the corresponding category. But good work Zathros you help me to solve my problem
Thanks again
Nick
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

arvarr
  • Locked
  • Question
Excel Formula Help 1
Replies
7
Views
564
SkipVought
S
PSchubert
  • Locked
  • Question
Excel: Formula only works with certain numerical values
Replies
12
Views
943
dhookom
dhookom
BugZap13
  • Locked
  • Question
Excel formula to count occurrences of a value in a different sheet
Replies
5
Views
663
bs6600
bs6600
TomSalvato
  • Locked
  • Question
EXCEL-formula help 2
Replies
6
Views
335
TomSalvato
TomSalvato
Swi
  • Locked
  • Question
Excel Webservice
Replies
2
Views
393
mintjulep
M

Part and Inventory Search

Sponsor

Back
Top