+ Reply to Thread
Results 1 to 6 of 6

Seeking help from a GURU

  1. #1
    Registered User
    Join Date
    01-11-2006
    Posts
    4

    Seeking help from a GURU

    I have a list of taxpayers in a county and I am trying to break it up into a list of Names and Addresses.

    There is a lot of erroneous data included on the list and it is not an excel file or any other recognizeable format.

    When i import the data into excel, everything is put into a separate cell. It looks something like this: Each line would be in a separate cell... but all in one column

    A
    1===========================
    2Account: 000151270003
    3Owner: A 4 HOME CENTER
    4
    5PO BOX 757
    6Market Value: 250,190
    7ROBBINSVILLE NC 28771
    8
    9Tax Description Asses
    10__________________ _____
    11COUNTY WIDE 250,
    12
    13===========================
    (this goes to row 65600 or something ridiculous)

    (Hope this is making sense thus far)

    Now i need to somehow separate this into a useable data base for instance, column A is name, column B is address etc.


    I guess i need each of the 13 rows above put into their own individual columns? Then i can delete the ones i dont need.
    I tried a paste - transpose which works fine for one block. But there is a ton of data

    If anyone thinks they could help me out on this matter i would REALLY appreciate it. We are starting a food bank for a small mountain town in North Carolina so your effort is going to a good cause! If anyone would like to take a look at the raw data file or the excel data i have thus far i wouldnt have a problem emailing it to you. Its public record so its not like there are any confidentiality issues.

    Thanks so much!!!
    Last edited by usf97j4x4; 01-11-2006 at 11:12 AM.

  2. #2
    David Billigmeier
    Guest

    RE: Seeking help from a GURU

    Try this:

    It looks like your data starts in A1, so paste this formula in B1:

    =OFFSET(INDIRECT("A"&(ROW()-1)*13+1),MOD(COLUMN()-2,13),0)

    Then, copy this formula all the way to column "N" (the 13====== should
    appear in N1 now). This should transpose A1:A13 into B1:N1. Then, drag this
    entire selection (B1:N1) down to the next row. B2:N2 should now be the
    transpose of A14:A26. You can drag this entire selection down as far as
    needed, with each row it will transpose the next 13 rows of column A into
    columns B to N.

    Is that what you needed?
    --
    Regards,
    Dave


    "usf97j4x4" wrote:

    >
    > I have a list of taxpayers in a county and I am trying to break it up
    > into a list of Names and Addresses.
    >
    > There is a lot of erroneous data included on the list and it is not an
    > excel file or any other recognizeable format.
    >
    > When i import the data into excel, everything is put into a separate
    > cell. It looks something like this: Each line would be in a separate
    > cell... but all in one column
    >
    > A
    > 1===========================
    > 2Account: 000151270003
    > 3Owner: A 4 HOME CENTER
    > 4
    > 5PO BOX 757
    > 6Market Value: 250,190
    > 7ROBBINSVILLE NC 28771
    > 8
    > 9Tax Description Asses
    > 10__________________ _____
    > 11COUNTY WIDE 250,
    > 12
    > 13===========================
    > (this goes to row 65600 or something ridiculous)
    >
    > (Hope this is making sense thus far)
    >
    > Now i need to somehow separate this into a useable data base for
    > instance, column A is name, column B is address etc.
    >
    >
    > I guess i need each of the 13 rows above put into their own individual
    > columns? Then i can delete the ones i dont need.
    > I tried a paste - transpose which works fine for one block. But i have
    > like 65,000 of these 13 row blocks.
    >
    >
    > If anyone thinks they could help me out on this matter i would REALLY
    > appreciate it. We are starting a food bank for a small mountain town
    > in North Carolina so your effort is going to a good cause! If anyone
    > would like to take a look at the raw data file or the excel data i have
    > thus far i wouldnt have a problem emailing it to you. Its public record
    > so its not like there are any confidentiality issues.
    >
    > Thanks so much!!!
    >
    >
    > --
    > usf97j4x4
    > ------------------------------------------------------------------------
    > usf97j4x4's Profile: http://www.excelforum.com/member.php...o&userid=30361
    > View this thread: http://www.excelforum.com/showthread...hreadid=500160
    >
    >


  3. #3
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996
    If all of your data beginns in Cell A1 and continues down, try this:

    B1: 2
    C1: 3
    D1: 5
    E1: 6
    F1: 7
    Then put this formula in B3 and copy across thru F2
    =INDEX($A$1:$A$65000,B$1+(ROW()-2)*13)

    Then copy cells B2:F2 down as far as you need

    Adjust the Row_1 numbers to select different fields

    Does that help?

    Regards,
    Ron

  4. #4
    Registered User
    Join Date
    01-11-2006
    Posts
    4
    Thanks for the responses guys

    I did what you said and started filling down, when the first 10 worked i got all excited but then they started offsetting. I figured out that not every block is exactly 13 lines.. Some are 11, some 12, etc.. I believe this may be a lost cause.

  5. #5
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996
    Don't give up just yet.

    Is there a pattern to the data?
    For instance...does the address always begin 2 rows below the Owner field?

    If yes...Try this:
    D1: Owner
    E1: Addr1
    F1: Addr2
    G1: Addr3

    B2:B65000 (numeric sequence from 1 through 64999)

    C2: =SMALL(IF(LEFT($A$1:$A$65000,5)="Owner",ROW($A$1:$A$65000 )),B2)
    (Note: commit that array formula by holding down [Ctrl]+[Shift] and press [Enter])

    Copy that formula down as far as you need.
    (That formula finds each successive occurrence of the Owner field)

    D2: =INDEX($A$1:$A$65000,$C2)
    E2: =INDEX($A$1:$A$65000,$C2+2)
    F2: =INDEX($A$1:$A$65000,$C2+3)
    G2: =INDEX($A$1:$A$65000,$C2+4)

    Copy cells D2:G2 down as far as you need.

    Is that something you can work with?

    Regards,
    Ron

  6. #6
    Registered User
    Join Date
    01-11-2006
    Posts
    4
    There are just too many inconsistencies in the data... For instance, if there a lots of owners for 1 property it may take a few lines just for all the owner names. Also there is a tax exemption program that will add a few lines into the mix, LOTS of people are on this exemption.

    Thanks so much for all your help. You guys' knowledge of excel is absolutely amazing. And i thought i was savy..

    I am probably going to take this right to the tax collecter and try to go through their database to produce the mailer we need. Hopefully this will work. You'd think they would make it simple to help people. All we want to do is make a owner/address mailer!

    Fyi, this is for the Graham County, NC foodbank. It's a tiny town in the Smokies and there are a lot of poor people. We delivered over 60 tons of food last year and will probably double it this year.

    Thanks again for your help and effort
    Last edited by usf97j4x4; 01-11-2006 at 01:20 PM.

+ 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