Excel help with sorting formula

[Tankstand]

Hello every one!

Can anyone help with a formula please?

In the included file, you will see that I need to return a name that is to the left of a number that has been returned.

As the numbers in the range are updated, the top five list amends itself, but I cannot work out how to return the name associated with the value.

Thanking you all.

Mark

[chrisb691]

Other ways to do this, but I used vlookup. I had to move the names to the right, as the function works from left to right and your numbers become the index. :)

[Wongo]

You need to use vlookup.

Here is an example

Give range F1:G26 a name. Here is how, highlight F1 to G26, then enter the name myList int the textbox above A1

in cell try this formula in G30

=vlookup(E30, myList, 2, false)

you should get 117.

Now copy the formula to cell.

[Wongo]

Sorry, I didn't read you question carefully.

You need to move the list of names to the right of the list of numbers.

Give the range a name

use vlookup to check a number against the list. You will get the corresponding name. :2tsup:

[ian]

Mark
your son may need to do some more work on his table of numbers

while VLOOKUP is the formula he needs, the way it works has some traps.

VLOOKUP will try and find the exact match -- however if the number to be tested and that in the lookup table are not exactly equal, VLOOKUP will use the largest value that is less than or equal to the lookup value. With on screen rounding what looks like twoi instances of 176 can be two subtly different numbers leading to erronous results.

I saw that your example had a significant rounding.

[Tankstand]

Thankyou for the replys and attempts.

Chris, alas Vlookup seems to return the first name that meets the criteria, type in say 200 in different cells and you will see.

Forgive the very simple example that I have provided.

This is for myself. Our Quality man is leaving (Cancer) and I am trying to improve the way our quality data base at work is managed (Now that it is my job to archive production scrap sheets and generate the graphs).

Currently there is way, way too much repetition and re-entering of data.

I am very familiar with Vlookup (An excellent formula) but re-arranging the fault codes to the right of the quantities might not go down too well.

There are 9 different part types with up to 20 different fault codes per part! (Good fun!):no:

This "Top five" faults graph is about the last one of many that I have automated, alas this one has me stumped.

Thanks again

Mark.

[Wongo]

Ian, if you set the last parameter to false it will only return the exact match or #N/A for a no match.

=vlookup(E30, myList, 2, false)

[ian]

Wongo
I understand, but I was trying to alert Mark to the fact that while his spreadsheet may display 176 in a particular cell, if the underlying number that has been on scren rounded to 176 is actually 175.676, VLOOKUP will return the table value for 175 not that for 176

[ian]

snip
I am very familiar with Vlookup (An excellent formula) but re-arranging the fault codes to the right of the quantities might not go down too well.

There are 9 different part types with up to 20 different fault codes per part! (Good fun!):no:

This "Top five" faults graph is about the last one of many that I have automated, alas this one has me stumped.

Thanks again

Mark.Mark
the way around this reluctance to modify an existing worksheet (or data base table) is to keep the original and create second worksheet (or table) -- where the data is arranged in the order VLOOKUP needs to work

[ajw]

Attached file shows another way to do this. Data in two columns that can contain any number of row entries, in any order. The summary table to the right uses:

Large function to get the top 5 (or whatever) scores
Index and Match functions to perform name lookup. vlookup requires the lookup value to be in the left hand column. This method overcomes this - but I'd probably change the column order or put a hidden column in to make the vlookup work.

Cheers,

ajw

[chrisb691]

Attached file shows another way to do this. Data in two columns that can contain any number of row entries, in any order. The summary table to the right uses:

Large function to get the top 5 (or whatever) scores
Index and Match functions to perform name lookup. vlookup requires the lookup value to be in the left hand column. This method overcomes this - but I'd probably change the column order or put a hidden column in to make the vlookup work.

Cheers,

ajw
Nice solution ajw.

[ajw]

After re-reading your post, I think there's a better way to look at the problem you're trying to solve. This method works for Excel 2007 onwards.

The zip file contains a xlsx file. The forum wouldn't let me upload it directly (wrong file type). You'll need Excel 2007 or 2010 to open this file.

I've put a second tab on your spreadsheet to show how I'd collect and summarize the data. For this example, I've used types of linen (towels, sheets etc) and faults that are found in a commercial laundry (torn, stained etc). This sounds similar to the situation you've described.

The summary table on the right hand side uses the Countifs function to count the number of rows that contain the name of an item and the fault. The summary below uses the Large function embedded into the vlookup function, to show the top 3 problem items. You could similarly do the top x problem types.

I've also included a column called Rank. This uses the RANK function to show the relative ranking of each score. With this data, there are two items with the same number of faults. They both rank 3rd in the list. You'll see that there's no 4th in the rank list. It's a useful function.

Let me know if this isn't clear or you'd like some more help with the problem.

cheers,

ajw

[Tankstand]

Thanks AJW

Alas, auto sorting with index and match struggles when there are more than one high count.

I'd love to look at your last solution, but already, (By looking at the instructions) I have no desire to install Gzip.

Is it possible you could E-mail me the *.xlsx?

Thanks

PM coming your way.

Mark

[ian]

alternatively, AJW could save the file in the .xls format

I'm interested to look at his solution

[ajw]

Saving it as an xls file will disable the formulae I've used. The "countifs" function is new in 2007 / 2010, which requires the xlsx file extension. It would be good if this file type could be added to the permissable files for uploading...

I've received a PM from Tankstand and will email him the excel file directly.

I've put a jpg of the screen from Excel with the Countifs cell highlighted so you can see how the formula is constructed.

cheers,

ajw

[ian]

thank you

[ajw]

Ian - I've modified the method to take account of the situation that arises when two or more "faults" have the same count. The table with the data for the graph uses the RANK column (sorted) to get its data.

Tankstand and I are corresponding via email to get this solved - once we've got a final solution I'll post it here.

cheers,

ajw