Excel

[Qelan]

umm, I know this maybe a strange questions but here goes.
How could I get a spreedsheet to display a field that is linked to a value that meets the specified parameters.
ex:
Fields A1:A3 contain strings; Apple, Bannana, Taco. Fields B1:B3 contain values denoting amounts of items in column "A".

What I want to do is display the values in column "A" in order based on the values in column "B".

Actually I want to fliter the data after its sorted but I can deal with that myself.

[JohnTaber]

I am not an Excel guru but...

You highlght the rows you want to order then go to the Data:Sort option. When you get there you set if there is a header row and decide on the column to use as the basis for the sort. Give it a try and see if that is what you are after...

[Fuzzy Gnome]

=LARGE(B1:B3,n) will give the nth largest value in B1:B3. Make an array of them with all values of n. You can then use one of the lookup functions to get the corresponding value in A1:A3.
This would fail if you don't have unique values in B1:B3 because it wouldn't distinguish them. For example if B1:B3 was 5, 7, 5 the output might be apple, apple, banana.

[Qelan]

Ok got something wierd hapening
=LOOKUP((LARGE(B2:B26,1)),B2:B26,A2:A26)
=LOOKUP((LARGE(B2:B26,2)),B2:B26,A2:A26)
=LOOKUP((LARGE(B2:B26,3)),B2:B26,A2:A26)
=LOOKUP((LARGE(B2:B26,4)),B2:B26,A2:A26)
=LOOKUP((LARGE(B2:B26,5)),B2:B26,A2:A26)

I made 5 entries to test the formua. It works sometimes but other times it gives me very cryptic results. Could you look at this to see where I went wrong?

edit: Oh now I know where I went wrong. The lookup command needs the original values to be in order.. sigh

[Fuzzy Gnome]

OK try this
=INDEX($A$1:$A$26,(MATCH(LARGE($B$1:$B$26,1),$B$1: $B$26,0)))
=INDEX($A$1:$A$26,(MATCH(LARGE($B$1:$B$26,2),$B$1: $B$26,0)))
etc.

[Qelan]

d00d that works PERFECTLY! Thanks a million. I should be able to finish the rest of it. I need to take the same formula and do something like
(IF C2:C26=(x,y,or z),(INDEX($A$1:$A$26,(MATCH(LARGE($B$1:$B$26,1),$B $1:$B$26,0)))),"")
(IF C2:C26=(x,y,or z),(INDEX($A$1:$A$26,(MATCH(LARGE($B$1:$B$26,2),$B $1:$B$26,0)))),"")
(IF C2:C26=(x,y,or z),(INDEX($A$1:$A$26,(MATCH(LARGE($B$1:$B$26,3),$B $1:$B$26,0)))),"")

Actually what I am trying to do is sort the list into even smaller groups. Hmm, If you don't mind messing with this stuff still I would apreaciate any of your advice. Meanwhile I will try to solve it. But thanks again for fixing that formula for me.

Hehe I was looking at how WRONG the logic is in the example I was trying to make above. I will try to make a better example.

I need to take the original formula then if it is part of group (n1,n2,or n3) it will display it in first field else it does nothing.

Now it applies the formula again in sequence and depending on if it is part of the group(n1,n2,or n3) it displays it in second field or does nothing.

This continues but I would like the results to display in sequence without making any spaces. Not sure If I made any sense or not. I will keep trying to find the answer and I appreciate all the time you have gave me so far.

So far this is what I have as how to start breaking the list down into smaller groups:
=OR((INDEX($C2:$C26,(MATCH(LARGE($B$2:$B$26,1),$B$ 2:$B$26,0))))=n1)

Problem I have with this is then I will need to make an array out of it to try to get a "TRUE/FASLE" result to represent each grouping:
=OR(((INDEX($C2:$C26,(MATCH(LARGE($B$2:$B$26,1),$B $2:$B$26,0))))=n1),((INDEX($C2:$C26,(MATCH(LARGE($ B$2:$B$26,1),$B$2:$B$26,0))))=n2),((INDEX($C2:$C26 ,(MATCH(LARGE($B$2:$B$26,1),$B$2:$B$26,0))))=n3))

Now if this is the only way to do this then I would need to put that formula into another array so it will display my field($A$1:$A$26...) for a "TRUE" result:
=(IF(OR(((INDEX($C2:$C26,(MATCH(LARGE($B$2:$B$26,1 ),$B$2:$B$26,0))))=n1),((INDEX($C2:$C26,(MATCH(LAR GE($B$2:$B$26,1),$B$2:$B$26,0))))=n2),((INDEX($C2: $C26,(MATCH(LARGE($B$2:$B$26,1),$B$2:$B$26,0))))=n 3)))="TRUE"),(INDEX($A$1:$A$26,(MATCH(LARGE$B$1:$B $26,1),$B$1:$B$26,0)))),"")

Now if all this is correct then I can move down to next item on the list:
=(IF(OR(((INDEX($C2:$C26,(MATCH(LARGE($B$2:$B$26,2 ),$B$2:$B$26,0))))=n1),((INDEX($C2:$C26,(MATCH(LAR GE($B$2:$B$26,2),$B$2:$B$26,0))))=n2),((INDEX($C2: $C26,(MATCH(LARGE($B$2:$B$26,2),$B$2:$B$26,0))))=n 3)))="TRUE"),(INDEX($A$1:$A$26,(MATCH(LARGE$B$1:$B $26,2),$B$1:$B$26,0)))),"")

Then 3rd item and so on.

Hope I made this clear enough and didnt put too many logic errors so it doesnt make sense.

[Qelan]

I just got back home and tried the formula I was working on but after awhile I just started over because it will be much easier then trying to find out where the error is in that mess.

[Qelan]

Ok this is what I have now:
=IF(OR((INDEX($C2:$C26,(MATCH(LARGE($B$2:$B$26,1), $B$2:$B$26,0))))=n1)=TRUE,"Correct","")

This works for half of what I need but I was wondering if there is anyway to get n1 to equal a group of number or items?

[Fuzzy Gnome]

I think there probably is.
(not being very helpful today)

[Qelan]

Ok this is what I am up to so far:
=IF(OR((INDEX($C2:$C26,(MATCH(LARGE($B$2:$B$26,1), $B$2:$B$26,0))))=n1)=TRUE,INDEX($A$1:$A$26,(MATCH( LARGE($B$1:$B$26,1),$B$1:$B$26,0))),"")

This works great I just hope there is a shortcut to get "n1" to equal several values. Maybe a TRUE logic statment?

[Fuzzy Gnome]

You can test for multiple values like this:
=IF(ISNA(MATCH([your expression here],N1:N8,0)),"","Correct")
That checks to see if the value of the expression is in N1:N8 and chooses the output accordingly.

[Fuzzy Gnome]

BTW, your OR() function doesn't do anything.

[Qelan]

Isnt the OR function is what is giving me my "TRUE"/"FALSE output?

I am going to try to input that last function you gave me because so far to get it to work I had to nest several IF statments lake this:
=IF(OR((INDEX($C2:$C26,(MATCH(LARGE($B$2:$B$26,1), $B$2:$B$26,0))))=n1)=TRUE,INDEX($A$1:$A$26,(MATCH( LARGE($B$1:$B$26,1),$B$1:$B$26,0))),IF(OR((INDEX($ C2:$C26,(MATCH(LARGE($B$2:$B$26,1),$B$2:$B$26,0))) )=n2)=TRUE,INDEX($A$1:$A$26,(MATCH(LARGE($B$1:$B$2 6,1),$B$1:$B$26,0))),IF(OR((INDEX($C2:$C26,(MATCH( LARGE($B$2:$B$26,1),$B$2:$B$26,0))))=n3)=TRUE,INDE X($A$1:$A$26,(MATCH(LARGE($B$1:$B$26,1),$B$1:$B$26 ,0))),IF(OR((INDEX($C2:$C26,(MATCH(LARGE($B$2:$B$2 6,1),$B$2:$B$26,0))))=n4)=TRUE,INDEX($A$1:$A$26,(M ATCH(LARGE($B$1:$B$26,1),$B$1:$B$26,0))),""))))

This works but it's very messy and I believe there is a limit to how many IF statments I can nest together.
BTW I really appreciate you going through all this trouble. Thanks alot

[Qelan]

Originally posted by Fuzzy Gnome
BTW, your OR() function doesn't do anything.

Thanks I see what your talking about now. Working on using that last function you showed me. Thanks again

[Qelan]

OMG you own dude! That function is working flawless. I only have to make small modifications to get it to sort the diffrent groups but its way easy now. Thanks a LOT! Oh, here is the final form of it to sort a small group:
=IF(ISNA(MATCH(INDEX($C2:$C26,(MATCH(LARGE($B$2:$B $26,1),$B$2:$B$26,0))),D4753,0)),"",(INDEX($A$1:$A$26,(MATCH(LARGE($B$1:$B$2 6,1),$B$1:$B$26,0)))))

If you want to modify it or anything I wont complain