[Solved] Filtering records based on a value

[amjadinsaudia]

Dear All,

I came across this blog through Md. Abdul Kader posts. He is doing good. His method to describe and solve the issue is easy to understand.

18,541.78​

7,305.39​

10,388.17​

2,990.00​

My requirement is to filter records based on a value 13378.17

This value is total of last two records as show above in the list.

what formula to write in order to sum values in data so that total will come to 13378.17

 

[Yousuf Shovon]

what formula to write in order to sum values in data so that total will come to 13378.17

Hello Amjad,

Thank you for sharing your experience with us. Also, we are glad to hear the appreciation message about Md. Abdul Kader's post. We're fortunate to have him as part of our team. We will pass it along to him.

Regarding your concern, I have managed to create a VBA code to filter records based on a given value. It took me quite some time because I wanted to do this with formula but it is not easy for this kind of tough problem. So, use the below VBA code instead and it worked as expected.

The VBA code:

Sub ExtractValuesToMatchTotal()

'An ExcelDemy Product

    Dim dataList As Range
    Dim totalCell As Range
    Dim outputColumn As Range
    Dim total As Double
    Dim currentTotal As Double
    Dim rowIndex As Integer
    Dim i As Integer
    Dim combination() As Integer
  
    ' Define the range containing your data (A1:A10 in this example)
    Set dataList = ThisWorkbook.Sheets("Sheet1").Range("A2:A20") ' Update the sheet name and range as needed
  
    ' Define the cell with the total you want to achieve (C3 in this example)
    Set totalCell = ThisWorkbook.Sheets("Sheet1").Range("B2") ' Update the sheet name and cell as needed
  
    ' Define the output column where extracted values will be placed (B1:B10 in this example)
    Set outputColumn = ThisWorkbook.Sheets("Sheet1").Range("C2:C20") ' Update the sheet name and range as needed
  
    total = totalCell.Value
    currentTotal = 0
    rowIndex = 1
  
    ReDim combination(1 To dataList.Rows.Count)
  
    outputColumn.ClearContents
  
    If FindCombination(dataList, total, currentTotal, combination, 1) Then
        For i = 1 To UBound(combination)
            If combination(i) = 1 Then
                outputColumn.Cells(rowIndex, 1).Value = dataList.Cells(i, 1).Value
                rowIndex = rowIndex + 1
            End If
        Next i
    End If
End Sub

Function FindCombination(dataList As Range, targetTotal As Double, currentTotal As Double, combination() As Integer, currentIndex As Integer) As Boolean
    If currentIndex > dataList.Rows.Count Then
        If currentTotal = targetTotal Then
            FindCombination = True
        Else
            FindCombination = False
        End If
    Else
        combination(currentIndex) = 1
        If FindCombination(dataList, targetTotal, currentTotal + dataList.Cells(currentIndex, 1).Value, combination, currentIndex + 1) Then
            FindCombination = True
            Exit Function
        End If
      
        combination(currentIndex) = 0
        If FindCombination(dataList, targetTotal, currentTotal, combination, currentIndex + 1) Then
            FindCombination = True
            Exit Function
        End If
    End If
End Function

Note: Adjust the range in the code according to your data.

• Copy the VBA code to a module >> run it. (Read this if you have any queries regarding this step)

How to Write VBA Code in Excel​

As a result, you will get the desired filtered list in column C:

​

Another example:

​

Note: Do not use commas unnecessarily in numbers like in your post. It converts the numbers into text format in this case.

I have attached the workbook for your understanding. Let me know if you have further questions.

Regards,
Yousuf Shovon

 

[amjadinsaudia]

Dear Mr. Yousuf, thanks for your reply. I feel pride as I m in contact with the people who ar part of STEM and respect and integrity.

Further, I have attached the file with real data and range is updated in vba code but facing with an error of range failed.

Kindly check this and advise.

Note: Now two columns will be required in filter range and same two columns in output.

 

[amjadinsaudia]

Method 'count' of object 'range' failed.

This is the error, I am currently facing. Even though I have make to range to 40 lines only. Till 30 lines its working fine.

 

[Yousuf Shovon]

Further, I have attached the file with real data and range is updated in vba code but facing with an error of range failed.

Hello Amjad,

Thank you for your feedback. The code fails because of the large dataset- too much data, and too many possibilities to check. Also, you have 2 new ranges now. Considering the new requirements, I was able to create a new code that filters the data in another cell matching the criteria and highlighting them.

Here is the code:

Sub SumWithMatchCrit()

    Dim lr As Long
    Dim cll As Range
    Dim test As Range
    Dim trng As Range
    Dim rng As Range
    Dim crit As Double
    Dim tVal As Double
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Worksheets("Sheet1")

    lr = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    Set rng = ws.Range("B2:B" & lr)
    rng.Interior.Pattern = xlNone
    crit = ws.Cells(2, 3).Value

    For Each cll In rng
        Set trng = cll
        For Each test In rng
            If Intersect(test, trng) Is Nothing Then
                tVal = test.Value + WorksheetFunction.Sum(trng)
                If Round(tVal, 1) = Round(crit, 1) Then
                    Union(trng, test).Interior.Color = RGB(255, 255, 0)
                    Union(trng.Offset(0, -1), test.Offset(0, -1)).Interior.Color = RGB(255, 255, 0)
                ElseIf tVal < crit Then
                    Set trng = Union(trng, test)
                End If
            End If
        Next test
        If Not trng Is Nothing Then Debug.Print trng.Address
    Next cll
   
    Call CopyHighlightedCells

End Sub


Sub CopyHighlightedCells()

    Dim LastRowA As Long, LastRowB As Long
    Dim ws As Worksheet
    Dim HighlightedValuesA() As Variant
    Dim HighlightedValuesB() As Variant
    Dim i As Long, j As Long
   
    Set ws = ThisWorkbook.Worksheets("Sheet1")
   
    LastRowA = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    LastRowB = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
   
    ReDim HighlightedValuesA(1 To LastRowA - 1)
    ReDim HighlightedValuesB(1 To LastRowB - 1)
   
    j = 1
    For i = 2 To LastRowA
        If ws.Cells(i, "A").Interior.Color <> RGB(255, 255, 255) Then
            HighlightedValuesA(j) = ws.Cells(i, "A").Value
            j = j + 1
        End If
    Next i
   
    j = 1
   
    For i = 2 To LastRowB
        If ws.Cells(i, "B").Interior.Color <> RGB(255, 255, 255) Then
            HighlightedValuesB(j) = ws.Cells(i, "B").Value
            j = j + 1
        End If
    Next i
   
    For i = 1 To UBound(HighlightedValuesA)
        ws.Cells(i + 1, "D").Value = HighlightedValuesA(i)
    Next i
   
    For i = 1 To UBound(HighlightedValuesB)
        ws.Cells(i + 1, "E").Value = HighlightedValuesB(i)
    Next i
   
End Sub

Run the code and you will obtain the output.



Note: For a large dataset with decimals, the sum may not match exactly like the criteria. But the sum matches with integer numbers.

I have also attached the workbook here. Let me know if you have further questions.

Regards.

 

[amjadinsaudia]

Dear Mr. Yousuf

Thank you so much for your work. I was extremely pleased to receive this reply from you, and I appreciate the your hard work.

I must admit that was was not possible for me to derive the formula or VBA code.

Please see below my comments. A little more work on the code can improve the performance, if possible otherwise your efforts this fulfill the purpose for the time being.
Salute to you.

a) Whenever we give an input to Filter in a series, give US correct result. Range B35:B44 BUT not the exact values of the range mentioned. I know this is combination and could make total of any values in the list.

b) Whenever we give an input which is combination of last 24 values do not provide an result.
Input Range B208:B231

c) Whenever we give an input which is in series and then any other Input Range B48:B52 & B61
do not display the result.

d) Whenever we give an input any random values like B6, B11 & B18 then
display the result .

 

[amjadinsaudia]

In attached file we give three different ranges and code is not running.

Please check and reply.

 

[Yousuf Shovon]

I must admit that was was not possible for me to derive the formula or VBA code.

Please see below my comments. A little more work on the code can improve the performance, if possible otherwise your efforts this fulfill the purpose for the time being.
Salute to you.

Hello Amjad,

Thank you for your words of appreciation! The code works okay but it has a bug. The code fails to find the exact sum if the data are both round and decimal numbers. In your second workbook, there is the same case. That's why you could not run it. I can give you a quick solution for now. Just change the value to zero in the below code line:

If Round(tVal, 1) = Round(crit, 1) Then

So, the modified code line will be:

If Round(tVal, 0) = Round(crit, 0) Then

As a result, we will be rounding tVal and crit (data and criteria value) to the nearest integer and then comparing them for equality. This means that any decimal part of tVal and crit will be removed, and they will only be considered equal if their integer parts are the same. And, you will get an output as below.

​

If you check the output sum, it gets close to the criteria but does not match exactly. Currently, I am working to make the code bug free for you. Thanks for your above comment. I will let you know as soon as I can do this. Thanks for staying in touch!

Regards,
Yousuf Shovon

 

[amjadinsaudia]

Dear Yousuf,

After updating the code, still VBA code is not filtering records correctly.

Please see attached file in which I have given three different ranges to filter.

After running the code, output total is not same as required and difference is too much in thousands.

If there was small difference because of decmials can be, can be ignored but difference is in thousands.

File is attached.

 

[Yousuf Shovon]

Please see attached file in which I have given three different ranges to filter.

After running the code, output total is not same as required and difference is too much in thousands.

If there was small difference because of decmials can be, can be ignored but difference is in thousands.

Good News Amjad,

Our team has worked and created a VBA code that can overcome the previous situations and find out the exact data sum that matches the criteria.

Here is the code:

Sub Combination_Search()
'An ExcelDemy Product

Set rng = Range("B2:B134")
Set CompareCell = Range("C2")
Set TrackerCell = Range("G2")

r = rng.Rows.Count
total = 2 ^ r

Dim Arr() As Variant
Found = False

For n = 0 To total - 1
    Sum = 0
    ReDim Arr(0)
    Count = -1
    For i = 1 To r
        Position = r - i
        Power = 2 ^ Position
        Quotient = Int(n / Power)
        If Quotient Mod 2 = 1 Then
            Sum = Sum + rng.Cells(i, 1).Value
            Count = Count + 1
            ReDim Preserve Arr(Count)
            Arr(Count) = i
        End If
    Next i

    If Sum = CompareCell.Value Then
        For j = LBound(Arr) To UBound(Arr)
            rng.Cells(Arr(j), 1).Interior.Color = vbGreen
        Next j
        Found = True
        Exit For
    End If
    
    TrackerCell.Value = Str((((n + 1) / total) * 100)) & "% Searched."
    
Next n

If Found = False Then
    MsgBox ("No Such Combination Found.")
End If

End Sub

If you run the code, it will look through 2^ 134 possibilities in this case, and find you the first possibility that matches the criteria. Also, you can actually see the search percentage in the G2 cell as the code analyzes the data.



Note: As mentioned earlier, the code has to match a lot of possibilities. So, the runtime will be a lot more than the previous code. Also, you can use a better PC for faster results and keep the PC uninterrupted while running the code.

After you find the output in column E, now you can lookup Bill No. using any lookup functions. Read the following articles:

How to Use VLOOKUP Function in Excel

How to Use XLOOKUP Function in Excel​

The code has successfully passed all our tests. So, just give it time and a high PC config to work with large data. I have attached the workbook here.

Regards.

 

[amjadinsaudia]

Dear Yousuf

MS Excel has many possibilities for any requirement to complete.

I noted the requirement details for running this code!

However, I have execute the code for two hours and it was went without interruption and now I break it.

Will run it again while leaving the office.

Next day will see the result.

Thanks for your collective efforts. I will be back soon with my feedback.

 

[amjadinsaudia]

Dear Yousuf,

I have approached Microsoft Tech community and found a suitable, effective & efficient solution to the problem .

It is resolved through solver Add-in. It has an limit of 200 variables to check and this limit is understandable

File is attached for your reference please.

Thanks for your guidance throughout the process in order to achieve excellence in STEM.

We must enable solver Add-In through File --> Options --> Add-Ins and Click on Go
Then check on Solver Add-in.