Max but previous column

[tanha]

Hi.
Suppose I have two colums, one is Location and Second is Quantity, and I want the maximum but not like the max quantity display instead I like the Location of the Max is displayed...

Location--Quantity
A --10
B --20
C --10
A --30
B --15
A --50

now if u look the max is of A is 50 but instead I want to see A as the result

 

[unmarkedhelicopter]

for a given range :-
=INDEX(A2:A10,MATCH(MAX(B2:B10),B2:B10))

 

[tanha]

thanks very much, I will implement this method on my problem and then post the result....Thnaks anyway...

 

[tanha]

Mr. unmarkedhelicopter...
Sorry I cant implement your posted formula on my problem, plz see the attachment ...

 

[tanha]

if anyone could help me through this plz...

 

[unmarkedhelicopter]

Okay,
First, don't send private messages requesting help on a current thread.

What are your "two columns" ? are they not A & B ?
What range do they actually cover ?
I'll look at your attachement and see if it clears this up.

Why do you have two headers ? What is "extract number" ?
Where does Herat or Kabul enter the equation ?

 

[tanha]

Thanks for replying...

As you see in the attachment there are two sheets 1. Inventory 2. Max Of Property
1. Inventory: "Property Number" is what we enter, but "Extracted Number" is a formula column, which just extract the number from right of the "Property Number" column, but here I entered manually.

2. Max Of Property: In this sheet I will extract the Unique "Location" by Advanced Filter, and in "Max of Property Number" column I want to see the MAX but previous column...

3. The two header is for later use, just to filter and search the data...

4. The range will start from row 6 upto 10000

 

[unmarkedhelicopter]

Where does Herat or Kabul enter the equation ?

And what about Herat and Kabul ?
i.e. these are entered into your totals sheet but from where and how are they populated ?

Edit: Without seeing some data I can't help, you have data, you know what it should look like and therefore if you give it me you will know if the answer is correct. (It doesn't have to be full data, and you can 'snitise it' if you feel the need but without some actual data to play with my hands are tied).

 

[tanha]

see...Herat and Kabul are the Unique name "Location" from inventory sheet, and I need the formula "MAX but previous column" according to these Locations...

I know there are no more data, they are not needed...

as you see in "Max Of Property" Sheet, there is a column "Max of Property Number" and the result is like 12, 5 and so on, but I want their corresponding column data

 

[unmarkedhelicopter]

Please give sample data for tests

 

[tanha]

Thanks... Here is the attachment with data and explanation

 

[unmarkedhelicopter]

This is still less than clear.
On "Max of Property" sheet (what is that supposed to mean ?) You enter Herat for row 6 an then enter "22", by this am I supposed to infer you want SPSS/AFG/HRT/FA00022 looking up on sheet "Inventory" ? then you want the quantity available for SPSS/AFG/HRT/FA00022 ?? which according to this is 2 (i.e. D27) ???

We are not looking over your shoulder, we can not see your reports, requests for information or your data, you have to explain things step by step.

And if B6 is generated by a formula then show it.

 

[tanha]

On "Max of Property" sheet (what is that supposed to mean ?) You enter Herat for row 6 an then enter "22", by this am I supposed to infer you want SPSS/AFG/HRT/FA00022 looking up on sheet "Inventory" ? yes

then you want the quantity available for SPSS/AFG/HRT/FA00022 ??NO, I need Property Number from sheet Inventory
which according to this is 2 (i.e. D27) ??? NO, A27 on Sheet Inventory

 

[unmarkedhelicopter]

All:-
This almost makes sense but I just can't see what tanha is talking about. Can anyone else pick this up as I'm getting nowhere ?

 

[Brianwarnock]

It actually makes sense although his formulae on Max of property sheet are a little heavy, it should just be
=IF(ISERROR(Inventory!$N$6:$N$65536=A6),"",MAX(IF(Inventory!$N$6:$N$65536=A6,VALUE(Inventory!$B$6:$B$65536),0)))
but how you get from there to the cell in the previous column i don't know.
One cannot use the value being returned for Col B in a Lookup function as it may apply to many properties, you really need the cell ref offset by one col, but Index Match is not feasible for the reason mentioned above.

Maybe a custom function is required, but its beyond mydwindling expertise

Brian

 

[unmarkedhelicopter]

Well I can write a UDF if necessary but what am I looking up given what criteria ???

 

[Brianwarnock]

As I see it he is looking down "Inventory" column N to match "Max of property" col A in the row of the formula, and finding the MAX value in Inventory!colB, but then wants the data in Column A.

If his sample is real ie the data is always like that then
outerloop of Max of property col a
innerloop match to Inv!Col N read to last match
read off value in col A

Does that make sense?


Brian

 

[tanha]

First Thanks all,
See the attachment now, hope everything should be clear...

 

[unmarkedhelicopter]

Okay, your extract number was a UDF using a vbscript object.
You should not use UDF's on large sheets unless there is no alternative (they are bottlenecks and slow your spreadsheet down, especially when, like this, you are calling an external object. Native functions are ALWAYS faster). The alternative here is =T(RIGHT(A6,5)) if you want the text or =VALUE(RIGHT(A6,5)) if you want the number. (I would recomend the number but it's up to you). We need to know which for the next step.

So what you want is as follows :-
You type in "Herat" into "Max of Property" Sheet A6
You then want to consider ALL rows from sheet "Inventory" where the value in column N is "Herat"
You want to find (of these records) the largest value in column B and return the "Property Number" associated with that.
So Herat would return SPSS/AFG/HRT/FA00022 and Kabul would return SPSS/AFG/KBL/FA00003 ?

Would it EVER be possible to have two records with SPSS/AFG/KBL/FA00003 ?
How big will the table (EVER) get ? 10,000 rows ? 100,000 ? fill the sheet ?

You can only have 64k rows on recnt versions of Excel, except for Excel 2007 where you 'could' have upto 4M (is that correct ??? I just run 2002)

 

[tanha]

yeah you are right for the script (UDF), but anyway I would solve that...thanx for recommendation...


So what you want is as follows :-
You type in "Herat" into "Max of Property" Sheet A6
You then want to consider ALL rows from sheet "Inventory" where the value in column N is "Herat"
You want to find (of these records) the largest value in column B and return the "Property Number" associated with that.
So Herat would return SPSS/AFG/HRT/FA00022 and Kabul would return SPSS/AFG/KBL/FA00003 ?

yes you are right, I need what u mentioned in above statements...

Would it EVER be possible to have two records with SPSS/AFG/KBL/FA00003 ?

No, it is not possible...

How big will the table (EVER) get ? 10,000 rows ? 100,000 ? fill the sheet ?

suppose 10000, or end of the sheet...

thnks again...for everything, I hope it is clear now...so I am waiting for solution...