Results 1 to 5 of 5
  1. #1
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Lookup city/state combo (Excel 2003 (all))

    Hi all,
    I need assistance in writing a formula to generate a count (of matches) between two tables (on different sheets). Each table has two cols; City and State. Both tables are sorted alphabetically by city then state, and duplicates are removed. I want to test if any given city/state pair (in the shorter master table) exist in the second Data table.

    <pre>Master table: Sheet1 (2133 entries)
    City | State
    Abbeville AL
    Abbeville LA
    Abbeville SC
    Abberdeen MS
    Abbertden NC
    .etc.</pre>


    <pre>Data table: Sheet2 (6847entries)
    City | State
    city1 XX
    Abbeville SC
    city2 XX
    Abbertden NC</pre>


    So my output would be (listed on sheet with Master table):<pre>Master Table (column A | B | C)
    Abbeville SC 1 (or True or Yes, or 'x' or whatever)
    Abbertden NC 1 (or True or Yes, or 'x' whatever)</pre>

    So in this case
    I tried VLOOKUP but can't get it to work to compare both city/state at same time (since cities alone are not unique).

    The end goal is to determine what the % of the 'master' list entries exist in the 'data' list (do 33% match or 27% or ??). On 2nd thought it's really the intersection of the two lists (what city/state pairs they have in common).

    I attached a subset of the cities/states (just the "A" cities) for you to play with.

    Thanks much, <img src=/S/help.gif border=0 alt=help width=23 height=15> <img src=/S/please.gif border=0 alt=please width=31 height=23>
    Deb

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup city/state combo (Excel 2003 (all))

    If you put the formula:

    <pre>=A1&B1
    </pre>


    in cell C1 on Sheet2 and copy it down to C6847, then you could put this formula:

    <pre>=NOT(ISERROR(MATCH(A1&B1,Sheet2!$C$1:$C$6847, 0)))
    </pre>


    in cell C1 on Sheet1 and copy it down to C2133, it should give you what you want. You can then hide column C on Sheet2 if you want to.
    Legare Coleman

  3. #3
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Long Beach, California, USA
    Posts
    233
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup city/state combo (Excel 2003 (all))

    Deb,

    Just a quick attempt... the gurus will probably come up with something better. Created two named ranges on the A-Cities sheet. On the Master sheet entered the formula =SUM((CityRange=A2)*(StateRange=B2)).

  4. #4
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup city/state combo (Excel 2003 (all))

    <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23> Thank you very much. Both your solution and Legare's work quite well.

    These lists are quite big (and just step 1 in several steps I need to do) and it's amazing how much 'bad' data there was. I had to look through each sheet manually to make sure that things like "Ft. Lauderdale" isn't spelled as "Fort Lauderdale" in one list but the other. Obviously when the data was first entered they weren't consistent with the name formats. It definitely matters when you're comparing stuff.

    Thanks again,
    Deb

  5. #5
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup city/state combo (Excel 2003 (all))

    Since you are on Excel 2003, you could use the List feature...

    Also a shorter formula whose result can be summed to obtain a total count:

    In C2 (A-Cities) enter & copy down:

    =A2&","&B2

    This formula will be automatically adjusted by Excel when any addition or deletion to the list occurs.

    In C2 (Master List) enter & copy down:

    =--ISNUMBER(MATCH(A2&","&B2,'A-Cities'!$C$2:$C$98,0))

    This formula will also be updated automatically by Excel itself when changes occur in A-Cities or in Master List.

    See the attachment.
    Microsoft MVP - Excel

Posting Permissions

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