+ Reply to Thread
Results 1 to 2 of 2

Lookup multi columns w/ multi answers

  1. #1
    Registered User
    Join Date
    01-27-2010
    Location
    Concord, NH
    MS-Off Ver
    Excel 2007
    Posts
    1

    Lookup multi columns w/ multi answers

    I have two excel files, one a full list of town properties (master) and the second a partial list (pickups) of town properties. The partial list is of yearly work that needs to be completed (reviewed & verified) but the tracking is done on the master list.

    1. I need to pull in the year the work is being completed for from the partial list to the master list.
    2. I need to track the completion of meas&list (M&L) or meas (M), identified by a 1 in the corresponding cell from the master spreadsheet.

    Each property is specifically identified by the Map (A), Lot (B), Block (C) format and is the same for each file.

    I cannot concatenate the info because it will cause duplicate responses (ex M11L12B0 & M111L2B0 both create 11120). I want to do both methods to track and verify the completion of all lots in the town over a 4 to 5 year time frame and on an annual basis.

    I have the formula that will give me the response from both the M&L and M columns when I concatenate, but I don't know the formula for verifying all 3 MLB columns. Examples of the spreadsheets are attached.
    Thanks for the help!
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    03-08-2007
    Posts
    93

    Re: Lookup multi columns w/ multi answers

    There are a couple of ways to create a unique key for each record. If the values are always numeric, you can use:

    Key = Map * 1000000 + Lot * 1000 + Block (adjust the mutipliers so that you are guaranteed at least one zero between each component.)

    If not, use:

    Key = Map & "-" & Lot & "-" & Block.

    Excel handles alpha keys just as well as numeric.

    Hope this helps

    SAE

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1