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]

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.

Note:

The below formula also finding the highest values even with multiple entries like 12200&12201&12203. But, I need to find between 4001 to 5000 ranges.

Because this year invoice number starts with 4001 which is a lesser value than last year invoice number series like 12200 and so.

=AGGREGATE(14,6,--MID(SUBSTITUTE(K8:K10921,"&",REPT(" ",30)),{1,30,60,90,120},30),1)

Please suggest the formula

Thanks in advance

Sincerely Yours
Anbuselvam K

 

[kvsrinivasamurthy]

Here is the UDF code

Function MaxSpecial(DateRng As Range, Year_ As Integer, NumRng As Range) As Long


Dim cel As Range
Dim T As Long, TA As Long


For Each cel In NumRng


    If Year(DateRng.Cells(T, 1)) = Year_ Then
    M = Split(cel, "&")
    
    For TA = 0 To UBound(M)
    MaxSpecial = WorksheetFunction.Max(MaxSpecial, 0 + M(TA))
    Next TA
    
    End If
T = T + 1
Next cel


End Function

To paste the code
Developer Tab --> Visual Basic
Insert --> Module
Then paste the code.
Close the widow.
Now UDF is available in function list.
For DateRng Select J8:J9999
For Year_ enter 2019 (Or required year)
For NumRng Select K8:K9999

 

[Anbuselvam]

Dear Mr KVSM

Firstly, I would like to thank you for your valuable reply.

Please see the below dropbox link screenshot.

I don't know what went wrong while applying your suggested steps.

https://www.dropbox.com/s/xozbl27f8plbvf3/Screenshot 2019-01-09 14.20.43.png?dl=0

https://www.dropbox.com/s/cgs925rc56wheus/Screenshot 2019-01-09 14.21.02.png?dl=0

Please do the needful.

Thanks in advance.

 

[kvsrinivasamurthy]

Have you pasted the code in the module. If you have done it and not getting result post the file.

 

[Anbuselvam]

please check the below link

https://www.dropbox.com/s/fpplx15jb9f53go/Highest value with VB Code.xlsm?dl=0

 

[kvsrinivasamurthy]

I have revised the code

Function MaxSpecial(DateRng As Range, Year_ As Integer, NumRng As Range) As Long


Dim cel As Range
Dim T As Long, TA As Long


T = 1
For Each cel In NumRng


    If Year(DateRng.Cells(T, 1)) = Year_ Then
    M = Split(cel, "&")
    
    For TA = 0 To UBound(M)
    MaxSpecial = WorksheetFunction.Max(MaxSpecial, 0 + M(TA))
    Next TA
    
    End If
T = T + 1
Next cel


End Function

In the file formula in K6 is very very slow. If not required delete this.

 

[Anbuselvam]

Really Thanks Mr KVSM

it is working now.

Can you suggest an alternate formula for K6? if your time permits.

Actually, In K6 resulting the missing value of the invoice numbers.

 

[kvsrinivasamurthy]

=Anbuselvam 

Actually, In K6 resulting the missing value of the invoice numbers.

I did not understand . Explain in detail.

 

[Anbuselvam]

=Anbuselvam 

Actually, In K6 resulting the missing value of the invoice numbers.

I did not understand . Explain in detail.

Our invoice will be like a serial number if I enter 4049&4050 and then 4052&4053 then K6 will reflect 4051. because I skipped or forgot to enter 4051.

I hope you understand.