Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2013-02-06
5
Medium Priority
?
192 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:Massimo Scola
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 26

Assisted Solution

by:redmondb
redmondb earned 1000 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 1000 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:Massimo Scola
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:Massimo Scola
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
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…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

670 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