Excel 2007 : listing x largest values

[confusus]

I have some data in A1:J150 (with couple of #N/A errors in I1:I150) in sheet1.
What I want is to list 10 max values from I column and their respective cell with text string that is located in column B.
I know i need array formula, VLOOKUP() and IFERROR() maybe, but I just cant figure it out properly.
Need assistance

[DonkeyOte]

A sample file would be advised as there quite a few unknowns...

For ex.:

You specify A1:J150 as holding values but then state you want to return associated value from B1:B150 ... so does B1:B150 hold numbers or not ?
If not what does A1:A150 hold out of curiosity ?

Do the numerics in the matrix repeat or are they always unique ?

It then follows that if duplicated which of the duplicates is given precedence - is it based on the row or column - eg 100000 appears in G1 and D2 ... which is to be listed first ?

etc...

Generally speaking correcting underlying errors at source is advised, however, in this instance the likelihood is that you will need an array at which point accounting for the errors is pretty trivial.

[confusus]

I want to be able to list rows that meet certain criteria(greater than given value..., max..., list all larger that with an exception of person XX, and so on.
In sample workbook, lets say i wanted to list rows (in my case i want to list just value cell with persons name, not the whole row.) value row is decimal numbers, lookup is person name. Considering what you said about same values at the end of the list, it doesn't matter, it's not that critical..

[DonkeyOte]

So, to be clear, will you have duplicate values in I - yes / no ?

I ask because this has a major impact on the possible solutions open to you.

[DonkeyOte]

On an aside - why not just a Pivot Table ?

Set the Pivot up such that:

Name, Email & No fields are Row Labels
Value Field as Data Field set to Sum.

Right click on any Name - choose Filter - Top 10 - by Sum of Value.
Right click on any Name - choose Sort - More Sort Options - Descending based on Sum of Value
(modify layout to suit)

[confusus]

First: Yes, I will/should.

The thing is: I created workbook to accept import from external program (gps), it corrects text exported entries to real dates, matches names with vehicles with VLOOKUP, categorizes stuff and so on. What I actually need, is the list of exceptions, that have values drastically different than the majority. Movement after work shift, max speeds, max distance, and so on. So, most of the values in column I will be #N/A errors or values close to zeroes. I need 10, or 12, or 15, does not really matter, (mostly it will be 5 or six of them out of 200), that have like 500% or more of an standard value.

[DonkeyOte]

I strongly suggest you use a Pivot Table.

Handling duplicity (and underlying errors) with formula can be a pain / inefficient - particularly if you choose to avoid helpers.

A Pivot (based on your sample) will do all you require, efficiently and without need for any formulae.

[confusus]

Yes, i tried it the first time. I placed it on sheet 4. Sheet 1 is table, sheet2 is raw import from gps, sheet 3 contains various reports based on table on sheet1, in which i tried to place that list I asked about, but not working as planned. Now, pivot table actually seems like the best thing! really. The only major problem is that i must correct error entries in table on sheet1, now that is not an option, because all cells are references to sheet2, formated and cleaned with huuge formulae. Which may get even bigger if I replace errors with zero values :/

[DonkeyOte]

I confess I'm a little lost.

See attached "proof of concept" which is based upon your earlier sample.

If you need to, post a file that reflects your real setup.