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

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

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

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

if anyone could help me through this plz...

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 ?

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

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).

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

Please give sample data for tests

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

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.

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

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 ?

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

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

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

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

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)

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...

Seeing as you were unclear about whether the extract numbers would be text or numbers I have done a solution based on numbers. It does appear to work with text but this has not been fully tested, I can not guarantee that this will continue to work on a large table.

You were also unclear on how big the table could get, this has forced me to ask for specific ranges, I have built in 'resonable' error checking but again I can not guarantee that this will work in all circumstances. In a perfect world I could just ask for the Location and make assumptions about the rest but ... not very robust.

The attached spreadsheet works as you requested and you can get a view of how to use the function I wrote (yes ... it's a dreaded UDF !).

This is probably 'not optimal' I can think of 3 or 4 things I can do to speed this up but as we now have a solution, and it's highly unlikely that I will ever work on anything similar, what's the point ? Anyone else wanna have a go ?

Brian / ANYONE, I know it's good manners to zip files before posting (minimise storage requirements and bandwidth) but do you know (of all the stupid files you can upload) why you can't do a spreadsheet ?

Hi Unmarked
I havedownloaded your Zip and will take a look purely out of interest but do not expect to make any changes. I notice a slight irritation with the original poster, feel the same as he ignores questions, I wish I knew his location as English may be a problem for him/her.

I had a brainwave that if the format of the property name is fixed per location as per his example, he never answered my questions on this, then 2 working columns, that can be hidden, will solve the problem. Take Row6

Column D has a text 00000 in D1
then in D6
=CONCATENATE(LEFT(D$1,LEN(D$1)-LEN(C6)),C6)

Column c has his original formula in C6

and column B
=CONCATENATE("SPSS/AFG/HRT/FA", D6)

what do you think?
tanha will this work for you, are my assumptions correct?

Brian

I had a brainwave that if the fornmat of the property name is fixed per location as per his example, he never answered my questions on this then 2 working columns, that can be hidden, will solve the problem. Take Row6

Column D has a text 00000 in D1
then in D6
=CONCATENATE(LEFT(D$1,LEN(D$1)-LEN(C6)),C6)
=REPT("0",5-LEN(C6))&C6

Column c has his original formula in C6

and column B
=CONCATENATE("SPSS/AFG/HRT/FA", D6)
is for Herat and SPSS/AFG/KBL/FA is for Kabul so this may not work.I could well be wrong though (it's not unusual !) post your version and we'll explore that option, having said that if we were allowed a hidden column I could do it with an array formula

I like the Rept function, I hadn't come across that. Yes you have to put the correct Text in for each location given the current spreadsheet design, but otherwise I have tested on the data given and it works.

I'd love to see your array formula if you care to spend more time on this.

I see nothing wrong in hidden work columns, I put them over to the right of any data.

Brian

Brian, you show me yours and I'll show you mine :D

Okdokay.
D6 now uses the Rept function, D7 still has the original Concatenate function.

Brian

Thanks much Mr. unmarkedhelicopter, and all others, I am really sorry I could not explain more, because I had a really bad internet speed... the problem is fine and wonderful and working well Mr. unmarkedhelicopter....

About the extracting number, it is not fixed something like five, maybe the numbers in string is 6 or 7 or more...

Is it possible to extract all the numbers from right...just extract what numbers are at the right till reach the text??? SPSS/AFG/HRT/FA000012345 result = 000012345 or SPSS/AFG/HRT/KBL123 result = 123

Again thanks all here specially Mr. unmarkedhelicopr...

Well, here you are Brian.
I sort of cheated on both the solutions, the UDF above is still the best, I feel, as it makes no assumptions othar than code is allowed to run.
I included two solutions, one that uses 1 helper column and one that does not. The second is a mother of all array formulas (don't bother sending in bigger ones, it's just an expression) and maintenance for a user would be horrific.
The issues I have with both concern data integrity as (in my book) users don't seem to place this very high on their agendas, so if there are errors in the data ... then guess what ... there 'may' be an error in the result.

I also had a look at tanha's request for a native formula that would pull the end number from any string :-
=-LOOKUP(0,-MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),255))
assuming the string is in A1 of course. This will ONLY pull numbers from the ends of strings so you have been warned.

Hi heli
I agree that a solution withouy assumptions is normally best, I would say always but am aware the performance issues you quoted earlier, if the assumptions/conditions are set in concret then use them for a speedier solution if nescessary.
I look foreward to looking at your work when I get a chance, the domestic workload is a little heavy at the moment due to a kitchen refit. :(

Brian

Oooo ! doing it yourself or supervising ?
I have a friend who extended and got a 'moben' kitchen, 168 items on 'their' project managers snagging list, basically half had to be torn out, second time went VERY smoothly.

Good luck !

Thanks again Mr. unmarkedhelicopter,
1. The formula for extracting numbers from right is correct but unhappily if there is a number in the middle of the string then there is an error, I dont need the numbers in the middle of the sting, just i need the numbers occurs at the right side till reach at strings. SPSS/AFG/12HRT/FA00012 I need just 12, but there is an error...

2. For the Max Of Property, your UDF, I want the Max Of Peoperty Number according to two criteria, suppose I Have column "Location" and want to add another column "Type" then in UDF I can't Use the two criteria...What should I do?

Please stop with the Mr. bit okay ? it's just unmarkedhelicopter or UMH
I'll have a look at the file and get back when I can.

Ok UMH...Waiting for u

NOTE: See the attachment, which I add a new column on Inventory Sheet next to Location named "Type".

For your property number :-
How may final digits do you ever expect to have ?
Why is it not a consistent format ?
Does it always/Will it always - have an FA before the number ?
Why would you change a location code to have a number in it ?

How may final digits do you ever expect to have?
maybe 10 in further future, now it is five, but I want to find a method to just extract numeric digits from right side, till reach to text...I dont want extract numeric digits if exist in middle or at the beginning.

Why is it not a consistent format?
because,
If the location is "Herat" then the format will be SPSS/AFG/HRT/FA00001
If the location is "Kabul" then the format will be SPSS/AFG/HRT/FA00001
If the location is "Pushton Zarghon" then it will be SPSS/AFG/PZ/FA00001
and so on...
also If "Herat" has sub-branches like "Station 2" then the format will be SPSS/AFG/HRT/ST2/FA00001
and If "Herat" has sub-branches like "Adrasken" then the format will be SPSS/AFG/HRT/AD/FAAG00001

and so on

Does it always/Will it always - have an FA before the number?
NO,
FA stands for "Fixed Assets"
A stands for "Assets"
G stands for "General"
FAAG stands for "Fixed Assets Integrated"

SPSS/AFG/HRT/FA00001
SPSS/AFG/HRT/A00001
SPSS/AFG/HRT/G00001
SPSS/AFG/HRT/FAAG00001

SPSS/AFG/HRT/Station 2/FA00001
SPSS/AFG/HRT/Station 2/A00001
SPSS/AFG/HRT/Station 2/G00001
SPSS/AFG/HRT/Station 2/FAAG00001

Why would you change a location code to have a number in it?
Suppose I have 9 inventory of Fixed Assets (FA) on Herat location, then the Property Number will be like follow:
SPSS/AFG/HRT/FA00001
...
SPSS/AFG/HRT/FA00009
and if another inventory on Herat we enter in the Database, then the Property Number will be like below:
SPSS/AFG/HRT/FA00010

and if we have a Fixed Assets (FA) inventory on Herat - Station 2, the the Property Number will be like follow starts from 1:
SPSS/AFG/HRT/Station 2/FA00001

and if we have a General (G) inventory on Herat - Station 2, the the Property Number will be like follow starts from 1:
SPSS/AFG/HRT/Station 2/G00001


for this reason I added another column named "Type", to find the Max Property according to "Location" and "Type".

Thanks UMH

So in summary you want the largest ending digits for any given type and location ?
What if there are none for that location or type ?

This does mean you will need an additional perameter.
Seeing as it's done in a UDF I could rewrite the UDF to have say :-
UMHMaxOfProperty_Type(Location, Type, Locations, Types, Property Numbers)
Do you actually need the number extract anymore as I can do that in the UDF ? (This would also tidy up your sheet).

From what you have said your property numbers are poorly thought out.
If all Property numbers have SPSS/AFG/ then that does not mean anything.
I think you could organise these much better.
Also what happens if you sell a property (compulsory purchase is possible, say a road is being built) does that old number become available to a new property ? or do you allow gaps to form ?
I assume from all that has been said that you use max to just refer to the newest property.

This would lend it's self to being done in a data-base, you could still use Excel as your front-end if none of your PC's have Access, or even if they do and your people are happier working in Excel. You could also have a single file with many people working on it. (Don't even mention Excel shared workbooks !)

So in summary you want the largest ending digits for any given type and location ?
yeah, I need just the ending numbers, not in middle and start if there is any...

What if there are none for that location or type ?
Then there wont be any inventory in the database, just we record the inventory when we distribute something...

Do you actually need the number extract anymore as I can do that in the UDF ?
That would be your kind doing that...in another UDF

If all Property numbers have SPSS/AFG/ then that does not mean anything.
Yeah I know it is useless, just SPSS/AFG is in case we send database to other country for review...know that this from AFG

I assume from all that has been said that you use max to just refer to the newest property.
You are completely right, I need the new Property number...

This would lend it's self to being done in a data-base, you could still use Excel as your front-end if none of your PC's have Access, or even if they do and your people are happier working in Excel. You could also have a single file with many people working on it. (Don't even mention Excel shared workbooks !)
I dont understand this last statement

Do you actually need the number extract anymore as I can do that in the UDF ?
That would be your kind doing that...in another UDFNo, I mean you don't need it as a column so we could get rid of it. I could still find the max. (Why don't you have a date aquired column instead).

This would lend it's self to being done in a data-base, you could still use Excel as your front-end if none of your PC's have Access, or even if they do and your people are happier working in Excel. You could also have a single file with many people working on it. (Don't even mention Excel shared workbooks !)
I dont understand this last statementI'm saying this SHOULD be done in Access (or ANO DB), even if you don't have Access and want to use Excel.

Ok...As you see reasonalbe do it, just please put comment on every line of VBA code if possible, that would be your kind...

Now I am doing this in Excel to have a prototype for testing, then I will do it in MS Access...It is used on a single computer...

Thanks for ur nice idea...

Respected UMH,
May I request you kindly post the solutions, because I am really in need of that...

Now I am doing this in Excel to have a prototype for testing, then I will do it in MS Access...It is used on a single computer...

Thanks for ur nice idea...

Tanha I don't have time to get involved at the moment, but I would say that if you intend to use ACCESS then don't bother prototypyng in EXCEL, a database and a spreadsheet are two very different pieces of software, requiring different approaches to their design.

Brian

Thanks Mr. Brianwarnock,
Here they want their database in excel, so I want to make it in excel sheet now...Thanks for ur advise

Mr. UMH,
May I request you kindly post the solution plz...Because I am really in need of that...

Sorry for posting again,
Anyone here guide and help me through this...Specailly Mr. UMH

The people on this board give of their free time to answer questions.
We have other lives that must take priority, jobs to got to, families to spend time with, vacations to take, food to eat, bodies that need care and sleep ( :) ) etc. etc.

Demanding answers to questions is NOT likely to get what you require.
Sending me multiple PM's demanding resolution is just plain annoying.
I did not just ignore you I just didn't see your demands.
I VERY nearly decided to give this thread up as a bad mistake, but that would have hurt others who may need some pointers for their similar problems.

This is obviously a business question and as such gets a lower priority on ALL forums. (Why should you get an answer for free (and support as well)) when by definition you should be paying for your solutions ?

I relented and here is your answer, but do NOT expect a repeat.
I have to put bread on the table too and spending time assuaging your demands does not enhance me or mine.

Thanks a bunch UMH,
It works perfect...