Find the highest value from range, multiple entry and adjacent cells date

[Anbuselvam]

Hi

In the excel sheet, I have the date in J8:J9999 and Invoice number in the Column K8:K9999.

Invoice number having 4 series as below,
1 to 1000
1001 to 4000
4001 to 5000
10000 to 15000

Invoice number entry will be like this
1&2&3
1005
1006&1007&1008
4004
4005&4006&4007

I used a formula in R4 to find the highest value from 4000 to 5000 is
=AGGREGATE(14,6,K8:K9999/(K8:K9999>=4001)/(K8:K9999<=5000),1)

My expected value in R4 is 4007 (highest value between 4000 to 5000) but it is showing 4004 because it is ignoring to take 4005&4006&4007 entry.

How do I change the formula to get the 4007 in cell R4?

Note: The entry from 4000 to 5000 will be the year of 2019 entries only. I don't know how to get the highest value from this year entry as well as multiple entries too.

Please suggest the formula

Thanks in advance

Sincerely Yours
Anbuselvam K

 

[Anbuselvam]

Dear KVSM

Thanks a lot for your support. The code is working well.

Only the production date cell format is not changing.

I always use 15-Jan-2019 (Date Format)

But it is showing 01/15/2019.

Other than this all is working well.

Once again thanks for your support.

 

[Anbuselvam]

Below file link for your information.

https://www.dropbox.com/s/zj1set2miplwsfi/One cell with multiple results KVSM.xlsb?dl=0

 

[kvsrinivasamurthy]

Here is revised date

Function GetData(ProductRng As Range, Criteria As String, InvoiceRng As Range, ResultRng As Range) As String


Dim T As Long
Dim Val As String


For T = 1 To ProductRng.Cells.Count


    If ProductRng.Cells(T, 1) = Criteria And InvoiceRng.Cells(T, 1) = 0 _
            And InvoiceRng.Cells(T, 1) <> "" Then
        
        If IsDate(CStr(ResultRng.Cells(T, 1))) Then
        Val = Format(ResultRng.Cells(T, 1), "dd-mmm-yyyy")
        Else
        Val = ResultRng.Cells(T, 1)
        End If
        
    GetData = GetData & ", " & Val
    End If


Next T
If GetData <> "" Then GetData = Mid(GetData, 2)


End Function

 

[Anbuselvam]

Thanks, Mr KVSM

It is working good. I will start new post when I required your help.

Here is revised date

Function GetData(ProductRng As Range, Criteria As String, InvoiceRng As Range, ResultRng As Range) As String


Dim T As Long
Dim Val As String


For T = 1 To ProductRng.Cells.Count


    If ProductRng.Cells(T, 1) = Criteria And InvoiceRng.Cells(T, 1) = 0 _
            And InvoiceRng.Cells(T, 1) <> "" Then
        
        If IsDate(CStr(ResultRng.Cells(T, 1))) Then
        Val = Format(ResultRng.Cells(T, 1), "dd-mmm-yyyy")
        Else
        Val = ResultRng.Cells(T, 1)
        End If
        
    GetData = GetData & ", " & Val
    End If


Next T
If GetData <> "" Then GetData = Mid(GetData, 2)


End Function

 

[Anbuselvam]

Dear Mr KVSM

I would like to learn how to write this kind of codes depends on our requirements. Can you suggest or guide to learn the basic of VBA?