Convert unique address list from vertical to horizontal

  • Hello I am trying to convert a town tax list from a vertical to horizontal list so I can then do a simple vlookup on one key column (map+Lot) The format of the list is this:


    A1 (account#) | B1 (name)
    A2 (blank) | B2 (address)
    A3 (blank) | B3 (City, State)
    A4 (blank) | B4 (zip)
    A5 (blank) | B5 (map+lot#)


    New addresses start with a new account number in column A so that can be the trigger



    Now there are two tough parts that I cannot easily figure a way to resolve.


    The first is there could be more than one row for the address such as:


    A1 (account#) | B1 (name)
    A2 (blank) | B2 (address)
    A2 (blank) | B3 (address)
    A3 (blank) | B4 (City, State)
    A4 (blank) | B5 (zip)
    A5 (blank) | B6 (map+lot#)


    Nothing in the address is consistent, for example the zip code could be included in the city/state cell or it could be alone. and I am not concerned with making sure that one column always lists the (city, State) whats important to me is that the account number ends up in 1 column consistently and the map+lot column is also aligned. Such as


    A1-Account B1-Name B2-address b3-address B4-address B5-City/state B6-zip B7-map+lot#


    one thing that is a bit consistent is the map and lot number, there is 3 numbers then a dash and then 3 more numbers and possible a letter and maybe another dash. But you could search on 000- and ID that as a map+lot number.


    Now my biggest issue is I have no idea how to do this. is this quick script someone could write for me? We are a none profit snowmobile club and we are trying to figure out what land owners we cross and this would speed up looking through several thousand land owner entrys manually.


    Thanks


    ~ Phil


    A1 (account#) | B1 (name)
    A2 (blank) | B2 (address)
    A2 (blank) | B3 (address)
    A3 (blank) | B4 (City, State)
    A4 (blank) | B5 (zip)
    A5 (blank) | B6 (map+lot#)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!