Forum Discussion

marvinmarapao's avatar
marvinmarapao
Copper Contributor
Jun 14, 2022

How to extracting a data between column range. Column range will vary.

I wanted to extract data that fall into my category using if function.  The data shall be extracted within multiple ranges and the range will vary.    Need a solution for this.  Thank you.
excel
Macros and VBA
OliverScheurich's avatar
OliverScheurich
Gold Contributor
Jun 14, 2022

marvinmarapao 

=IFERROR(IF(AND(INDIRECT($J$3)>=$J$2,INDIRECT($J$3)<=$K$2,INDIRECT($J$4)>$J$1,INDIRECT($J$4)<$K$1),B3,""),"")

Is this what you want to do? In the attached file you can dynamically change the criteria and the column range in the orange cells.

  • marvinmarapao's avatar
    marvinmarapao
    Copper Contributor
    Jun 14, 2022

    OliverScheurich thanks for the response.

    I wanted to do sample as shown below.  Thanks for the help.


    10   Sweet

    11   Sour

    25   Sour

    15   Sweet

    12   Sour

    16   Sour

    23   Sour

    16   Sweet

     

    Data required

    1. Max value of range inside the range of sweet

      Range 1 = 25

      Range 2 =  23

     

    Thanks for the help

     

     

     

    • OliverScheurich's avatar
      OliverScheurich
      Gold Contributor
      Jun 14, 2022

      marvinmarapao 

      Sub sweet()

Dim i As Integer
Dim j As Integer
Dim row1 As Integer
Dim max As Integer

Range("E:E").Clear

row1 = Application.WorksheetFunction.Match("sweet", Range(Cells(1, 2), Cells(8, 2)), 0)
j = 1
For i = 1 To 15

If Cells(row1 + i, 2).Value = "sweet" Then
max = Application.WorksheetFunction.max(Range(Cells(row1 + 1, 1), Cells(row1 + i - 1, 1)))
row1 = row1 + i

Cells(j, 5).Value = max
j = j + 1
i = 0
Else
End If

Next i

End Sub

      Maybe with these lines of code. I assume that there is at least one time sour within two times sweet and not more than 14 times sour within two times sweet. You can click the button in cell G2 in the attached file to start the macro.

Resources