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

Thank you so much for your support.

I forgot to update you one more details.

In the year 2019, two different invoice numbers are going on. One starts with "1" which is a free sample to the customer and another one start with "4000" which is sales.

Like the date column, I have one more heading which is having sales and free samples for each entry.

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

I need to find the missing invoice from 4000 and above only.

If your time permits please change the code which can check the missing numbers above 4000.

Thanks again and sorry to add the things later.

Try this code.

Function MissingNos(DateRng As Range, Year_ As Integer, NumRng As Range) As String


Dim cel As Range
Dim T As Long, TA As Long, MinVal As Long, MaxVal As Long
Dim VfyStr As String


T = 1
For Each cel In NumRng


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


VfyStr = VfyStr & "&"


For TA = MinVal + 1 To MaxVal - 1
    
    If InStr(1, VfyStr, "&" & TA & "&") = 0 Then
    MissingNos = MissingNos & "," & TA
    End If


Next TA


If MissingNos <> "" Then MissingNos = Mid(MissingNos, 2)
End Function

 

[kvsrinivasamurthy]

For each year what are limits.

 

[Anbuselvam]

For 2019 4000 and above for sales 1 and above for free samples. Previous years no changes and it was completed.

For each year what are limits.

 

[kvsrinivasamurthy]

Revised code

Function MissingNos(DateRng As Range, Year_ As Integer, NumRng As Range) As String


Dim cel As Range
Dim T As Long, TA As Long, MinVal As Long, MaxVal As Long, StartNo As Long
Dim VfyStr As String




If Year_ = 2019 Then StartNo = 3999
T = 1
For Each cel In NumRng


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


VfyStr = VfyStr & "&"


For TA = MinVal + 1 To MaxVal - 1
    
    If InStr(1, VfyStr, "&" & TA & "&") = 0 Then
    MissingNos = MissingNos & "," & TA
    End If


Next TA


If MissingNos <> "" Then MissingNos = Mid(MissingNos, 2)
End Function

 

[Anbuselvam]

Dear Mr KVSM

Firstly, sorry for the delay in reply due to the weekend holiday (Friday & Saturday)

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

In my excel sheet, I have a similar formulation like finding the missing numbers as below.

{=IFERROR(SMALL(IF(ISNA(MATCH(ROW(INDEX($A:$A,$Q$4):INDEX($A:$A,$R$4)),SMALL(IFERROR(0+TRIM(MID(SUBSTITUTE(K8:K10895,"&",REPT(" ",30)),{1,30,60,90,120},30)),""),ROW(1:10895)),0)),ROW(INDEX($A:$A,$Q$4):INDEX($A:$A,$R$4))),ROW(A1)),"")}

Now I have replaced the formula with your VB code which was reduced the calculation time.

I will share you the other formulas for changing with VB code, Please support if your time permits.

Sincerely Yours
Anbuselvam K

Revised code

Function MissingNos(DateRng As Range, Year_ As Integer, NumRng As Range) As String


Dim cel As Range
Dim T As Long, TA As Long, MinVal As Long, MaxVal As Long, StartNo As Long
Dim VfyStr As String




If Year_ = 2019 Then StartNo = 3999
T = 1
For Each cel In NumRng


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


VfyStr = VfyStr & "&"


For TA = MinVal + 1 To MaxVal - 1
    
    If InStr(1, VfyStr, "&" & TA & "&") = 0 Then
    MissingNos = MissingNos & "," & TA
    End If


Next TA


If MissingNos <> "" Then MissingNos = Mid(MissingNos, 2)
End Function

 

[kvsrinivasamurthy]

Upload the file ad explain what you are doing by the formula.

 

[Anbuselvam]

In the below-linked sheet, I got multiple results in the sheet COA and cell B8, B10 and B16.

To get the results I have a helper column in the sheet Master Data Column M, N and O.

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

How can I avoid those helper columns to get the results as like now?

Please share the code which can eliminate all other formulas.

Thanks in advance

 

[kvsrinivasamurthy]

What is you are calculating finally.

 

[Anbuselvam]

In the attached sheet, In Master data

Column B, C and H are Production date, Product Name and Production quantity

Column J and L are sales date and sales quantity

In the sheet COA,

Cell B8, B10 and B16 are Quantity, Batch number and Production date which is in the stock (Produced but not yet sale to any one)

Cell B4 drop down list is the product names. If i select a product from the list, then B4, B10 and B16 must displays the stock of the selected products from the data of Master data.

I hope it is enough you to write the code. Please let me know if you want more details.

What is you are calculating finally.

 

[kvsrinivasamurthy]

Here is the code for udf.
There is circular reference . It is giving problem.

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


Dim T As Long


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
    GetData = GetData & ", " & ResultRng.Cells(T, 1)
    End If


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


End Function