Excel, Need to reference the left column after using larger

Question

I need to create a sheet on my work where one of the data it has is how many people you attended in a neinghorhood in a month (social service) then we need to contabilize this data to discover the top 10 most attended neighboorhods.

What I did:

First I use cont.if to discover how many attendances we had in every neighboorhood, that generates a sheet like that:

|--------------|--------------|
|Neighboorhood |Ocurrences    |
|--------------|--------------|
|N1            |12            |
|N2            |3             |
|N3            |15            |
|...           |...           |
|NX            |xx            |
|--------------|--------------|

etc...

But then I need to sort this data in other sheet (a report one) where it would automatically pick the 10 neighboorhoods with most ocurrences and automatically display this data in a table (as it will be used by people with little knowledge of office suit, so they would only need to put how many attendances they had and the table would do all the hard work automatically), I tried to use a formula like:

|-----------------------------------|--------------------|
|Most Ocurrences                    |                    |
|-----------------------------------|--------------------|
|=Larger(offset($O2:O$6,0,-1,1,1),1)|=Larger($O$2:$O$6,1)|
|=Larger(offset($O2:O$6,0,-1,1,1),2)|=Larger($O$2:$O$6,2)|
|...                                |...                 |

in hopes that it would show something like this:

|-----------------------------------|--------------------|
|Most Ocurrences                    |                    |
|-----------------------------------|--------------------|
|N3                                 |15                  |
|N1                                 |12                  |
|...                                |...                 |

but it ends in a error like that:

|-----------------------------------|--------------------|
|Most Ocurrences                    |                    |
|-----------------------------------|--------------------|
|#NUM!                              |15                  |
|#NUM!                              |12                  |
|...                                |...                 |

Because I believe the program is considering that I want to offset the values used on the formula, and not the result, so it tries to calculate the larger with the offset which uses number, but the cell to the left has text so the error. But what I need is to it to calculate the larger, then pick up the value of the cell left (or right if its easier) from it and display it too, is possible to do it without a macro?

Like:

Column A2: shows the value of the cell from the left of the largest value

Column B2: Find the largest value.

Column A3: shows the value of the cell from the left of the second largest value

Column B3: Find the second largest value. etc...

Is that possible? i don't want to use macros as the people who will deal with it don't will know how to run macros, neither that would be possible, as they only have libre office, while we have office.

Thanks in advance!

Answer 1

So first lets get the numbers in order:

A slight change to your LARGE formula will make it Dragable:

=LARGE($O$2:$O$6,ROW(1:1))

This will make it so you do not need to hard code the sequence in each formula.

Next let's get the name:

=INDEX($N:$N,AGGREGATE(15,6,ROW($O$2:$O$6)/($O$2:$O$6=B2),COUNTIF($B$2:B2,B2)))

---

EDIT

For pre 2010 excel you will need to use this array formula:

=INDEX($N:$N,LARGE(IF($O$2:$O$6=B2,ROW($O$2:$O$6)),COUNTIF($B$2:B2,B2)))

As an array formula it must be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode. If done correctly then Excel will put {} around the formula.

---

This will return the neighborhood. It will also ensure that if there is duplicate numbers in column B it will find the next.

It refers to the numbers returned in column B.

---

With your data on a sheet called database: