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