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

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

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


--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://www.excelforum.com/showthread...hreadid=500160

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.


--
usf97j4x4
------------------------------------------------------------------------
usf97j4x4's Profile: http://www.excelforum.com/member.php...o&userid=30361
View this thread: http://www.excelforum.com/showthread...hreadid=500160

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


--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://www.excelforum.com/showthread...hreadid=500160

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 collected 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


--
usf97j4x4
------------------------------------------------------------------------
usf97j4x4's Profile: http://www.excelforum.com/member.php...o&userid=30361
View this thread: http://www.excelforum.com/showthread...hreadid=500160