Sorting rows using formulae

[Fitch1]

I have an asset register sheet, and I’m trying to pull the assets that need PAT testing to another sheet in numerical order (although I have to strip the first number as it’s the class of the equipment).

I managed to use the small & offset commands in column L to numerically sort, but don’t know how to use that to line up the corresponding fields after that.
I’ve looked at vlookup, index, match, but don’t have the wherewithall to actually use any of them correctly.

Columns N to R is my feeble attempt… Close but no banana.

The final result should be:
(Column N)
1001
1002
1003
2004
2005
1006
1007
1008
1009
2010

Can someone point me in the right direction?
Thanks.
pat.ods (25.7 KB)

[Lupp]

You may sort with the help of formulas using a few helper columns. However, sorting this way gets complicated if there is more than one sort key you need to regard, or if the keys aren’t numbers, or if there disambiguation of equally ranking keys is needed. The preferrable functions SMALL(), LARGE() and RANK() also only work with numbers. But there is remedy even if a bit complicated.

The main problem can soon arise concerning efficiency. You may have a correct solution, tested with (say) 100 rows of data.
Trying to use the same attempt for 10000 rows can end up with completely insufficient efficiency. Most solutions don’t “scale linear”.

On the other hand the LibO API comes with very efficient sorting.
To use it you must either sort interactively or use CustomCode.
There are many advantages concerning the usage of API-based custom code in this special case. Alas! The pörobl.em of passiong arguments …

The attached solution dedmontratzesd the usage of formulas in two different ways.
patLupp.ods (27.1 KB)
It doesn’t interpret the values in column B as compounds. Of course you can introduce this too. But if this was your main concern my suggestions will not be of much value for you.

See also
patLupp2.ods (27.2 KB)

[Fitch1]

Thank you Lupp.
That is certainly very useful code, which I will store and peruse later (have just had a couple of whiskys - as you do…
I’m pleased to say that the Men’s shed this is for will have a max of about 30 mains operated tools, but over 100 hand tools eventually, probably.
I have attached the (almost) finished version, which shows how I separated the PAT items to a second sheet, to put the dates in.
I used copious amounts of helper cells on the second sheet, which I will probably make the text from column L onwards, white eventually, to hide them,
LCS-Asset.ods (30.3 KB)

Cheers!

[gaetanopr]

Hi, with a macro it would have been better, try the attached file.
The result is in the range B17:F26
pat.ods (18.8 KB)

[Fitch1]

Thank you for that.
I put it into the original file, and it’s great until you have any blank lines, then everything goes error 508.

Don’t quite know how to error check an array…

Using MATCH, I’ve managed to get the row for each record
MATCH(L4,I$4:I$13,0)+3
so now I’ve got the correct row, can I use VLOOKUP?

[gaetanopr]

I fixed the formula, now empty cells are considered. You could use data sorting to create a single support column, for example column A as per my example and then sort by column A in a different position. Among the options “COPY SORT RESULTS IN”
pat_gaetano.ods (18.8 KB)

[Fitch1]

Thank you.
That is one heck of a formula, and no helper cells required.
Neat!

[Fitch1]

I think I’ve done it using the MATCH and INDIRECT functions.
Yippee!
pat.ods (26.1 KB)