Sort in ascending order multiple rows (more than one rows), and copypaste it

[egotajcs]

Hi guys,

I like to solve my excel problems on my own, but sometime I stuck into a problem which I cannot solve.
I have a big database and I would like to sort prices in ascending order. But only inside for every four, five or six (or basically, any) rows. As I show it in the printscreen – and uploading the excel – I could do it for the first six-packed row. But I cannot copy-paste the formula downward for the pack (which is only 5 rows).
The date can be helpful to separate the packs.

I only hope I could tell you guys, what I would like to do.

I think, I should use arrays (CTRL-Shift-Enter), but that thing is over my brain sadly.

https://s11.postimg.org/pmok06ikz/probe_Formula_for_ordering_some_rows.jpg

Here it is my WIP excel file: https://anonfile.com/K5e9P4bbb4/results_probe_Formula_for_array_fav.xlsx

 

[Marcelo Branco]

Try this

Array formula in D2 copied down
=SMALL(IF($A$2:$A$16=A2,$C$2:$C$16),COUNTIF(A$2:A2,A2))
confirmed with Ctrl+Shift+Enter, not just Enter

Array formula in E2 copied down
=INDEX($B$2:$B$16,MATCH(1,IF($A$2:$A$16=A2,IF($C$2:$C$16=D2,1)),0))
confirmed with Ctrl+Shift+Enter, not just Enter

M.

 

[egotajcs]

Wow, man, you are the man, thank you very-very much!

 

[Marcelo Branco]

You are welcome. Glad to help

M.

 

[egotajcs]

Oh, Marcello, it turned out, there is a glitch in the formula. It is really not so bad, I can live with it, but I'm curious, how it should be avoid.
How the formula react if within a pack, there is a repeating value?

Is there a way to modify the formula, if there is a repeating value, find the second occurence as well?

Here it is the excel file: https://anonfile.com/25v4P1b5bf/results_probe_Formula_for_array_fav_GLITCH.xlsx

 

[Marcelo Branco]

According your first data sample i assumed (wrongly, as i can see now ) there was no duplicates in the same group (date).

To deal with such scenario try:
Array formula in E2 copied down
=INDEX(B:B,SMALL(IF($A$2:$A$37=A2,IF($C$2:$C$37=D2,ROW($C$2:$C$37))),COUNTIFS($A$2:$A2,A2,$D$2:$D2,D2)))
Ctrl+Shift+enter

M.

 

[egotajcs]

Thank you, Marcelo, much appreciated! Thank you, thank you! :D

 

[Marcelo Branco]

You are welcome.

M.

 

[egotajcs]

Hi Marcelo,

May I ask you further? Now, I would like to define the real order, because these two columns were really hust a helpful columns to show the real price orders within the times. I thought, I could do is on my own. First, I just wanted to sort it out with an INDEX/MATCH combination, but I realized that there could be repeated prices. Then - you know, I like to solve excel problems on my own - I wanted to solve it with an array formula, using your solution as an inspiration, and I thought, I could manage it.

My formula isn't working, because the 'E' column is a formula itself, not a plain number. If I change it to plain numbers, it works. (see row 17-27). What am I doing wrong?

Also, if there is a repeating number, within a pack, how I should deal with it? Similar to the comment upward? I think I could understand the simplier array formula, but the second one, when you could deal with duplicates (the long one with SMALL and COUNTIF), I don't understand.

So, may I ask you to show me this final, third columns solution?

Please, see the excel file from here: https://anonfile.com/QaT0Pcb2be/results_probe_Formula_for_array_fav_GLITCH_WORKING.xlsx

 

[Marcelo Branco]

I don't understand what you are trying to do...
Questions:
You already have the order numbers in column E, so what are you looking for now?
What you mean by "real order"?
You said the formulas are working in rows 17-27, but they return strange results: 5 for order whose price is 65 and 5 too for order whose price is 90. It doesn't seem correct for me.

M.