Solved

How to determine a day/value based on three criterias?

Posted on 2013-02-06
5
153 Views
Last Modified: 2013-02-18
Hello Experts

I would like to look up a delivery day based on city , street name and street number.

some streets exist in several cities
I've created a userform which already does part of the job (see button on worksheet) and this is the second userform which I'd like to work:
How do I get the correct value?
This is part of the code which looks up the data. It returns the first day/postal code in the worksheet.

        PostalCode = Application.Evaluate("=LOOKUP(2,1/((StreetTable[Street]=""" & Me.cboStreet.Value & """)*(" & MakeNumeric(Me.txtNumber.Value) & ">=StreetTable[From])*(" & MakeNumeric(Me.txtNumber.Value) & "<=StreetTable[To])),StreetTable[PostalCode])")
        Delivery = Application.Evaluate("=LOOKUP(2,1/((StreetTable[Street]=""" & Me.cboStreet.Value & """)*(" & MakeNumeric(Me.txtNumber.Value) & ">=StreetTable[From])*(" & MakeNumeric(Me.txtNumber.Value) & "<=StreetTable[To])),StreetTable[Delivery])")
        City = Application.Evaluate("=LOOKUP(2,1/((StreetTable[Street]=""" & Me.cboStreet.Value & """)*(" & MakeNumeric(Me.txtNumber.Value) & ">=StreetTable[From])*(" & MakeNumeric(Me.txtNumber.Value) & "<=StreetTable[To])),StreetTable[City])")

Open in new window


The lookup is difficult as the same street exists in several cities,

What do I need to do in order to get the correct days/postal code?

Thanks
Massimo
Search.xlsm
0
Comment
Question by:mscola
  • 2
  • 2
5 Comments
 
LVL 26

Assisted Solution

by:redmondb
redmondb earned 250 total points
Comment Utility
Hi, Massimo.

The cleanest way to do this is to add a "Lookup" column which concatenates the City and Street (e.g. Luzern:Adligenswilerstrasse). Is this acceptable to you? If so, can a city+street combination have multiple entries, e.g. one for houses 1 to 100 and a second for houses 200 to 300?

Thanks,
Brian.
0
 
LVL 12

Accepted Solution

by:
telyni19 earned 250 total points
Comment Utility
I suggest using VLookup formulas, and, like the previous commenter, adding a concatenated column.

See attached for my suggested solution which has the second form fully functioning. Note that I first set up a completely worksheet-based mock-up in columns L and M to construct the initial formulas, as a display of how the solution works, and then I translated them into your code. The worksheet-based formulas use named ranges in order to make the formulas more understandable. It's currently populated with the "difficult" combination you highlighted, showing that it gets the correct result.

Once you're satisfied with the result, you can delete columns L and M and hide column A to make the sheet look exactly as it did before. Hiding column A won't interfere with the vlookups.

As a side note, I don't think you need the MakeNumeric function; the built-in Val function works for the purposes of the form.

The vlookup worksheet function is used multiple times with similar constructions, so I encapsulated it in a function whose main statement says this (with variables for the inputs):

    VLookEval = WorksheetFunction.VLookup(strStreet & strCity, Range("StreetTable[[#All],[Lookup]:[Delivery]]"), intCol, False)

The first input to VLookup is the lookup value, which is the concatenated street and city. The second input is the data table to search. The third input is the column number to return when the value is found. The fourth input is a boolean asking whether to check approximate values; for exact lookups, it's always False.

Since I added the column at the left side of the table (required positioning to make the vlookups work), I also corrected the form initialization by having it use the same variable column determination as the Lookup button's code in order to allow for any other additions to the table.

The code is fully commented, so take a look and see what you think.
Search-Vlookup.xlsm
0
 

Author Comment

by:mscola
Comment Utility
Hello

I tried Brian's attempt first and it took me a long time and it didn't work - even though I had the same idea in mind. Then I tried telny's  workbook. It works with the sample data but when I tried it with the actual data and streets it didn't work because ....

The streets were retrieved from another workbook and they contained whitespace. I used following function in the range "streets":

   Dim cCell As Range
   For Each cCell In Selection
      On Error Resume Next
      cCell.Value = RTrim(cCell.Value)
   Next cCell

Open in new window

 
to remove the whitespace and everything works fine now!

Thanks Brian and telyni19
0
 

Author Closing Comment

by:mscola
Comment Utility
thanks
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
Thanks, mscola.

I tried Brian's attempt first and it took me a long time and it didn't work
If it's not working for you then most experts would prefer you to come back sooner rather than later - they're usually well-experienced at getting to the heart of problems.

Regards,
Brian.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now