PDA

View Full Version : Solved: Listing Items In Order



drums4monty
11-24-2006, 02:56 AM
I am using this formula to put a list of data in order of popularity, however if there are two items the same it lists the first occurrence twice, how do I get it to list the second occurrence as well, and only the first occurrence once, i.e once an item is used it will not be used again? I hope this makes sense.

=INDEX($A$2:$A$16,MATCH(LARGE($B$2:$B$16,1),$B$2:$B$16,0))

Alan

Bob Phillips
11-24-2006, 03:15 AM
Try this array formula

=INDEX($A$1:$A$20,MATCH(LARGE($B$1:$B$20+1/(ROW($B$1:$B$20)*10^10),ROW(A1)),
$B$1:$B$20+1/(ROW($B$1:$B$20)*10^10),0))

drums4monty
11-24-2006, 03:29 AM
Thanks Xld

When I use your formula I get #NUM! error. I have posted the sheet on the forum so people can see what I need.

Alan

Bob Phillips
11-24-2006, 03:53 AM
You have to adjust it to your data, and array eneter it

=INDEX($A$2:$A$20,MATCH(LARGE($B$2:$B$20+1/(ROW($B$2:$B$20)*10^10),ROW(A1)),
$B$2:$B$20+1/(ROW($B$2:$B$20)*10^10),0))

drums4monty
11-24-2006, 04:03 AM
Sorry Xld

I get #N/A now. Im afraid I just dont know what I am doing. Thanks for the help anyway. :friends:
Alan

Bob Phillips
11-24-2006, 04:15 AM
Array enter it, control-shift-enter, not enter.

drums4monty
11-24-2006, 04:22 AM
I still get those errors, the formula should be all in one line should'nt it?

Bob Phillips
11-24-2006, 04:35 AM
It has to be in one cell, but it can be multiple lines.

Don't mean to be rude, but how did you manage to come up with the origianl, but struggle to input my version?

drums4monty
11-24-2006, 08:22 AM
I found it in a book but don't really understand it. Your example works great but I dont know how to put it on two lines like you have, also how do I copy and paste it into my original sheet? When I do it I get the errors again. I didnt think you were being rude, I am learning and need all the help I can get.

Bob Phillips
11-24-2006, 08:31 AM
It is not necessary to get it on two lines, I just do it to make it easier to read.

To achieve this, you type part of the formula, then Alt-Enter, then the rest of the formula. You can't just break anywhere, it must be a njatralk break point, such as after a comma, or a bracket.

I am struggling to understand why you cannot paste it into your workbook. Did you Ctrl-Shift-Enter it?

drums4monty
11-24-2006, 09:01 AM
Yes I enter it like that. I have written it from scratch and it now works ok, many thanks for all of your help, you have been very patient and a credit to this forum.

Like I say I have it working but have not got a clue how or why it works?

Alan

Bob Phillips
11-24-2006, 10:02 AM
Yes I enter it like that. I have written it from scratch and it now works ok, many thanks for all of your help, you have been very patient and a credit to this forum.

Like I say I have it working but have not got a clue how or why it works?

Alan

It is a very complex formula Alan. It is simpler if you use intermediate helper columns, but this way does it all in one hit. If you would like, I will attempt an explanation.

drums4monty
11-24-2006, 11:19 AM
That would be great xld, but only if you have the time.

Alan

Shazam
11-24-2006, 12:31 PM
Hi xld,


I'm trying to send you a private meassage. but its giving me this mesaage.


xld has exceeded their stored private messages quota and can not accept further messages until they clear some space.