Sort the Data with Repeated Records (with formulas only)

[LO_user4]

Dear Friends

Assalamualiqum…Brother…Hopping for your better health and safety…

Please note that I have some data in excel which is produced by software…
I have to Filter and Arrange the data as shown in Input table present in Excel File attachment…
It is requested to kindly use formulas and produce the Output table shown. (please use formulas, it will help me in learning Excel)

Although, I can use Filter options with selected results form the column, but I request if formulas can be used…
I have tried to using INDEX, MATCH, ROW, COUNTIF but I am unable to produce the required results…
I am unable to make a logic about sorting, filtering and arranging the data as per output table…

Thanks
Excel File.xlsx (22.0 KB)

[Lupp]

Sorting with ordinary Calc formulas is a tricky and inefficient approach.
What you want to achieve is done applying standard tools interactively within a minute (See extra sheets in the attachment).

In your example, however, we needn’t actually to sort or filter, but only to re-arrange the data. Accepting lots of disadvantages it can be done with sufficient efficiency by Calc formulas.

However
You got me to try it once more though I knew better.
As a user with some experience in such things I had to work with concentration for an hour. An unexperienced user may need some more hours to understand the solution (first sheet only).

Don’t continue on this way. If you have similar tasks often, there may be good reason to write specialized code, or to transfer the data to a database which better supports the kind of opperations. Otherwise work interactively.

And ByTheWay: Fancy formatting -if at all- as the very last step when all the functionality is implemented.

reArrangingSpecialCase.ods (66.6 KB)

No word about Excel. I don’t know any recent version and forgot gladly about the old ones.

==Editing 1/2 d later==
To make sure my nose stays intact , I’ll add a solution that actually sorts. It is quite old and arose out of a passion for experimentation.
I still recommend what I have already explained above. But interesting can also be procedures that have disadvantages in practice.
sortByFormulas.ods (168.6 KB)

[dscheikey]

Hi @LO_user4, your post is a bit confusing. You write in a LibreOffice Ask page that you want to learn something about Excel and attach an XLSX file. It’s hard to know exactly what you mean.

I have tried to do your task of sorting the input list according to your instructions with functions.
LibreOffice Calc does not have a ready-made sorting function at the moment. You could upgrade it with user macro functions.
I have solved the task as you requested with standard formulas. In LibreOffice Calc, however, you need 2 auxiliary columns, which you can hide if you want.
In the first auxiliary column ‘U’ I calculate the subsequent order of the data.

=SUM(MATCH(B5,B$5:B$14,0),ROW()/1000)

The rank is calculated in the second auxiliary column ‘V’.

=RANK(U5,U$5:U$164,1)

The list is then created in the output table with INDEX() and MATCH().

=INDEX($A$5:$I$164,MATCH(ROW()-4,$V$5:$V$164,0),COLUMN()-23)

Because you want to learn about Excel. In OFFICE 365 EXCEL there is the SORTBY() function. With this you can reach the list with a single formula (see cell AH5). Auxiliary columns are not needed here.

=SORTBY(A5:I164,XMATCH(B5:B164,B5:B14)+ROW(B5:B164)/1000)

Excel File.xlsx (33,6 KB)

I hope I was able to help you.