Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Feb 2009
    Posts
    24

    Unanswered: VLookup to find data from filtered rows

    Hi,

    I would like to use Vlookup function in a macro to find out the value from a filtered data.

    ex: Vlookup should check for the criteria in column C(from filtered data) and return the value from column D(from filtered data).

    Please assist.

    thanks
    Ganesh
    Everyone is gifted! Some open the package sooner!

  2. #2
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi,

    VLOOKUP() cannot distinguish between visible and hidden rows, so you cannot use it for this.

    The way to solve this really depends on the purpose of the filtering. Is the data being filtered to whittle down which value you want to look up (ie. filtering by criteria so only the match is visible)? Or for another reason?

    Please clarify...
    Last edited by Colin Legg; 11-26-09 at 07:29.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    <OT>Dupe removed. Colin - you can click on "Report Post" (icon top right of each post) to notify a Mod if you come across Dupes, Spam, naughty posts etc.</OT>
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Will do. Sorry, I keep overlooking the 'Report Post' option.

  5. #5
    Join Date
    Feb 2009
    Posts
    24
    Colin,

    pls refer to the attached file.

    hope this explains.

    thanks.
    Attached Files Attached Files
    Ganesh
    Everyone is gifted! Some open the package sooner!

  6. #6
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi,

    I'm just going to post the text in your attachment on here so everyone can read it without having to download the attachment:

    Assume you have the below information in sheet1
    Code:
    A			B			C
    APOLLO Hospitals	Laboratories		?
    APOLLO Hospitals	Service rooms		?
    APOLLO Hospitals	Operation Theatres	?
    Life Line Hospitals	Laboratories		?
    Life Line Hospitals	Service rooms		?
    Life Line Hospitals	Operation Theatres	?
    and you have the required data for column C of sheet1 in sheet2
    Code:
    A			B			C
    APOLLO Hospitals	Laboratories		10
    APOLLO Hospitals	Service rooms		12
    APOLLO Hospitals	Operation Theatres	15
    Life Line Hospitals	Laboratories		11
    Life Line Hospitals	Service rooms		13
    Life Line Hospitals	Operation Theatres	16
    Filter column A with “APOLLO Hospitals” on both the sheets and fill column C of sheet1 from column C of sheet2 based on the criteria in column B of sheet1.

    Ex: VLOOKUP(“Laboratories”, worksheets(“Sheet2”).range(“B1:C3”), 2)
    I've attached two different solutions to the problem.

    Solution 1 uses an array formula to conditionally lookup the matching value:
    Code:
    {=INDEX(Sheet2!$C$2:$C$7,MATCH(1,(Sheet2!$A$2:$A$7=A2)*(Sheet2!$B$2:$B$7=B2),0))}
    The drawback on this approach is that if you have large tables, the calculation performance of the workbook will become impaired.

    Solution 2 uses helper columns in column F on both sheets. The helper column contains the concatenated result of the two fields:
    Code:
    =A2&B2
    It then 'looks up' using the helper columns:
    Code:
    =INDEX(Sheet2!$C$2:$C$7,MATCH(Sheet1!F2,Sheet2!$F$2:$F$7,0))
    There is a potential issue with this approach, but I do not think it matters in this case.


    Note that neither solution employs filtering and/or VLOOKUP().
    Attached Files Attached Files

  7. #7
    Join Date
    Feb 2009
    Posts
    24
    colin,

    many thanks! you have helped me a lot.

    one question. how do i incorporate this in vb macro?
    Ganesh
    Everyone is gifted! Some open the package sooner!

  8. #8
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi,

    Well say you go for option #2.

    From VBA it's just a case of using the Range.Formula() property to put the formulas in the cells.

    Hope that helps...

  9. #9
    Join Date
    Feb 2009
    Posts
    24
    Excellent!!!

    you are the man!!!
    Ganesh
    Everyone is gifted! Some open the package sooner!

  10. #10
    Join Date
    Feb 2009
    Posts
    24
    sorry to bother you again.

    in the attached file the result is well executed.

    but wen i do the same thing, i get only "10" in all the columns.

    and i get #VALUE error when i apply the same rule in my own excel.

    also in your attachement the parenthesis {} is seen in the formula bar and disappears when the cell is selected. this looks strange for me.

    you have programmed a macro in the document. does it has any impact on the result?
    Ganesh
    Everyone is gifted! Some open the package sooner!

  11. #11
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi,
    but wen i do the same thing, i get only "10" in all the columns.

    and i get #VALUE error when i apply the same rule in my own excel.
    You'll have to show us the code and formulas you've tried. Please can you attach an Excel workbook?

    also in your attachement the parenthesis {} is seen in the formula bar and disappears when the cell is selected. this looks strange for me.
    I recommend that you go for option #2, not #option 1. But just for reference I will explain it -

    The { } in option #1 indicate that the formula is an array formula.

    If you were to manually enter the formula into the formulabar, you would type it in without the leading and final { } and press CTRL+SHIFT+ENTER to complete the entry. This will automatically insert the { } for you. The { } must not be entered manually. From VBA, to do this you would use the Range.FormulaArray() property instead of the Range.Formula() property.

    you have programmed a macro in the document. does it has any impact on the result?
    Sorry, I don't understand this question - but perhaps it will be answered when we identify the problems with the code you have tried?

    Hope that helps...

  12. #12
    Join Date
    Feb 2009
    Posts
    24
    colin,

    i am posting my macro. This is not matching the components properly. please have a look at it. consider my appollo hospitals example posted earlier.

    ***********************************************

    'assume you have the below info in sheet("Volumes as per web")
    C D
    Laboratories 10
    Service rooms 12
    Operation Theatres 15
    Laboratories 11
    Service rooms 13
    Operation Theatres 16

    'assume you have the below info in sheet("Billing Information")
    C D
    Laboratories ?
    Service rooms ?
    Operation Theatres ?
    Laboratories ?
    Service rooms ?
    Operation Theatres ?

    'you need to fill in column D

    Sub CollateVolumes()
    Dim Client_BIC As String
    Dim TotalClients, CLS_Client_Count, ClientID, NrOfBIMs, BIMend, BIM, BIMRowNr As Long
    Dim rg1, rg2 As Long
    Dim rng, rng2 As Range
    TotalClients = Sheets("Control Panel").Range("L2").Value
    Client_Count = 1

    Do Until Client_Count = TotalClients
    'assume Client_BIC = "Apollo Hospitals"
    Client_BIC = Sheets("Control Panel").Range("B" & Client_Count).Value
    'NrOfBIMs = 105
    NrOfBIMs = Sheets("Control Panel").Range("E" & Client_Count).Value
    'BIMend = 109
    BIMend = Sheets("Control Panel").Range("F" & Client_Count).Value
    'rg1 = 136
    'rg2 = 142
    rg1 = Sheets("Control Panel").Range("G" & Client_Count).Value
    rg2 = Sheets("Control Panel").Range("H" & Client_Count).Value
    Sheets("Volumes as per web").Select
    Selection.AutoFilter Field:=1, Criteria1:=Client_BIC

    'here my idea is to define the range as "C136142". Please refer to "set rng" in the next line; not sure if this is the right way to define the range. same for "set rng2" as well.
    Set rng = Sheets("Volumes as per web").Range("C" & rg1, "D" & rg2)
    Set rng2 = Sheets("Volumes as per web").Range("C" & rg1, "C" & rg2)
    Sheets("Billing Information").Select
    Selection.AutoFilter Field:=1, Criteria1:=Client_BIC
    For BIMRowNr = NrOfBIMs To BIMend
    'assume BIM = "Transaction Fee"
    BIM = Worksheets("Billing Information").Cells(BIMRowNr, 2).Value
    On Error Resume Next
    If BIM = "Monthly Maintenance Fee" Then Worksheets("Billing Information").Cells(BIMRowNr, 5) = "1"
    Else
    'is this the right way to use Index and Match function in vba?
    Worksheets("Billing Information").Cells(BIMRowNr, 5) = _
    WorksheetFunction.Index(rng, WorksheetFunction.Match(BIM, rng2), 2)
    On Error GoTo 0
    Next
    Client_Count = Client_Count + 1
    Loop
    End Sub
    Ganesh
    Everyone is gifted! Some open the package sooner!

  13. #13
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi,

    I see that you are trying to derive the results by calling the WorksheetFunction object rather than by using formulas in the worksheet. Is using the worksheet to calculate the results unacceptable and, if so, why?

  14. #14
    Join Date
    Feb 2009
    Posts
    24
    colin,

    the no.of.components to be evaluated keeps changing everytime. using a formula to obtain the match volumes will not serve the purpose when the no.of.rows keeps increasing and decreasing.
    also the information in sheet2 is not manually feeded. it is downloaded from the web and imported into excel.
    i feel more convenient doing this with vba rather than using the formula.
    pls assist.
    Ganesh
    Everyone is gifted! Some open the package sooner!

  15. #15
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi
    colin,

    the no.of.components to be evaluated keeps changing everytime. using a formula to obtain the match volumes will not serve the purpose when the no.of.rows keeps increasing and decreasing.
    also the information in sheet2 is not manually feeded. it is downloaded from the web and imported into excel.
    i feel more convenient doing this with vba rather than using the formula.
    pls assist.
    Yes, but my suggestion is that you can use VBA to put the formulas into the worksheets. I mentioned before that you would use the Range.Formula() property to do it. This is more efficient than using the WorksheetFunction object to call the functions, not only because the WorksheetFunction is a fairly expensive call but also because the formula approach can be done without looping.

Posting Permissions

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