Amazing chart utilities from Jon Peltier
Thanks Thanks:  0
Likes Likes:  0
Page 3 of 4 FirstFirst 1234 LastLast
Results 21 to 30 of 31

Thread: test

  1. #21
    New Member
    Join Date
    Oct 2017
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: test

    1. Below is a sample of the data I’m working with. The “Source Data” is a list of about 301,000 addresses. I have modified them so that all suffixes, directions, etc. are listed in all forms (E/ East, Ter/Terrace, etc). The matching TaxIDs are in column B.

    -------a) The 2nd table is a Lookup Table. The goal is to have the correct TaxID auto populate when I enter just the “Street, Direction (if any), and Zip”. There are some duplicate addresses, and I am looking to pull the first 3 matching TaxIDs.

    -------b) I have to Lookup each address twice, once with the Direction Suffix, and once without. Sometimes the data from external sources adds a Dir Suffix and the Source Data doesn’t have it, which results in an error on the Lookup (Row 3 is an example).

    2. Function vs. Static - I'm not familiar with the static code option you mentioned...but in terms of purpose, the only thing needed is that when a new Address/ZIP is added, the TAXID Searches will automatically fill down and calculate. If the static code can achieve this more quickly, then that would be great. Below is how the data will be used:

    -------1) I will get daily feeds with roughly 30-50 Addresses/ZIPs, and will paste that into TABLE2 to identify the TaxID. Many other formulas occur as well, but the TaxID is the first.
    -------2) I may need to manually enter an Address/ZIP to get the same information, but time to calculate will be important especially in those instances....

    ** The Source Data (301,000 Addresses) will not change in terms of order or quantity. The # of rows will stay the same. Not sure if that helps or not **

    Thank you for your response to my initial inquiry. Since starting to work with these large datasets I've been trying to find faster ways to accomplish basic calculations. I'm not yet at a point where I feel comfortable utilizing VBA Macros for all my data, but from these forums it sounds like those types of solutions are required to speed up the process. Thanks again for any and all assistance.


    SOURCE DATA

    A B
    1 COMBINED ADDRESS TAXID
    2 7708 MARWOOD DR Drive Drive DR 20735 17090897504
    3 2417 UNIVERSITY BLVD Boulevard Boulevard BLVD 20783 17171893023
    4 10400 WOODLAWN BLVD Boulevard Boulevard BLVD 20774 17131466242
    5 14218 HIDDEN FOREST DR Drive Drive DR 20607 17053875432
    6 14218 HIDDEN FOREST DR Drive Drive DR 20607 17055586403

    TABLE 2

    A B C D E F G H I
    1 STREET ADDRESS DIR ZIP ADDRESS - W/ DIR ADDRESS - NO DIR TAXID - W/ DIR TAXID - NO DIR TAXID #2 TAXID #3
    2 7708 MARWOOD DR 20735 7708 MARWOOD DR 20735 7708 MARWOOD DR 20735 17090897504
    3 2417 UNIVERSITY BLVD E 20783 2417 UNIVERSITY BLVD E 20783 2417 UNIVERSITY BLVD 20783 17171893023
    4 10400 WOODLAWN BLVD 20774 10400 WOODLAWN BLVD 20774 10400 WOODLAWN BLVD 20774 17131466242
    5 14218 HIDDEN FOREST DRIVE 20607 14218 HIDDEN FOREST DRIVE 20607 14218 HIDDEN FOREST DRIVE 20607 17053875432 17055586403

    Worksheet Formulas
    Cell Formula
    D2 =TRIM(CONCATENATE(A2," ",B2," ",C2))
    E2 =TRIM(CONCATENATE(A2," ",C2))
    F2 =CheckWords(Sheet1!$P$2:$P$300929,D2,ROWS(F2:F2),1)
    G2 =IF(F2="",CheckWords(Sheet1!$P$2:$P$300929,E2,ROWS(G2:G2),1),"")
    H2 =IF(F2<>"",CheckWords(Sheet1!$P$2:$P$300929,D2,ROWS($1:$2),1),CheckWords(Sheet1!$P$2:$P$300929,E2,ROWS($1:$2),1))
    I2 =IF(F2<>"",CheckWords(Sheet1!$P$2:$P$300929,D2,ROWS($1:$3),1),CheckWords(Sheet1!$P$2:$P$300929,E2,ROWS($1:$3),1))

  2. #22
    New Member
    Join Date
    Oct 2017
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: test

    1. Below is a sample of the data I’m working with. The “Source Data” is a list of about 301,000 addresses. I have modified them so that all suffixes, directions, etc. are listed in all forms (E/ East, Ter/Terrace, etc). The matching TaxIDs are in column B.


    -------a) The 2nd table is a Lookup Table. The goal is to have the correct TaxID auto populate when I enter just the “Street, Direction (if any), and Zip”. There are some duplicate addresses, and I am looking to pull the first 3 matching TaxIDs.


    -------b) I have to Lookup each address twice, once with the Direction Suffix, and once without. Sometimes the data from external sources adds a Dir Suffix and the Source Data doesn’t have it, which results in an error on the Lookup (Row 3 is an example).


    2. Function vs. Static - I'm not familiar with the static code option you mentioned...but in terms of purpose, the only thing needed is that when a new Address/ZIP is added, the TAXID Searches will automatically fill down and calculate. If the static code can achieve this more quickly, then that would be great. Below is how the data will be used:


    -------1) I will get daily feeds with roughly 30-50 Addresses/ZIPs, and will paste that into TABLE2 to identify the TaxID. Many other formulas occur as well, but the TaxID is the first.
    -------2) I may need to manually enter an Address/ZIP to get the same information, but time to calculate will be important especially in those instances....


    ** The Source Data (301,000 Addresses) will not change in terms of order or quantity. The # of rows will stay the same. Not sure if that helps or not **


    Thank you for your response to my initial inquiry. Since starting to work with these large datasets I've been trying to find faster ways to accomplish basic calculations. I'm not yet at a point where I feel comfortable utilizing VBA Macros for all my data, but from these forums it sounds like those types of solutions are required to speed up the process. Thanks again for any and all assistance.




    SOURCE DATA


    A B
    1 COMBINED ADDRESS TAXID
    2 7708 MARWOOD DR Drive Drive DR 20735 17090897504
    3 2417 UNIVERSITY BLVD Boulevard Boulevard BLVD 20783 17171893023
    4 10400 WOODLAWN BLVD Boulevard Boulevard BLVD 20774 17131466242
    5 14218 HIDDEN FOREST DR Drive Drive DR 20607 17053875432
    6 14218 HIDDEN FOREST DR Drive Drive DR 20607 17055586403

    TABLE 2


    A B C D E F G H I
    1 STREET ADDRESS DIR ZIP ADDRESS - W/ DIR ADDRESS - NO DIR TAXID - W/ DIR TAXID - NO DIR TAXID #2 TAXID #3
    2 7708 MARWOOD DR 20735 7708 MARWOOD DR 20735 7708 MARWOOD DR 20735 17090897504
    3 2417 UNIVERSITY BLVD E 20783 2417 UNIVERSITY BLVD E 20783 2417 UNIVERSITY BLVD 20783 17171893023
    4 10400 WOODLAWN BLVD 20774 10400 WOODLAWN BLVD 20774 10400 WOODLAWN BLVD 20774 17131466242
    5 14218 HIDDEN FOREST DRIVE 20607 14218 HIDDEN FOREST DRIVE 20607 14218 HIDDEN FOREST DRIVE 20607 17053875432 17055586403


    Worksheet Formulas
    Cell Formula
    D2 =TRIM(CONCATENATE(A2," ",B2," ",C2))
    E2 =TRIM(CONCATENATE(A2," ",C2))
    F2 =CheckWords(Sheet1!$P$2:$P$300929,D2,ROWS(F2:F2),1)
    G2 =IF(F2="",CheckWords(Sheet1!$P$2:$P$300929,E2,ROWS(G2:G2),1),"")
    H2 =IF(F2<>"",CheckWords(Sheet1!$P$2:$P$300929,D2,ROWS($1:$2),1),CheckWords(Sheet1!$P$2:$P$300929,E2,ROWS($1:$2),1))
    I2 =IF(F2<>"",CheckWords(Sheet1!$P$2:$P$300929,D2,ROWS($1:$3),1),CheckWords(Sheet1!$P$2:$P$300929,E2,ROWS($1:$3),1))

  3. #23
    New Member
    Join Date
    Oct 2017
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: test

    1. Below is a sample of the data I’m working with. The “Source Data” is a list of about 301,000 addresses. I have modified them so that all suffixes, directions, etc. are listed in all forms (E/ East, Ter/Terrace, etc). The matching TaxIDs are in column B.

    -------a) The 2nd table is a Lookup Table. The goal is to have the correct TaxID auto populate when I enter just the “Street, Direction (if any), and Zip”. There are some duplicate addresses, and I am looking to pull the first 3 matching TaxIDs.

    ------b) I have to Lookup each address twice, once with the Direction Suffix, and once without. Sometimes the data from external sources adds a Dir Suffix and the Source Data doesn’t have it, which results in an error on the Lookup (Row 3 is an example).


    2. Function vs. Static - I'm not familiar with the static code option you mentioned...but in terms of purpose, the only thing needed is that when a new Address/ZIP is added, the TAXID Searches will automatically fill down and calculate. If the static code can achieve this more quickly, then that would be great. Below is how the data will be used:

    -------1) I will get daily feeds with roughly 30-50 Addresses/ZIPs, and will paste that into TABLE2 to identify the TaxID. Many other formulas occur as well, but the TaxID is the first.
    -------2) I may need to manually enter an Address/ZIP to get the same information, but time to calculate will be important especially in those instances....

    ** The Source Data (301,000 Addresses) will not change in terms of order or quantity. The # of rows will stay the same. Not sure if that helps or not **

    Thank you for your response to my initial inquiry. Since starting to work with these large datasets I've been trying to find faster ways to accomplish basic calculations. I'm not yet at a point where I feel comfortable utilizing VBA Macros for all my data, but from these forums it sounds like those types of solutions are required to speed up the process. Thanks again for any and all assistance.


    SOURCE DATA

    A B
    1 COMBINED ADDRESS TAXID
    2 7708 MARWOOD DR Drive Drive DR 20735 17090897504
    3 2417 UNIVERSITY BLVD Boulevard Boulevard BLVD 20783 17171893023
    4 10400 WOODLAWN BLVD Boulevard Boulevard BLVD 20774 17131466242
    5 14218 HIDDEN FOREST DR Drive Drive DR 20607 17053875432
    6 14218 HIDDEN FOREST DR Drive Drive DR 20607 17055586403

    TABLE 2


    A B C D E F G H I
    1 STREET ADDRESS DIR ZIP ADDRESS - W/ DIR ADDRESS - NO DIR TAXID - W/ DIR TAXID - NO DIR TAXID #2 TAXID #3
    2 7708 MARWOOD DR 20735 7708 MARWOOD DR 20735 7708 MARWOOD DR 20735 17090897504
    3 2417 UNIVERSITY BLVD E 20783 2417 UNIVERSITY BLVD E 20783 2417 UNIVERSITY BLVD 20783 17171893023
    4 10400 WOODLAWN BLVD 20774 10400 WOODLAWN BLVD 20774 10400 WOODLAWN BLVD 20774 17131466242
    5 14218 HIDDEN FOREST DRIVE 20607 14218 HIDDEN FOREST DRIVE 20607 14218 HIDDEN FOREST DRIVE 20607 17053875432 17055586403


    Worksheet Formulas
    Cell Formula
    D2 =TRIM(CONCATENATE(A2," ",B2," ",C2))
    E2 =TRIM(CONCATENATE(A2," ",C2))
    F2 =CheckWords(Sheet1!$A$2:$A$300929,D2,ROWS(F2:F2),1)
    G2 =IF(F2="",CheckWords(Sheet1!$A$2:$A$300929,E2,ROWS(G2:G2),1),"")
    H2 =IF(F2<>"",CheckWords(Sheet1!$A$2:$A$300929,D2,ROWS($1:$2),1),CheckWords(Sheet1!$A$2:$A$300929,E2,ROWS($1:$2),1))
    I2 =IF(F2<>"",CheckWords(Sheet1!$A$2:$A$300929,D2,ROWS($1:$3),1),CheckWords(Sheet1!$A$2:$A$300929,E2,ROWS($1:$3),1))

  4. #24
    New Member
    Join Date
    Oct 2017
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: test

    Example1: In Table 2, Rows 4 & 5 have the same address - 2034 Anvil Lane - howeverRow 4 has a "South" Direction suffix. The Source Data has a uniqueTaxID for 1) "2034 N ANVIL LANE 20748" and 2) "2034 S ANVIL LANE20748". Since Row 4 has the "S" DIR Suffix, only 1 TaxIDis matched. However Row 5, w/out a DIR Suffix, finds a match for both the North andSouth Addresses in the Source Data.

    Example 2: In Table 2, Row 3, the Address searched was"2417 UNIVERSITY BLVD E 20783". But the Source Data does not have aDirection Suffix (2417 UNIVERSITY BLVD Boulevard 20783). There was nomatch when using the DIR Suffix Address (Cell G2), but found a match whenremoving the DIR Suffix (Cell H2).



    A B C D E F G H I J
    1 STREET DIR CITY ZIP ADDRESS - W/ DIR ADDRESS - NO DIR TAXID - W/ DIR TAXID - NO DIR TAXID #2 TAXID #3
    2 2010 NORTH ANVIL LANE 20748 2010 NORTH ANVIL LANE 20748 2010 NORTH ANVIL LANE 20748 17121336544
    3 2417 UNIVERSITY BLVD E 20783 2417 UNIVERSITY BLVD E 20783 2417 UNIVERSITY BLVD 20783 17171893023
    4 2034 ANVIL LANE S 20748 2034 ANVIL LANE S 20748 2034 ANVIL LANE 20748 17121337674
    5 2034 ANVIL LANE 20748 2034 ANVIL LANE 20748 2034 ANVIL LANE 20748 17121337674 17121336429
    6 11258 WESTPORT DR W 20720 11258 WESTPORT DR W 20720 11258 WESTPORT DR 20720 17131480847
    7 6119 SW CRAIN HWY 20772 6119 SW CRAIN HWY 20772 6119 SW CRAIN HWY 20772 17030200493 17030200892
    8 16800 VILLAGE DR S 20772 16800 VILLAGE DR S 20772 16800 VILLAGE DR 20772 17030207647
    9 5300 MAPLESHADE LN W 20772 5300 MAPLESHADE LN W 20772 5300 MAPLESHADE LN 20772
    Sheet3

    Worksheet Formulas
    Cell Formula
    E2 =TRIM(CONCATENATE(A2," ",B2," ",D2))
    F2 =TRIM(CONCATENATE(A2," ",D2))
    G2 =CheckWords(Sheet1!$A$2:$A$10,E2,ROWS(G2:G2),1)
    H2 =IF(G2="",CheckWords(Sheet1!$A$2:$A$10,F2,ROWS(H2:H2),1),"")
    I2 =IF(G2<>"",CheckWords(Sheet1!$A$2:$A$10,E2,ROWS($1:$2),1),CheckWords(Sheet1!$A$2:$A$10,F2,ROWS($1:$2),1))
    J2 =IF(G2<>"",CheckWords(Sheet1!$A$2:$A$10,E2,ROWS($1:$3),1),CheckWords(Sheet1!$A$2:$A$10,F2,ROWS($1:$3),1))

  5. #25
    New Member
    Join Date
    Oct 2017
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: test

    Example1: In Table 2, Rows 4 & 5 have the same address - 2034 Anvil Lane - howeverRow 4 has a "South" Direction suffix. The Source Data has a uniqueTaxID for 1) "2034 N ANVIL LANE 20748" and 2) "2034 S ANVIL LANE20748". Since Row 4 has the "S" DIR Suffix, only 1 TaxIDis matched. However Row 5, w/out a DIR Suffix, finds a match for both the North andSouth Addresses in the Source Data.

    ABCDEFGHIJ
    1STREETDIRCITYZIPADDRESS - W/ DIRADDRESS - NO DIRTAXID - W/ DIRTAXID - NO DIRTAXID #2TAXID #3
    22010 NORTH ANVIL LANE207482010 NORTH ANVIL LANE 207482010 NORTH ANVIL LANE 2074817121336544
    32417 UNIVERSITY BLVDE207832417 UNIVERSITY BLVD E 207832417 UNIVERSITY BLVD 2078317171893023
    42034 ANVIL LANES207482034 ANVIL LANE S 207482034 ANVIL LANE 2074817121337674
    52034 ANVIL LANE207482034 ANVIL LANE 207482034 ANVIL LANE 207481712133767417121336429
    611258 WESTPORT DRW2072011258 WESTPORT DR W 2072011258 WESTPORT DR 2072017131480847
    76119 SW CRAIN HWY207726119 SW CRAIN HWY 207726119 SW CRAIN HWY 207721703020049317030200892
    816800 VILLAGE DRS2077216800 VILLAGE DR S 2077216800 VILLAGE DR 2077217030207647
    95300 MAPLESHADE LNW207725300 MAPLESHADE LN W 207725300 MAPLESHADE LN 20772

    Sheet3



    Worksheet Formulas
    CellFormula
    E2=TRIM(CONCATENATE(A2," ",B2," ",D2))
    F2=TRIM(CONCATENATE(A2," ",D2))
    G2=CheckWords(Sheet1!$A$2:$A$10,E2,ROWS(G2:G2),1)
    H2=IF(G2="",CheckWords(Sheet1!$A$2:$A$10,F2,ROWS(H2:H2),1),"")
    I2=IF(G2<>"",CheckWords(Sheet1!$A$2:$A$10,E2,ROWS($1:$2),1),CheckWords(Sheet1!$A$2:$A$10,F2,ROWS($1:$2),1))
    J2=IF(G2<>"",CheckWords(Sheet1!$A$2:$A$10,E2,ROWS($1:$3),1),CheckWords(Sheet1!$A$2:$A$10,F2,ROWS($1:$3),1))


  6. #26
    New Member
    Join Date
    Oct 2017
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: test

    A B C D E F G H I J
    1 STREET DIR CITY ZIP ADDRESS - W/ DIR ADDRESS - NO DIR TAXID - W/ DIR TAXID - NO DIR TAXID #2 TAXID #3
    2 2010 NORTH ANVIL LANE 20748 2010 NORTH ANVIL LANE 20748 2010 NORTH ANVIL LANE 20748 17121336544
    3 2417 UNIVERSITY BLVD E 20783 2417 UNIVERSITY BLVD E 20783 2417 UNIVERSITY BLVD 20783 17171893023
    4 2034 ANVIL LANE S 20748 2034 ANVIL LANE S 20748 2034 ANVIL LANE 20748 17121337674
    5 2034 ANVIL LANE 20748 2034 ANVIL LANE 20748 2034 ANVIL LANE 20748 17121337674 17121336429
    6 11258 WESTPORT DR W 20720 11258 WESTPORT DR W 20720 11258 WESTPORT DR 20720 17131480847
    7 6119 SW CRAIN HWY 20772 6119 SW CRAIN HWY 20772 6119 SW CRAIN HWY 20772 17030200493 17030200892
    8 16800 VILLAGE DR S 20772 16800 VILLAGE DR S 20772 16800 VILLAGE DR 20772 17030207647
    9 5300 MAPLESHADE LN W 20772 5300 MAPLESHADE LN W 20772 5300 MAPLESHADE LN 20772
    Sheet3

    Worksheet Formulas
    Cell Formula
    E2 =TRIM(CONCATENATE(A2," ",B2," ",D2))
    F2 =TRIM(CONCATENATE(A2," ",D2))
    G2 =CheckWords(Sheet1!$A$2:$A$10,E2,ROWS(G2:G2),1)
    H2 =IF(G2="",CheckWords(Sheet1!$A$2:$A$10,F2,ROWS(H2:H2),1),"")
    I2 =IF(G2<>"",CheckWords(Sheet1!$A$2:$A$10,E2,ROWS($1:$2),1),CheckWords(Sheet1!$A$2:$A$10,F2,ROWS($1:$2),1))
    J2 =IF(G2<>"",CheckWords(Sheet1!$A$2:$A$10,E2,ROWS($1:$3),1),CheckWords(Sheet1!$A$2:$A$10,F2,ROWS($1:$3),1))

  7. #27
    New Member
    Join Date
    Oct 2017
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: test

    Example1: In Table 2, Rows 4 & 5 have the same address - 2034 Anvil Lane - howeverRow 4 has a "South" Direction suffix. The Source Data has a uniqueTaxID for 1) "2034 N ANVIL LANE 20748" and 2) "2034 S ANVIL LANE20748". Since Row 4 has the "S" DIR Suffix, only 1 TaxIDis matched. However Row 5, w/out a DIR Suffix, finds a match for both the North andSouth Addresses in the Source Data.

    ABCDEFGHIJ
    1STREETDIRCITYZIPADDRESS - W/ DIRADDRESS - NO DIRTAXID - W/ DIRTAXID - NO DIRTAXID #2TAXID #3
    22010 NORTH ANVIL LANE207482010 NORTH ANVIL LANE 207482010 NORTH ANVIL LANE 2074817121336544
    32417 UNIVERSITY BLVDE207832417 UNIVERSITY BLVD E 207832417 UNIVERSITY BLVD 2078317171893023
    42034 ANVIL LANES207482034 ANVIL LANE S 207482034 ANVIL LANE 2074817121337674
    52034 ANVIL LANE207482034 ANVIL LANE 207482034 ANVIL LANE 207481712133767417121336429
    611258 WESTPORT DRW2072011258 WESTPORT DR W 2072011258 WESTPORT DR 2072017131480847
    76119 SW CRAIN HWY207726119 SW CRAIN HWY 207726119 SW CRAIN HWY 207721703020049317030200892
    816800 VILLAGE DRS2077216800 VILLAGE DR S 2077216800 VILLAGE DR 2077217030207647
    95300 MAPLESHADE LNW207725300 MAPLESHADE LN W 207725300 MAPLESHADE LN 20772

    Sheet3



    Worksheet Formulas
    CellFormula
    E2=TRIM(CONCATENATE(A2," ",B2," ",D2))
    F2=TRIM(CONCATENATE(A2," ",D2))
    G2=CheckWords(Sheet1!$A$2:$A$10,E2,ROWS(G2:G2),1)
    H2=IF(G2="",CheckWords(Sheet1!$A$2:$A$10,F2,ROWS(H2:H2),1),"")
    I2=IF(G2<>"",CheckWords(Sheet1!$A$2:$A$10,E2,ROWS($1:$2),1),CheckWords(Sheet1!$A$2:$A$10,F2,ROWS($1:$2),1))
    J2=IF(G2<>"",CheckWords(Sheet1!$A$2:$A$10,E2,ROWS($1:$3),1),CheckWords(Sheet1!$A$2:$A$10,F2,ROWS($1:$3),1))


  8. #28
    New Member
    Join Date
    Oct 2017
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: test

    1. Yes, sometimes. The data isn't uniformly assembled, so the Direction will be in the front at times (5507 N Park Ave), at the end (5507 Park Ave N), or sometimes in the middle (5507 Park North Ave). It also randomly switches between the abbreviated form (N) to the long form (North). It happens often that for whatever reason The Source/County Data does not have a DIR Suffix, while another Data Source (Sale Records) does have one for the same property. It also goes the other way, where "5507 N Park Ave" is a distinct address from "5507 Park Ave". That's why I need to have two Lookup Strings - Address w/ Direction and Address - No Direction.

    Example 1: In Table 2, Rows 4 & 5 have the same address - 2034 Anvil Lane - however Row 4 has a "South" Direction suffix. The Source Data has a unique TaxID for 1) "2034 N ANVIL LANE 20748" and 2) "2034 S ANVIL LANE 20748". Since Row 4 has the "S" DIR Suffix, only 1 TaxID is matched. However Row 5, w/out a DIR Suffix, finds a match for both the North and South Addresses in the Source Data.

    Example 2: In Table 2, Row 3, the Address searched was "2417 UNIVERSITY BLVD E 20783". But the Source Data does not have a Direction Suffix (2417 UNIVERSITY BLVD Boulevard 20783). There was no match when using the DIR Suffix Address (Cell G2), but found a match when removing the DIR Suffix (Cell H2).

    This is why I liked your CheckWords example, because the order of each word in the Lookup String does not matter, and catches all potential matching TAXIDs.
    --If my Source Data does not include a DIR Suffix, but the Lookup Address data does, the 2nd Address LookUp (Column E) will still find its Match.
    --If the Source Data does include a DIR Suffix, but the Lookup Address does not, then the worst case is I will have multiple matches (i.e. N & S Address matches), and would need to flag/review those. I'm not aware of a way to get around that issue though.


    2. I added those two columns to deal with the issues discussed above re: the DIR Suffix. If there's a way to search by both Address Lookup String types within the solution itself, then no I would not need those columns.

    3. I'm using the "MrExcel HTML Maker 08072017".


    A B
    1 COMBINED ADDRESS TAXID
    2 2010 NORTH N ANVIL LANE 20748 17121336544
    3 2417 UNIVERSITY BLVD Boulevard Boulevard BLVD 20783 17171893023
    4 2034 S SOUTH ANVIL LN LANE 20748 17121337674
    5 2034 NORTH N ANVIL LANE 20748 17121336429
    6 11258 W WEST WESTPORT DR Drive 20720 17131480847
    7 6119 SW SOUTHWEST CRAIN HWY Highway 20772 17030200493
    8 6119 SW SOUTHWEST CRAIN HWY Highway 20772 17030200892
    9 16800 VILLAGE DR Drive 20772 17030207647
    10 5300 W WEST MAPLE SHADE LN LANE 20772 17151746445
    Sheet1


    A B C D E F G H I J
    1 STREET DIR CITY ZIP ADDRESS - W/ DIR ADDRESS - NO DIR TAXID - W/ DIR TAXID - NO DIR TAXID #2 TAXID #3
    2 2010 NORTH ANVIL LANE 20748 2010 NORTH ANVIL LANE 20748 2010 NORTH ANVIL LANE 20748 17121336544
    3 2417 UNIVERSITY BLVD E 20783 2417 UNIVERSITY BLVD E 20783 2417 UNIVERSITY BLVD 20783 17171893023
    4 2034 ANVIL LANE S 20748 2034 ANVIL LANE S 20748 2034 ANVIL LANE 20748 17121337674
    5 2034 ANVIL LANE 20748 2034 ANVIL LANE 20748 2034 ANVIL LANE 20748 17121337674 17121336429
    6 11258 WESTPORT DR W 20720 11258 WESTPORT DR W 20720 11258 WESTPORT DR 20720 17131480847
    7 6119 SW CRAIN HWY 20772 6119 SW CRAIN HWY 20772 6119 SW CRAIN HWY 20772 17030200493 17030200892
    8 16800 VILLAGE DR S 20772 16800 VILLAGE DR S 20772 16800 VILLAGE DR 20772 17030207647
    9 5300 MAPLESHADE LN W 20772 5300 MAPLESHADE LN W 20772 5300 MAPLESHADE LN 20772
    Sheet3

    Worksheet Formulas
    Cell Formula
    E2 =TRIM(CONCATENATE(A2," ",B2," ",D2))
    F2 =TRIM(CONCATENATE(A2," ",D2))
    G2 =CheckWords(Sheet1!$A$2:$A$10,E2,ROWS(G2:G2),1)
    H2 =IF(G2="",CheckWords(Sheet1!$A$2:$A$10,F2,ROWS(H2:H2),1),"")
    I2 =IF(G2<>"",CheckWords(Sheet1!$A$2:$A$10,E2,ROWS($1:$2),1),CheckWords(Sheet1!$A$2:$A$10,F2,ROWS($1:$2),1))
    J2 =IF(G2<>"",CheckWords(Sheet1!$A$2:$A$10,E2,ROWS($1:$3),1),CheckWords(Sheet1!$A$2:$A$10,F2,ROWS($1:$3),
    Last edited by Fluff; Jul 12th, 2018 at 07:04 AM.

  9. #29
    New Member
    Join Date
    May 2018
    Posts
    28
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: test

    A B C D E F G
    1
    2
    3 Prefix Start No End No Value for No of times generated series oncatenate
    4 AA 101 110 1
    5 BC 111 120 3
    6 D 121 130 2
    7 E 131 140 2
    8 F 141 150 4
    9 G 151 160 4
    10 H 161 170 2
    11 I 171 180 3
    12 J 181 190 4
    13 K 191 200 1
    14 L 201 210 2
    15 M 211 220 3
    16 N 221 230 1
    17 O 231 240 2
    18 P 241 250 3
    19 Q 251 260 2
    20 R 261 270 2
    21 S 271 280 2
    22 T 281 290 2
    23 U 291 300 2
    24 V 301 310 2
    25 W 311 320 2
    26 X 321 330 2
    27 Y 331 340 2
    28 Z 341 350 2
    29
    Sheet1

  10. #30
    Board Regular
    Join Date
    Jun 2006
    Location
    Mid-Atlantic, US
    Posts
    262
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: test

    ABCDE
    3ASSIGNMENT:0ALLOC_NMBRAADOJun-2018
    4
    5Sum of AMOUNT_DC:0BALANCEPeriod
    6GL_ACCOUNT:0GL_ACCOUNTTRAD_PART:0PCOMPANYDec-17Jun-18variance
    72090000AA11(8,577,429.19)(8,878,897.30)(301,468.11)
    82090000AA33(472,397.68)(293,905.64)178,492.04
    92090000AACI(2,836.36)(2,836.36)
    102090000AADC(37,149.80)(21,495.04)15,654.76
    112090000AAIB(54,788,390.57)(54,089,414.34)698,976.23
    122090000 Total(63,875,367.24)(63,286,548.68)588,818.56
    136510000AA111,887,034.42 1,953,357.44 66,323.02
    146510000AA33163,449.59 101,691.35 (61,758.24)
    156510000AACI468.00 468.00
    166510000AADC13,002.43 4,513.96 (8,488.47)
    176510000AAIB602,672.38 594,983.46 (7,688.92)
    186510000 Total2,666,158.82 2,655,014.21 (11,144.61)
    198500990AA118,577,429.19 8,878,897.30 301,468.11
    208500990AA33472,397.68 293,905.64 (178,492.04)
    218500990AACI2,836.36 2,836.36
    228500990AADC37,149.80 21,495.04 (15,654.76)
    238500990AAIB54,788,390.57 54,089,414.34 (698,976.23)
    248500990 Total63,875,367.24 63,286,548.68 (588,818.56)
    259900100AA11(1,887,034.42)(1,953,357.44)(66,323.02)
    269900100AA33(163,449.59)(101,691.35)61,758.24
    279900100AACI(468.00)(468.00)
    289900100AADC(13,002.43)(4,513.96)8,488.47
    299900100AAIB(602,672.38)(594,983.46)7,688.92
    309900100 Total(2,666,158.82)(2,655,014.21)11,144.61
    31Grand Total0.00 0.00 0.00

    pivot AADC



    Worksheet Formulas
    CellFormula
    C3=IF(D6<>"Dec",EOMONTH(DATE(VALUE(MID(CELL("filename",$A$1),FIND("[",CELL("filename",$A$1),1)+4,4)),VALUE(MID(CELL("filename",$A$1),FIND("[",CELL("filename",$A$1),1)+1,2)),1),0),EOMONTH(DATE(VALUE(MID(CELL("filename",$A$1),FIND("[",CELL("filename",$A$1),1)+4,4)+1),VALUE(MID(CELL("filename",$A$1),FIND("[",CELL("filename",$A$1),1)+1,2)),1),0))


Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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