Solved: Listing Items In Order

[drums4monty]

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]

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]

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]

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]

Sorry Xld

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

[Bob Phillips]

Array enter it, control-shift-enter, not enter.

[drums4monty]

I still get those errors, the formula should be all in one line should'nt it?

[Bob Phillips]

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]

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]

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]

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]

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]

That would be great xld, but only if you have the time.

Alan

[Shazam]

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.