Max but previous column (1 Viewer)

[unmarkedhelicopter]

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 ?

 

[Brianwarnock]

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

 

[unmarkedhelicopter]

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

 

[Brianwarnock]

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

 

[unmarkedhelicopter]

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

 

[Brianwarnock]

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

Brian

 

[tanha]

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

 

[unmarkedhelicopter]

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.

 

[Brianwarnock]

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

 

[unmarkedhelicopter]

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 !

 

[tanha]

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?

 

[unmarkedhelicopter]

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.

 

[tanha]

Ok UMH...Waiting for u

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

 

[unmarkedhelicopter]

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 ?

 

[tanha]

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

 

[unmarkedhelicopter]

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

 

[tanha]

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

 

[unmarkedhelicopter]

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

No, 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 statement

I'm saying this SHOULD be done in Access (or ANO DB), even if you don't have Access and want to use Excel.

 

[tanha]

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

 

[tanha]

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