Sorting Numbers in Excel ("LARGE" Formula Error)

Question

I have a spreadsheet that has a list of invoice numbers based on a date, I am then trying to use the LARGE formula to sort these invoice numbers into numerical order in another column. My Large formula return the error "no valid input data" Is this because the invoice number is being formulated based on a date? is there a work around for this?

    C                               D

1  Invoice Number        Date

2  1601260                    01/26/2016

3  1601261                    01/26/2016

4  1601250                    01/25/2016

The first 2 digits are the year:  (YEAR(D2))-2000)

Second 2 digits are month:  IF(MONTH(D8)>9,MONTH(D8),"0"&MONTH(D8))

Third 2 digits are day:   IF(DAY(D8)>9,DAY(D8),"0"&DAY(D8))

Last digit is a count, so if this is first occurrence of date then =0 2nd occurrence =1, etc...

IF(COUNTIF($D$2:D2,D2)>1,COUNTIF($D$2:D2,D2)-1,0))

the entire formula is:

IF(D2="","",YEAR(D2)-2000&IF(MONTH(D2)>9,MONTH(D2),"0"&MONTH(D2))& IF(DAY(D2)>9,DAY(D2),"0"&DAY(D2))&

IF(COUNTIF($D$2:D2,D2)>1,COUNTIF($D$2:D2,D2)-1,0))

Im then using the LARGE Formula

LARGE(C2:C9,1)

Answer 1

You could change your formula to:

=IF(D2="","",(YEAR(D2)-2000&IF(MONTH(D2)>9,MONTH(D2),"0"&MONTH(D2))& IF(DAY(D2)>9,DAY(D2),"0"&DAY(D2))&

IF(COUNTIF($D$2:D2,D2)>1,COUNTIF($D$2:D2,D2)-1,0))+0)

Answer 2

I still dont know why but this worked for some reason.

Answer 3

I get back #VALUE, an Array Value could not be found.

I don't use Array formulas very often, so I'm not sure how they work.

Answer 4

Enter below formula as an array formula (CTRL+SHIFT+ENTER) & copy down:

=LARGE(VALUE($C$2:$C$9),ROW(1:1))

Regards,

Amit Tandon

If this response answers your question then please mark as Answer.