finding the largest document revision

[skittlz]

I have a spreadsheet which has a document numbers and document revisions. The revisions are either engineering (always numbers - so 1, 2, 3, 4 etc) or production (Alphabets - A through ZZ). I need a way to find the "highest" document revision. I tried using ASCII with the CODE function but it gets screwed up with numeric revs, or anything above Z.

Engineering Revs are always smaller than production revs.

Any suggestions?

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Pretty hard to guess what your data layout is and where/how the result(s) should appear. What about a small set of dummy sample data with the expected result(s) manually filled in and explain again in relation to that sample data?
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[skittlz]

Hi Peter,

Unfortunately I cannot install the plugin - work laptop. Here's a "Sample Data" screenshot - hope this helps! I'm using the latest revision of Excel - so 365. Hope this clarifies some of your questions!

Regards,

- Ritul

 

[Peter_SSs]

Unfortunately I cannot install the plugin - work laptop.

In that case it would be a good idea in any future threads to state that in the first post so we don't keep asking.
An alternative (not as good though) is to simply copy/paste from Excel into the forum and tell us what range is posted and what formulas (if any) are used.

I'm using the latest revision of Excel - so 365

Please put it in your account details as requested so the information is always readily available to helpers.

Here's a "Sample Data" screenshot

I am afraid that I don't understand it.

1. Does it mean that 5 cells in column B have formulas and the others do not? Or should those coloured cell values actually be appearing somewhere else? (if so where?)
2. Why do some document names (Proc-10 & Proc-35) not have anything marked?

 

[skittlz]

Hi Peter,

Sorry for the confusion - I just realized I had several typos my screenshot (I wasn't paying attention apparently) -

I'll try to clarify:

The "Procs" are names of the procedures. Its just a naming convention we use.

The Column B has revisions for each Proc - so every time a document is modified in any way - it gets a new revision.

For example, Proc-10 was updated 3 times - from 1, to A, to B. The highest (current) rev is B, so the formula for Proc-10 should return B.

Similarly, Proc-35 is at Rev 2 (updated twice). I'm looking for the formula for Column C, which'll tell me the "Highest" revision for each Proc.

My sincere apologies for the errors.

 

[Peter_SSs]

so the formula for Proc-10 should return B.

I'm thinking that the different result (C) shown in the image is probably actually what you want?

From the image it appears that you simply want the last item for each document name?

Also not sure about how the results should be presented but see if either of these is any use

24 02 27.xlsm
	A	B	C	D	E	F
1
2	Proc-10	1			Proc-10	C
3	Proc-10	A			Proc-20	C
4	Proc-10	B			Proc-30	1
5	Proc-10	C	This one		Proc-35	2
6	Proc-20	A			Proc-38	A
7	Proc-20	B			Proc-40	AA
8	Proc-20	C	This one
9	Proc-30	1	This one
10	Proc-35	1
11	Proc-35	2	This one
12	Proc-38	A	This one
13	Proc-40	Y
14	Proc-40	Z
15	Proc-40	AA	This one
16
Latest

Cell Formulas
Range	Formula
E2:F7	E2	=FILTER(A2:B15,A2:A15<>A3:A16)
C2:C15	C2	=IF(A2=A3,"","This one")
Dynamic array formulas.