Solved

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

Posted on 2013-02-06
5
163 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
ID: 38864310
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
ID: 38865666
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
ID: 38901158
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
ID: 38901161
thanks
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38901497
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

896 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

17 Experts available now in Live!

Get 1:1 Help Now