Index and sort by date, sort no longer works in 2019

[Mr_Excell_Ensie]

Hi Bunch of Fantastic People!

In the attached workbook im trying to get Data from sheet one, into two.

I require Sheet 2 to be become a sorted date column/array based on the date range in Sheet1.
The cells that have to appear are dates AND need to have "Lopend" in column B.
(Please skip blanks, and value "0")


Cant get =Sort to work anymore in 2019, hopefully your skills are honed enough to help me out on this one.

[CARACALLA]

Blad2

A2=IFERROR(INDEX(Blad1!$J$2:$J$41,SMALL(IF(FREQUENCY(IF(Blad1!$J$2:$J$41>0,IF(Blad1!$B$2:$B$41=Blad1!$B$2,Blad1!$J$2:$J$41)),Blad1!$J$2:$J$41),ROW(Blad1!$J$2:$J$41)-ROW(Blad1!$J$2)+1),ROWS(Blad2!$A$2:Blad2!$A2))),"")

control+shift +enter

Copy down

[Mr_Excell_Ensie]

Hi Caracalla!

Thank you for your reply, mille grazie!

It is close to perfect, the dates are showing up perfectly!
However, in my file i would like to have;
"Please return the value of Column F(unique identifier for other lookups), relating to the date value."

Is this something you can help me with?
Please also note that in ColumnA i have a text that could change and therefor i require it to be checked in the formula.
(dates are only needed to when in Column A is text "Lopend". (Means current.)

Again, your solution to the date sorting is MoltoMoltoBene!

[CARACALLA]

You can explain to me with a file what result you want.Put the result

Excuse my English

[Mr_Excell_Ensie]

Added the file.

In column A there are 2 words now:
"Lopend" and "Gereed"

Please sort only dates with "Column A" "Lopend" and please return the Column F (unique) too.

[CARACALLA]

Blad2

A2=IFERROR(INDEX(Blad1!$J$2:$J$41,SMALL(IF(FREQUENCY(IF(Blad1!$J$2:$J$41>0,IF(Blad1!$B$2:$B$41=Blad2!$C$1,Blad1!$J$2:$J$41)),Blad1!$J$2:$J$41),ROW(Blad1!$J$2:$J$41)-ROW(Blad1!$J$2)+1),ROWS(Blad2!$A$2:Blad2!$A2))),"")

control+shift +enter

Copy down

For GEREED type GEREED in C1

[Mr_Excell_Ensie]

Very close!

But I need the 16174.1.01 (Sheet1, Column A) to be next to the date 29-09-2020. (In sheet 2).
(Ma ho bisogno che 16174.1.01 (Foglio1, Colonna A) sia accanto alla data 29-09-2020. (Nel foglio 2).)

[CARACALLA]

I still don't understand what you want to achieve.
Attach the file with the results.
Your file was incorrect
associated with the code in c1 corresponds to a single date

[CARACALLA]

In column C all unieks associated with dates in chronological order for Lopend

For GEREED write GEERED in A1 Blad2

[Mr_Excell_Ensie]

deletion of this message

[Mr_Excell_Ensie]

Our posts have crossed, please hold while i check yours.

It looks like what i need, just give me a minute to check!

[Mr_Excell_Ensie]

Yes, this is what im searching for!

Can you tell me why 16174.1.10 is not found in Sheet2?
I guess because the date has to be unique?
(I see 1 date 29-09-20, but i have two uniques on that date: 16174.1.10 AND 16174.1.01)

Can you make it so that it CAN have duplicate dates shown?

29-09-20 16174.1.01
29-09-20 16174.1.10

[CARACALLA]

In this file all the codes associated with Lopend

with unique and chronological date.

for ready write ready in A1

[Mr_Excell_Ensie]

Im so pleased with your help.

Please have a look if you can sort the dates in one column, please (! )

Then were done, ti amo caro mio..

[CARACALLA]

Sorry it's not possible if you want to keep a unique value
therefore only one result for the date.

[Mr_Excell_Ensie]

Date doesnt need to be unique.

the unique is for later use, so i can xlookup data.

I want a long list of dates, even double ones, with the unique number next to it.

That gives my team a list of jobs that are schedulded for that date.

[CARACALLA]

New file is ok ?

[Mr_Excell_Ensie]

You young man, are a life-saver!

Thanks a million for persisting, I really, really, no; really really really appreciate very much, how you have wrestled yourself through this one!

This solved it for me! Bacio!

[CARACALLA]

Just because the best players on my team (Milan) are Dutch
Gullit, Van Basten, Rijkaard

Best player after Maradona

Cruyff

Ciao