Sort data by repeating date and assign data

[Wojciech]

Hi, I have a table which is a database:

nr​	date​
87668​	2024-09-09​
68765​	2023-11-04​
84575​	2025-10-09​
85765​	2023-11-04​
06754​	2024-01-23​
76598​	2024-06-10​
76598​	2024-08-25​
74987​	2025-01-04​
75409​	2024-02-21​
54675​	2024-10-26​
87243​	2024-02-24​

and I would like to generate a table like this using formulas:

nr​	date​
85765​	2023-11-04​
68765​	2023-11-04​
06754​	2024-01-23​
75409​	2024-02-21​
87243​	2024-02-24​
76598​	2024-06-10​
76598​	2024-08-25​

I'm asking what formula to enter in A2 and B2 and drag down. I am kindly asking for help

 

[Joe4]

I really do not understand what you are asking.
I see that the second list is sorted by date, but why did some rows disappear between the first and second list, and some did not?
What is the logic there?

 

[Wojciech]

Its like sort smallest to largest by date and show only first 7 rows. I can simply do this in power query, byt cant do it using function. What is important is that the dates may be repeated, but they may have different values in column A.

 

[Joe4]

What version of Excel are you using? 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’)

If you are using Excel 2021 or Excel 365, so you have the newer Excel SORT and FILTER functions, then you can do it like this (assuming your date entries are really dates and not text):

So the formula to put in cell E2 is:

=SORT(FILTER(A2:B12,B2:B12<=SMALL(B2:B12,7)),2)

All you have to do is enter that formula in cell E2, and it will populate all of E2:F8 at once.

 

[Wojciech]

I have Office 2021 with 365 features, but it seems that my Polish edition doesn't know these features. Office's function translator cannot translate these functions "SORT and FILTER".

 

[Joe4]

Sorry, I do not know anything about the Polish edition of Excel.

 

[Wojciech]

My mistake, it works. Thank you very much.

 

[Joe4]

You are welcome.
Glad I was able to help!

 

[Wojciech]

I have an additional question. Is it possible to change the formula so that it does not display column F when adding column B? I.e. the formula below in columns E and F would display what is visible in columns E and G in the attached image.

=SORT(FILTER(A2:C12;C2:C12<=SMALL(C2:C12;8));3)

 

[Joe4]

I have an additional question. Is it possible to change the formula so that it does not display column F when adding column B? I.e. the formula below in columns E and F would display what is visible in columns E and G in the attached image.

=SORT(FILTER(A2:C12;C2:C12<=SMALL(C2:C12;8));3)

Have a look here: FILTER Formula to Return Non-Adjacent Columns in Any Order - Excel Campus
(or maybe just hide column F!)