Announcement

Collapse
No announcement yet.

vlookup with more than one instance

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • vlookup with more than one instance



    Hi

    I have a worksheet with a parameter with values appearing more than once.
    Is there any way of using vlookup to get each of the rows.

    For example in the attached file, say I want all the lat and long values for a particular SC, is there anyway of extracting all of them and storing in an array.

    I tried vlookup, any other method would also be helpful

    Thanx

    aadarsh
    Attached Files

  • #2
    Re: vlookup with more than one instance

    Hi,

    (select all cells)
    is an autofilter not sufficient?

    DATA-FILTER-AUTOFILTER

    Gollem

    Comment


    • #3
      Re: vlookup with more than one instance

      Hi Aadarsh,

      See the attached file.

      HTH
      Attached Files
      Kris

      ExcelFox

      Comment


      • #4
        Re: vlookup with more than one instance

        Nice formule Krishnakumar .

        Comment


        • #5
          Re: vlookup with more than one instance

          Damn good KK

          Comment


          • #6
            Re: vlookup with more than one instance

            Hi KK

            Can u pl explain to a lesser mortal like me your formula....I somewhat got lost in the second half

            pangolin

            Comment


            • #7
              Re: vlookup with more than one instance

              Hi

              Great formulae....
              But the problem is i need them as arrays in VBA so that i can use them with further calculations...
              ie...I need an array for long(1......n)
              That is an array with each of the long values

              Thanx

              Aadarsh

              Comment


              • #8
                Re: vlookup with more than one instance

                Hi pangolin,

                Break the formula into different parts. see what haapens.
                =SMALL(IF($A$3:$A$261=$I$3,ROW($D$3:$D$261)),ROW($A2)-ROW($A$1))

                This will give you the smallest position of the value to return in the array.

                Aadarsh: I'm not a VB expert. So I hope someone in this forum will guide you.

                HTH
                Kris

                ExcelFox

                Comment


                • #9
                  Re: vlookup with more than one instance

                  Aadarsh what are you trying to do? Reading the data in an array is simple but what is the idea?

                  Gollem

                  Comment


                  • #10
                    Re: vlookup with more than one instance

                    Here is a small example to read data in an array.

                    Code:
                    Dim dbArray(1000)              As Double
                        Dim intRow                      As Integer
                        Dim intIndex                    As Integer
                        
                        intRow = 3 'Data starts on row 3
                        intIndex = 0
                        Do While ActiveWorkbook.ActiveSheet.Cells(intRow, 1).Value <> ""
                            'SC = 1 example
                            If ActiveWorkbook.ActiveSheet.Cells(intRow, 1).Value = 1 Then
                                dbArray(intIndex) = ActiveWorkbook.ActiveSheet.Cells(intRow, 4).Value
                                intIndex = intIndex + 1
                            End If
                            intRow = intRow + 1
                        Loop
                    Gollem

                    Comment


                    • #11
                      Re: vlookup with more than one instance

                      Basically I have this SC and I need to calculate the distance from a given point to any point with this particular SC...can be calculated from the lat and long and I then need to output all the distances or the nearest 2...based on requirement

                      The point is to have a list of all distances in an array is imperative

                      Hope this helps

                      Aadarsh

                      Comment


                      • #12
                        Re: vlookup with more than one instance

                        I think this should work..Thanx

                        Comment


                        • #13


                          Re: vlookup with more than one instance

                          KK,

                          I'm new but this is simply awesome. I'm glad I joined this forum.
                          Tell me, how do I start with understanding this formula. I've used it by just editting ths tructure but i need to understand it to use it well in the future.

                          Warm regards,
                          Rotex.

                          Comment

                          Working...
                          X