Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 13

Thread: vlookup with more than one instance

  1. #1
    Join Date
    12th January 2005
    Location
    Singapore
    Posts
    201

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    8th July 2004
    Location
    Belgium
    Posts
    737

    Re: vlookup with more than one instance

    Hi,

    (select all cells)
    is an autofilter not sufficient?

    DATA-FILTER-AUTOFILTER

    Gollem

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,383

    Re: vlookup with more than one instance

    Hi Aadarsh,

    See the attached file.

    HTH
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

  4. #4
    Join Date
    8th July 2004
    Location
    Belgium
    Posts
    737

    Re: vlookup with more than one instance

    Nice formule Krishnakumar .

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    26th July 2004
    Posts
    1,373

    Re: vlookup with more than one instance

    Damn good KK

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    26th July 2004
    Posts
    1,373

    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

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    12th January 2005
    Location
    Singapore
    Posts
    201

    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

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,383

    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

  9. #9
    Join Date
    8th July 2004
    Location
    Belgium
    Posts
    737

    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

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    8th July 2004
    Location
    Belgium
    Posts
    737

    Re: vlookup with more than one instance

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

    VB:
    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

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. VLOOKUP more than one instance
    By Report_2 in forum EXCEL HELP
    Replies: 16
    Last Post: July 7th, 2010, 02:51
  2. Replies: 5
    Last Post: September 24th, 2006, 09:32
  3. Add 2 instance >=.01 of first instance of >=.01
    By Kurt Nichols in forum EXCEL HELP
    Replies: 11
    Last Post: July 29th, 2006, 05:52
  4. Replies: 2
    Last Post: September 20th, 2005, 02:49
  5. 1 instance of Excel
    By pwrlftrtx in forum EXCEL HELP
    Replies: 2
    Last Post: June 25th, 2004, 08:33

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno